Post reply

Create a subquery with a filter and then left outer join other tables

  • March 10, 2022 at 12:27 am

    #3996517

    I have such a situation. I need to create a subquery and set to Type = 1.

    While I have a left join always pulling from FCSTPERFSTATIC, I need to create a sub query first to pull type 1 out of the HISTWIDE table and then do the left join.

    . When I remove the Type = 1 in the where statement you see below example where an item had forecast but no sales. We would want to see those.

    When I filter to type = 1 in the main query that record won’t be returned in the data

    SELECT f.[DMDUNIT]
    ,l.[U_ROLLUP_DEMAND_CHAIN]
    ,f.[LOC]
    ,i.[DESCR]
    ,d.[U_NPI_KEYATTRIBUTE]
    ,f.[STARTDATE]
    ,ABS(DATEDIFF(week, GETDATE(), d.[U_ONSALE_DATE])) AS 'Weeks of History'
    ,d.[U_ONSALE_DATE] as 'onsale_date'
    ,getdate() as 'DATE'
    ,a.[HistoryQuantity] AS 'Total History'
    ,f.[TOTFCST] AS 'Total Forecast'
    ,ABS(a.[HistoryQuantity] - f.[TOTFCST]) AS 'Absolute Error'
    ,CAST(((f.TOTFCST) / (a.HistoryQuantity)) - 1 as DECIMAL (18,2)) AS "Bias"
    FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC] f
    LEFT OUTER JOIN [BYIntegration].[SCPOMGR].[DMDUNIT] d
    ON f.[DMDUNIT]=d.[DMDUNIT]
    LEFT OUTER JOIN [BYIntegration].[SCPOMGR].[LOC] l
    ON f.[LOC]=l.[LOC]
    LEFT OUTER JOIN [BYIntegration].[SCPOMGR].[ITEM] i
    ON f.[DMDUNIT]=i.[ITEM]
    LEFT OUTER JOIN [BYIntegration].[SCPOMGR].[HISTWIDE_CHAIN] a
    ON f.[DMDUNIT]=a.[DMDUNIT] AND f.[STARTDATE]=a.[DMDPostDate] AND f.[LOC]=a.[LOC]
    AND f.[STARTDATE] BETWEEN @Last2WeekDATE AND @LWDATE
    and a.[TYPE]='1'
    order by 7 desc

    Issue1

    I wanted to ask if someone may have an idea how to create a subquery and then left outer join.

    When I just type ,(SELECT TYPE FROM [BYIntegration].[SCPOMGR].[HISTWIDE_CHAIN] a WHERE TYPE='1') AS 'Type'

    in SELECT statement I receive an error

    `

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    `

     

  • March 10, 2022 at 9:43 am

    #3996786

    You either need to filter in the JOIN or do something like WHERE (a.Type = 1 OR a.Type IS NULL)

    https://sqlbolt.com/lesson/select_queries_order_of_execution

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply