Home » SQL & PL/SQL » SQL & PL/SQL » Need Logic (Oracle 10i)
Need Logic [message #342200] Thu, 21 August 2008 12:14 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Table X
-------
ID	Name	DOB	Inst_tm		Actv_flg
1	xyz	1980	4-jul-2008	  N
1	xyz	1980	26-jul-2008	  Y
2	ABC	1991	20-Jul-2008	  Y
3	pqr	1993	12-jul-2008	  y
3       pqr	1993	26-jul-2008	  N

Table A
------

Id	Cd
1	x
2	y
3	Z	
4	u

Table y
-------
ID	Name	DOB	Inst_tm		Actv_flg
1	sim	1986	4-jul-2008	  N
1	sam	1980	26-jul-2008	  Y
2	ABC	1991	20-Jul-2008	  Y
3	pop	1991	12-jul-2008	  y
3       pqr	1993	26-jul-2008	  N


Table z
-------
ID	Name	DOB	Inst_tm		Actv_flg
1	John	1981	4-jul-2008	  N
1	xyz	1980	26-jul-2008	  Y
2	Tim	1991	20-Jul-2008	  Y
3	pqr	1993	12-jul-2008	  y
3       pqr	1993	26-jul-2008	  N


Now i have to write a query such that
it has to decode CD in Table A and produce results like this shown below

cD	Id		Name	Actv_flg
U	1		 -	 -
Y	1		 sam	  Y
x	2		 xyz	  Y
u	4		 --	  --
z	3		 pqr	  N

Here if you can observe if the CD is U and Id is 1 since it is not defined 
we get null's
if the CD is y then for concern ID the Name and Actv_flg is selected from
Table Y
If the CD is X then for concern ID the name and Actv_flg is selected form 
Table X

Also If you can see, ine Table X,Y,Z the ID's are repeated.
Here we will select the Id based on Latest Inst_tm.

I am working on getting logic. 
Can you help me.
Re: Need Logic [message #342204 is a reply to message #342200] Thu, 21 August 2008 12:25 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

1. Decode
2. Correlated Subquery
3. max function

Thumbs Up
Rajuvan.

[Updated on: Thu, 21 August 2008 12:26]

Report message to a moderator

Previous Topic: query help
Next Topic: Problem with join and finding data between 2 dates
Goto Forum:
  


Current Time: Fri Dec 09 23:13:30 CST 2016

Total time taken to generate the page: 0.12400 seconds