Refactored SQL Query for DISTINCT Records Retrieval

Simplified SQL Code

The provided SQL query retrieves distinct records from multiple joined tables and applies specific filters and calculations. Below is a refactored and simplified version, maintaining the original functionality while enhancing readability.

Key Changes Made

1. Consolidated LIKE Clauses: Used a single pattern for the DO field instead of repeating similar conditions.
2. Removed Redundant Calculations: Streamlined the calculation of flags.
3. Proper Indentation and Formatting: Enhanced readability through structured spacing.

Simplified SQL Query

SELECT DISTINCT 
    [PMATES OPEN SO].[MARKING CODE], 
    [DO] & [PMATES OPEN SO]![ITEM NUMBER] AS [Unique], 
    Stock_Points.[Stock point1] AS [Parent DC], 
    [PMATES OPEN SO].BRANCH, 
    [PMATES OPEN SO].DO, 
    [PMATES OPEN SO].[ITEM NUMBER], 
    [PMATES OPEN SO].[SUPPLIED P/N], 
    [PMATES OPEN SO].[PART NAME],
    [PMATES OPEN SO].[DIST REF NO], 
    [PMATES OPEN SO].[ORD DATE], 
    [PMATES OPEN SO].[REQ DATE2], 
    [PMATES OPEN SO].[SUSPEND QTY], 
    [PMATES OPEN SO].[BACK ORDER QTY], 
    [PMATES OPEN SO].[ALLOCATED QTY], 
    [PMATES OPEN SO].[DO AMOUNT], 
    [PMATES OPEN SO].[DO UNT PRICE],
    [PMATES OPEN SO].[STOCK LEVEL A408], 
    [PMATES OPEN SO].[STOCK LEVEL A346], 
    [PMATES OPEN SO].[STOCK LEVEL A210], 
    [PMATES OPEN SO].CUSTOMERNAME, 
    [PMATES OPEN SO].[CUSTOMER INFORMATION], 
    IIf([PMATES OPEN SO].[DO AMOUNT]>=10000,"CHECK"," ") AS [Value Flag], 
    ACTION_1040.[Allocated to], 
    ACTION_1040.[Allocation Date], 
    ACTION_1040.[Source Location], 
    ACTION_1040.[Service Level], 
    ACTION_1040.[Purchase Order], 
    ACTION_1040.[Allocation Required], 
    ACTION_1040.ETA, 
    ACTION_1040.Comments, 
    ACTION_1040.[Expediting PIC], 
    ACTION_1040.[Expediting Comments], 
    ACTION_1040.[Expediting Complete Y/N], 
    ACTION_1040.[AX Update], 
    [PMATES OPEN SO].[DC RANK], 
    IIf([REQ DATE2]=#1/1/1900#,"NEW CAL", 
        IIf([ACTION_1040]![ETA]=#1/1/2000#,"TBA",
            IIf([ACTION_1040]![ETA] Is Null,"TBA",
                IIf([ETA]>=[REQ DATE2],"Expedite","OK")
            )
        )
    ) AS [Requirement Check], 
    [PMATES OPEN SO].[DO C&H ORDER TYPE], 
    IIf([ACTION_1040]![ETA]=#1/1/2000#,"TBA",
        IIf([ACTION_1040]![ETA] Is Null,"TBA",
            IIf([ETA]0 
         OR [PMATES OPEN SO].[BACK ORDER QTY]>0)
ORDER BY 
    [PMATES OPEN SO].BRANCH, [PMATES OPEN SO].DO, [PMATES OPEN SO].[ITEM NUMBER];

Test Cases and Results

Here are some hypothetical cases to test the refactored SQL query.

Test Inputs

• Set of sample entries in PMATES OPEN SO, ACTION_1040, Stock_Points, Branch_Stock_1 representing:
  • Different DO codes starting with "D", "E", "U".
  • Varying values in SUSPEND QTY and BACK ORDER QTY.

Expected Outputs

• Rows should include distinct entries based on the conditions specified.
• Columns reflecting the calculations such as [Value Flag], [Requirement Check], and [ETA Validation].

Actual Outputs

• Execute the query in the relevant SQL environment to verify results. Ensure the output includes expected rows where:

• DO starts with "D", "E", or "U".

• SUSPEND QTY or BACK ORDER QTY are greater than 0.

Conclusion

The refactored SQL code is more concise and easier to read while preserving its original functionality. This optimization can enhance both performance and maintainability, benefiting further developments or debugging. Using tools available through Enterprise DNA can also provide additional insights into optimizing queries and data modeling practices.