I HAVE 2 ITEM ,ITEM NAME 'CHAIR' AND 'BASKET' AND A QUANTITY...I NEED THAT WHERE QUANTITY > 20 AND ITEM='CHAIR'; AND FOR ITEM='BASKET' ,QUANTITY >'09' SHOULD COME IN OUTPUT..
I FAILED ,NULLS ARE COMING..
I NEED THAT FOR ITEM CHAIR DQTY SHOULD SHOW >20 AND FOR ITEM BASKET DQTY SHOULD BE > 9,,IT SHOULD BE SHOWED IN MY OUTPUT..I DON'T WANT OTHER DQTY NUMBER IN MY OUTPUT
USING SQL-server-2014
this is giving me NUll ,not desire output
I wrote this query
"SELECT CASE WHEN op.dqty > '20' AND ti.SubCategoryName ='CHAIR' THEN ti.SubCategoryName WHEN op.dqty > '9' AND ti.SubCategoryName ='BASKET' THEN ti.SubCategoryName ELSE NULL END AS SubCategoryName , TI.SubCategoryName,op.site,op.sasitename,op.symn,op.dqty,count(distinct(orna)) as orna from ABCD op inner join item ti WHERE TI.SubCategoryName in ('CHAIR' ,'BASKET') GROUP BY TI.SubCategoryName,op.site,op.sasitename,op.symn,op.dqty"
Please don't post in ALL CAPITALS. It's really hard to read.
Can you post a sample of your data? I think you are missing a WHERE clause
Please format your query before posting e.g. this is more readable:
SELECT CASE
WHEN op.dqty > '20'
AND ti.SubCategoryName = 'CHAIR'
THEN ti.SubCategoryName
WHEN op.dqty > '9'
AND ti.SubCategoryName = 'BASKET'
THEN ti.SubCategoryName
ELSE NULL
END AS SubCategoryName
,TI.SubCategoryName
,op.site
,op.sasitename
,op.symn
,op.dqty
,count(DISTINCT (orna)) AS orna
FROM ABCD op
INNER JOIN item ti
WHERE TI.SubCategoryName IN (
'CHAIR'
,'BASKET'
)
GROUP BY TI.SubCategoryName
,op.site
,op.sasitename
,op.symn
,op.dqty