CODEWITHSUNDEEP
sqljoindb2
Buras
Buras

Reputation: 3099

SQL. How to join an existing table with the one that has been just generated (DB2)?

I have the following table:

enter image description here

My task is: for each event selected item , report the event id along with the total unit count for all items for that event.

I tried the following:

 SELECT S.ITEMNO,S.EVENTID, S.ROOMID, T.TOTAL                          
 FROM SELECTEDITEM S JOIN                                              
                    (SELECT EVENTID, SUM(UNIT_COUNT) AS TOTAL          
                    FROM SELECTEDITEM                                  
                    GROUP BY EVENTID) AS T                             
                    ON S.ITEMNO=T.ITEMNO;                              
---------+---------+---------+---------+---------+---------+---------+---------
DSNT408I SQLCODE = -206, ERROR:  T.ITEMNO IS NOT VALID IN THE CONTEXT WHERE IT 
         IS USED

Why is it not working? Can I join an existing table with one that has been just generated?

Upvotes: 0

Views: 288

Answers (2)

Edwin Stoteler
Edwin Stoteler

Reputation: 1228

SELECT S.ITEMNO,S.EVENTID, SUM(UNIT_COUNT) as total
FROM SELECTEDITEM S
GROUP BY S.ITEMNO,S.EVENTID

Upvotes: 1

beny23
beny23

Reputation: 35018

Are you sure you didn't want

SELECT S.ITEMNO,S.EVENTID, S.ROOMID, T.TOTAL                          
  FROM SELECTEDITEM S 
  JOIN (SELECT EVENTID, SUM(UNIT_COUNT) AS TOTAL          
          FROM SELECTEDITEM                                  
         GROUP BY EVENTID) AS T                             
    ON S.EVENTID=T.EVENTID;

The problem is that your subselect does not have an t.ITEMNO column, so the ON clause wouldn't work.

Upvotes: 1

Related Questions