RE: sql help

["Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx>]

Thanks for all the replies.  
Robs  solution worked great.  

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Monday, January 26, 2004 4:16 PM
To: Midrange Systems Technical Discussion
Subject: Re: sql help


Like this?

select * from smitty
....+....1....+....2..
CONTRACT  FLAG  MASTER
      1    Y         0
      2    Y         0
      3    N         1
      4    N         2
      5    N         1
********  End of data 


select contract, flag, 
case when flag='Y' then contract 
 else  master end 
 as masterout 
from rob/smitty 
order by masterout, flag desc, contract

....+....1....+....2....+ 
CONTRACT  FLAG  MASTEROUT 
      1    Y           1 
      3    N           1 
      5    N           1 
      2    Y           2 
      4    N           2 
********  End of data  ********

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/26/2004 03:08 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> cc

Fax to

Subject
sql help






I have a file that has  a contract#, a flag that indicates if the file
is a master contract, and a master contract# so i might have something
like 
 
contract    flag    master
1                y
2                y
3                n        1
4                n        2
5                n        1
 
 
where contract 1 is a master contract with subordinate contracts 3 and 5
 
i'm trying to create an SQL statement that will list the master
contracts and the subordinate contracts. So far i haven't been able to
get this to work.  Can i do this in 1 statement. 
 
i want results such as 
 
 
contract    flag    master
1                y
3                n        1
5                n        1
2                y 
4                n        2
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.