help... out of options with union

[vehto]

can any one help ? newbie

I have three table's two have text need.

select text_x from table_x
where ctl_date> '18-aug03'
and seq_num = '123'
union all
select text_y from table_y
where ctl_date> '18-aug03'
and seq_num = '123';

This works! and returns the text I need
now I need to incoporate the third table
which holds the "location code" which I really need.

I need all seq_num's and text from one specific Loc_code.


select seq_num from loc_table
where Loc_code = 'ST'
and ctl_date> '18-aug03'

this works ! and returns the seq_num

is there a way to get the results from the table above seq_num and use it as a varible for the select statements on the top (and seq_num = 'x')

or maybe there is another option
thanks

 

[JediDan]

I don't entirely understand the relationship between your tables, but what I think you're looking for is an uncorrelated subquery:

SELECT text_x
FROM table_x
WHERE ctl_date > '18-AUG-03'
AND seq_num IN (SELECT seq_num
                FROM loc_table
                WHERE loc_code = 'ST'
                AND ctl_date > '18-AUG-03')

Note that I'm using standard Oracle date format of DD-MON-RR, yours may be different.

Hope this helps.

 

[Turkbear]

Hi,
If I understand what you want to do you could try:

select text_x from table_x
where ctl_date> '18-aug03'
and seq_num in (select seq_num from loc_table
where Loc_code = 'ST'
and ctl_date> '18-aug03')
union all 
select text_y from table_y
where ctl_date> '18-aug03'
and seq_num in (select seq_num from loc_table
where Loc_code = 'ST'
and ctl_date> '18-aug03')
;

At least it should give you an idea as to how to use the subquery as the where clause.

 

[vehto]

YES! Thanks you ! this is what Im trying to do Turkbear

All text was returned with the query for the loc_code
but the seq_num and associated text or out of order another problem I encountered.
(ctl_date '18-aug-03' is correct my goof)


Example:
123 text......
123 text.....
333 text ......
333 text...
333
123 text ....
123 text ....
333 text ....

do I use a GROUP BY statement ?
Thanks
vehto