All Forums Database
Anakin 1 post Joined 09/12
12 Sep 2012
Avoid results of subquery as sum of items
SELECT STID.SALES_TRANS_DATE
    ,STID.ITEM_ID
    ,STID.ITEM_QTY
        
FROM  SALES_TRANS_ITEM_DLY AS STID  
    
WHERE STID.SALES_TRANS_DATE between '2011-01-01' and '2012-09-01'
AND  STID.ITEM_ID IN( SELECT  ID.ITEM_ID
                      FROM  ITEM_DIM AS ID
	              WHERE  ID.DEPT_ID IN(4233)
          	      AND   ID.FAMILY_GROUP_ID IN(124)
            		)
  
ORDER BY STID.SALES_TRANS_DT    

Hello all.

I am testing how to write subqueries in TD, but it is not returning the results I expected.

The above SQL returns the SUM of ITEM_QTY per day. I know multiple transactions happen during any day and expected that result.

Why am I not getting each transaction then?

Thanks.

 

 

 

Qaisar Kiani 337 posts Joined 11/05
13 Sep 2012

One possible reason for not getting the results could be that the sales transactions in SALES_TRANS_ITEM_DLY, but their ITEM_ID doesn't exist in ITEM_DIM.

Your SQL query looks fine, the only problem seems to be the missing data in ITEM_DIM table.

The below mentioned query looks to be the equivalent sql query without subquery, and if you are getting some rows for this query then the subquery needs a review!

SELECT STID.SALES_TRANS_DATE
    ,STID.ITEM_ID
    ,STID.ITEM_QTY
FROM  SALES_TRANS_ITEM_DLY AS STID  
INNER JOIN ITEM_DIM B
ON A.ITEM_ID = B.ITEM_ID
WHERE STID.SALES_TRANS_DATE between '2011-01-01' and '2012-09-01'
AND B.DEPT_ID IN (4223)
AND B.FAMILY_GROUP_ID IN (124)
ORDER BY STID.SALES_TRANS_DT    
You must sign in to leave a comment.