SQL script question / group by ?

I'm trying to get informations with SQL*Plus from one of our history table [Quoted] named 'states'. This table has all informations about our stores opening/closing dates.

I must find for each store its next date of opening 'next_date' given a date [Quoted] 'trading_date' as parameter.

This parameter 'trading_date' in my example is '19991108'.

I'm trying to retrieve all records with one single request but I can't see how.

[Quoted] I have used 'union' but I feel this is possible with a 'group by' clause or [Quoted] something like that...

Here is my script :

select col1, col2
from states
where store_num = 3700
and trading_date = (select next_date from states where to_char(trading_date, 'YYYYMMDD') = '19991108' and store_num = 3700);

union

select col1, col2
from states
where store_num = 4200
and trading_date = (select next_date from states where to_char(trading_date, 'YYYYMMDD') = '19991108' and store_num = 4200);

union

select col1, col2
from states
where store_num = 6300
and trading_date = (select next_date from states where to_char(trading_date, 'YYYYMMDD') = '19991108' and store_num = 6300);

... and so on for all our stores.

n.b: each store has its proper opening/closing dates

Can you help me ? Thank you !

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 26 1999 - 15:59:29 CET