Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need help on a query..

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
batu544

New User


Joined: 21 Jul 2005
Posts: 16
Location: bangalore

PostPosted: Sun Feb 01, 2009 3:39 pm    Post subject: Need help on a query..
Reply with quote

batu544 wrote:
Hi,
Here is one requirement.

Table 1

Code:
 

E_ID                E_NAME
-------         -------------------
10                 PAUL
20                 SAM
30                 DENIS
40                 MERTZ


Table 2

Code:


E_ID1               E_SALARY
--------            ---------------
10                    100000
20                    200000
20                    250000
30                    300000
50                    500000
60                    600000


Now the required output is

Code:


E_id            E_NAME           e_SALARY
--------       ------------         ------------
10              PAUL                 100000
20              SAM                  250000
30              DENIS               300000
40              MERTZ              -




just note : all the rows from table1 is required in the output. if there is any duplicate e_id1 in table2 then it should take the row having highest salary.

I have done this by creating one intermediate table and using LEFT JOIN. ( because I got some error while running left join with an inner select query. (may be its not allowed.)


Is it possible to get the desired result by using UNION ?

e.g.
Code:

SELECT E_ID, E_NAME
           ,E_ID1, E_SALARY
FROM TABLE1,TABLE2
WHERE E_ID = E_ID1

UNION ALL

SELECT E_ID, E_NAME
           ,E_ID1,E_SALARY
FROM  TABLE1,TABLE2
WHERE

0 = ( SELECT COUNT(*) FROM ....  )



I am not sure .. what to write in the 2nd part of UNION ..
Could somebody help me on this .....


Thanks
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2276
Location: @my desk

PostPosted: Sun Feb 01, 2009 4:32 pm    Post subject:
Reply with quote

batu544,

How about trying a simple LEFT JOIN like this.
Code:
  SELECT E_ID         
        ,E_NAME       
        ,MAX(E_SALARY)
    FROM TABLE1     
    LEFT JOIN         
         TABLE2     
      ON E_ID = E_ID1
GROUP BY E_ID,E_NAME;
Back to top
View user's profile Send private message
batu544

New User


Joined: 21 Jul 2005
Posts: 16
Location: bangalore

PostPosted: Sun Feb 01, 2009 9:37 pm    Post subject:
Reply with quote

Hi Arun,
Thanks for your reply.. but I hope this will not give me the expected result, when the table2 has more that 2 column and I need all the columns in my result.

Lets think that table2 contains following columns ..

Code:


E_ID1           E_SAARY              E_PLACE
---------        ------------           --------------
10                    100000               ST LOUIS
20                    200000               MISSOURI
20                    250000               CA
30                    300000               MO
50                    500000               CA
60                    600000               XX



One more point is if I don't want e_salary column in my result then this query will not work.

For this I used to write the query like this..

Code:


SELECT E_ID, E_NAME               
     , E_ID1,  E_SAL                 
FROM TAB1                                 
   , TAB2 A                               
 WHERE E_ID = E_ID1                             
  AND E_SALARY = ( SELECT MAX(E_SALARY) FROM TAB2
                      WHERE E_ID1 = A.E_ID1 )   
                                                 


This query is giving the rows which are present in both the tables. If I am doing LEFT JOIN with this query, then its giving error.. due to that inner query ..

So, need help in writing the query in other way.. icon_sad.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2276
Location: @my desk

PostPosted: Mon Feb 02, 2009 12:03 am    Post subject:
Reply with quote

Quote:
but I hope this will not give me the expected result,
This WILL give you the 'expected results' shown by you in your initial post.
Quote:
One more point is if I don't want e_salary column in my result then this query will not work
You cant expect a single sql to work for all the cases. Also you have not posted your 'new expected result'. I would suggest posting it so that somebody here might be able to help.
Back to top
View user's profile Send private message
batu544

New User


Joined: 21 Jul 2005
Posts: 16
Location: bangalore

PostPosted: Mon Feb 02, 2009 3:27 am    Post subject:
Reply with quote

arcvns wrote:
Quote:
but I hope this will not give me the expected result,
This WILL give you the 'expected results' shown by you in your initial post.

[/quote]
This is NOT giving the desired result when the table2 has more than 2 rows ..


I am posting the requirements here again..

table1

Code:


e_id    e_name
-----   -----------
10        xxxxx
20        yyyyy
30        zzzzz
40        aaaaa

table2
Code:

e_id1    e_salary   e_place
-----     ----------   ---------
10         100           bangalore
20         2000         pune
20         2500         mumbai
30         3000         Puri
50         5000         Test place


now the desired result is

Code:


e_id          e_name    e_palce
-----          ---------    ---------
10             xxxxxx    bangalore
20             yyyyyy    mumbai
30             zzzzzzz    puri
40             aaaaaa    --


Please note : All the rows from the table1 will be selected .. and details will be selected from table 2 for maximum salary only. ( e.g e_id = 20 ) ..

Could anyone please help me on this issue. ?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Feb 02, 2009 6:32 am    Post subject:
Reply with quote

Hello,

Quote:
You cant expect a single sql to work for all the cases.
Totally agree. Even if some single query is found, it may not be the best (or even a good) solution. Some very innocent-looking "simple" queries have a tendancy to "dim the lights".

2 very simple queries inside a bit of simple program code will do what you need and will also perform well.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Feb 02, 2009 9:17 am    Post subject:
Reply with quote

Quote:
If I am doing LEFT JOIN with this query, then its giving error.. due to that inner query ..


What error is it giving?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us