Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 2285
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: 2285
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us