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 in Sub query

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

New User


Joined: 14 Dec 2005
Posts: 22
Location: India

PostPosted: Fri Feb 10, 2006 12:18 pm    Post subject: Need help in Sub query
Reply with quote

Hi,
I have two tables Emp and Empsal
Data in the tables are of like below:

Emp
=================================
Emp_Code Emp_name Emp_Join_Date lEmp_Join_Sa
a1234 XYZ 1998-01-01 150.00
b1234 XXZ 2000-01-01 120.00
c1234 YYZ 2000-01-01 450.00


Empsal
=================================
Emp_Code Emp_Sal Emp_Join_Date
a1234 100.00 1998-01-01
b1234 200.00 2000-01-01
c1234 900.00 2000-01-01

Now I need a list of employees joind in 2000 (Emp_join_date) where Emp_join_sal is less than Emp_sal.
Back to top
View user's profile Send private message

nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Fri Feb 10, 2006 12:40 pm    Post subject:
Reply with quote

try this query
select emp_code a, emp_name a from emp a, empsal b where year(emp_join_date) = 2000 and a.emp_join_sal < b.emp_sal.
Back to top
View user's profile Send private message
jwell rymbei

New User


Joined: 14 Dec 2005
Posts: 22
Location: India

PostPosted: Fri Feb 10, 2006 12:50 pm    Post subject: Need help in Sub query
Reply with quote

Hi Nuthan,
since emp_code is present in both the table it might give sql error. I can see select emp_code a, emp_name a from emp a, empsal b what is this a and b??
Back to top
View user's profile Send private message
prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Fri Feb 10, 2006 2:23 pm    Post subject:
Reply with quote

Hi,

It is a.emp_code and a.emp_name from emp a, empsal b. Here a nad b are alias names.

T & R
Prabs
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Fri Feb 10, 2006 3:13 pm    Post subject:
Reply with quote

Hi Prabs,
Thanks for correcting me. its a.emp_code and a.emp_name only.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Fri Feb 10, 2006 3:16 pm    Post subject: Re: Need help in Sub query
Reply with quote

Prabs is correct...Here is modified query from Nuthan...

Code:
SELECT A.EMP_CODE, A.EMP_NAME FROM EMP A, EMPSAL B
WHERE
YEAR(EMP_JOIN_DATE) = 2000
AND
A.EMP_JOIN_SAL < B.EMP_SAL;


Regards,

Priyesh.
Back to top
View user's profile Send private message
nikyojin

New User


Joined: 05 Oct 2005
Posts: 94

PostPosted: Fri Feb 10, 2006 7:52 pm    Post subject: Re: Need help in Sub query
Reply with quote

Hi,
Modification for performance Tuning can be made..the join is also missing...the date functions in DB2 usually consume a lot of DB2 time...try to avoid it in queries.
Modified Query
Code:
SELECT A.EMP_CODE, A.EMP_NAME FROM EMP A, EMPSAL B
WHERE
A.Emp_Code = B.Emp_Code
AND
EMP_JOIN_DATE > '1999-12-31'
AND
EMP_JOIN_DATE < '2001-01-01'
AND
A.EMP_JOIN_SAL < B.EMP_SAL;


Pls correct me if I'm wrong

Thanks,
Nikhil .S.
Back to top
View user's profile Send private message
jwell rymbei

New User


Joined: 14 Dec 2005
Posts: 22
Location: India

PostPosted: Fri Feb 10, 2006 8:03 pm    Post subject:
Reply with quote

Thanks guyz for all the valuable suggestions..
Now again the lets say data in two tables are like below
Emp
=================================
Emp_Code Emp_name Emp_Join_Date lEmp_Join_Sa
a1234 XYZ 1998-01-01 150.00
b1234 XXZ 2000-01-01 120.00
c1234 YYZ 2000-01-01 450.00


Empsal
=================================
Emp_Code Seq_no Emp_Sal Emp_Join_Date
a1234 001 100.00 1998-01-01
b1234 001 200.00 2000-01-01
b1234 002 150.00 2001-01-01
b1234 003 150.00 2001-01-01
c1234 900.00 2000-01-01

Now to list employees joind in 2000 (Emp_join_date) where Emp_join_sal is less than Emp_sal. What we need to do.
Note in Empsal table we have seq no added as a new column and for a emp_code we can have multiple records.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Fri Feb 10, 2006 8:51 pm    Post subject: Re: Need help in Sub query
Reply with quote

You would need to append this field in SELECT clause if you want to have it in the final output (which you will ofcourse, as there are multiple SEQ NO. for a EMP_CODE).

And that would be coded as B.SEQ_NO

Regards,

Priyesh.
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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