-
help with query
Hi,
I have a simple query consisting 3 tables with joins.
e.g Table1 : Orders 2: Order Detail 3: Sales
The query goes like this,
select order.* from order, od, sales where order.orderno = od.orderno and od.salesno = sales.salesno;
Now I want to retrieve one more field from 4th table. and also want to check if the value in 4th table is blank then take the value of the filed from 5th table.
The schema of 4th and 5th table is same.
There is relation between Sales table and 4th/5th table.
How can I do this?confused:
thanks in advance:
-
Join them as you did the other tables - use outer join if the corresponding rows are not guaranteed to exist. Evaluate the result with something like: NVL(TRIM(tab4.col), tab5.col)
-
You can use "select clause" in a column list.
PHP Code:
select order.* ,
(select some_col from table_4
where table_4.col_x = sales.col_y)
from order,
od,
sales
where order.orderno = od.orderno and
od.salesno = sales.salesno;
Is that you are looking for?
OR You can use coalesce function at column level:
PHP Code:
SQL> select * from t1;
ORD_ID ORD_DATE
---------- ---------
8 15_DEC-04
1 15_DEC-04
2 15_DEC-04
9 15_DEC-04
7 15_DEC-04
5 15_DEC-04
4 15_DEC-04
3 15_DEC-04
6 15_DEC-04
9 rows selected.
SQL> select * from t2 ;
ORD_ID ORD_DATE
---------- ---------
8 15_DEC-04
1 15_DEC-04
2 15_DEC-04
9 15_DEC-04
7 15_DEC-04
5 15_DEC-04
4 15_DEC-04
3 15_DEC-04
6 15_DEC-04
9 rows selected.
SQL> get x
1 select t1.ord_id, t1.ord_date,
2 coalesce((select max(ord_date) from t2
3 where t2.ord_date < t1.ord_date),
4 (select max(ord_date) from t2
5 where t2.ord_date > t1.ord_date)
6 )
7* from t1
SQL> /
ORD_ID ORD_DATE COALESCE(
---------- --------- ---------
8 15_DEC-04 15_DEC-04
1 15_DEC-04 15_DEC-04
2 15_DEC-04 15_DEC-04
9 15_DEC-04 15_DEC-04
7 15_DEC-04 15_DEC-04
5 15_DEC-04 15_DEC-04
4 15_DEC-04 15_DEC-04
3 15_DEC-04 15_DEC-04
6 15_DEC-04 15_DEC-04
9 rows selected.
Tamil
Last edited by tamilselvan; 12-15-2004 at 02:38 PM.
-
thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|