CODEWITHSUNDEEP
sql
sjohn285
sjohn285

Reputation: 405

Why is my subquery returning no results in my query but returns normally when ran on it's own?

SELECT Part_Code 
INTO #Part_Codes
FROM v_items vi
WHERE Item_Class IN('ZFIN','AWORT')
AND Engineering_Status = 'A'
ORDER BY Division_Code, Desc_1

;WITH BOM AS (
    SELECT Parent_Part, Component_Part, Component_Part as Starting_Part, i.*
    FROM v_prod_structure v
    INNER JOIN v_items i on i.Item_Nbr = v.Parent_Part
    WHERE Component_Part IN(SELECT Part_Code + '|01' FROM #Part_Codes)
    AND Item_Class IN('FERM')

UNION ALL

    SELECT v.Parent_Part, v.Component_Part, BOM.Starting_Part, i.*
    FROM v_prod_structure v
    INNER JOIN BOM on BOM.Parent_Part = v.Component_Part
    INNER JOIN v_items i on i.Item_Nbr = v.Parent_Part
    WHERE i.Item_Class IN('STOR')
)
--SELECT Component_Part FROM BOM WHERE Item_Class = 'STOR' AND BOM.Starting_Part = '19125' + '|01'

SELECT Part_Code, Desc_1,Item_Class, Division_Code,
--Dilution
CASE Item_Class
    WHEN  'ZFIN' THEN
    (SELECT Qty_per_Assy 
    FROM v_prod_structure
    INNER JOIN v_items i on i.Item_Nbr = v_prod_structure.Component_Part
    WHERE Parent_Part = vi.Part_Code + '|01'
    AND Item_Class = 'STOR')
    ELSE 
    (SELECT Max_Ord
    FROM IPL
    WHERE Part_Nbr LIKE('%' + Part_Code + '%')) 
    END as Dilution,
NULL as 'Next Week',
--Fermentation
NULL as Fermentation,
--Storage
CASE Item_Class
    WHEN 'AWORT' 
    THEN (SELECT BOM.Component_Part FROM BOM WHERE vi.Item_Class = 'STOR' AND BOM.Starting_Part = vi.Part_Code + '|01')
ELSE NULL END as Storage
FROM v_items vi
WHERE Item_Class IN('ZFIN','AWORT')
AND Engineering_Status = 'A'
ORDER BY Division_Code, Desc_1

Now the part of this code that I need to return data but always returns null for every row:

CASE Item_Class
WHEN 'AWORT'
THEN (SELECT BOM.Component_Part FROM BOM WHERE vi.Item_Class = 'STOR' AND BOM.Starting_Part = vi.Part_Code + '|01')
ELSE NULL END as Storage

I've tested the case statement itself, and it correctly distinguishes between when an item is AWORT or not. The temp table #Part_Codes has all of the correct data as well. After trying SELECT BOM.Component_Part FROM BOM WHERE vi.Item_Class = 'STOR' AND BOM.Starting_Part = '19125' + '|01' manually ran out of the "main" query it gets the results I'm looking for correctly.

Upvotes: 0

Views: 42

Answers (2)

sjohn285
sjohn285

Reputation: 405

After reading the replies, I pieced together that I was simply looking at the wrong table in the where clause of the subquery. I changed vi.Item_Class to BOM.Item_Class

Upvotes: 0

FizzBuzz
FizzBuzz

Reputation: 693

This query cannot run on it's own - you are not selecting from any table with a 'vi' alias. Are you trying to filter the BOM table Item_Class?

SELECT BOM.Component_Part FROM BOM WHERE vi.Item_Class = 'STOR' AND BOM.Starting_Part = '19125' + '|01'

Seems like you are mixing up your aliases bec your case is contradictory: CASE Item_Class WHEN 'AWORT' -- this is looking at vi's Item_Class THEN (SELECT BOM.Component_Part FROM BOM WHERE vi.Item_Class = 'STOR'--vi's Item_Class can't equal AWORT and STOR at the same time

Upvotes: 1

Related Questions