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 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 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