DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: help with query

  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Question 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:

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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)

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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:
    SQLselect 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
    .

    SQLselect 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
    .
    SQLget x
      1  select t1
    .ord_idt1.ord_date,
      
    2         coalesce((select max(ord_datefrom t2 
      3                    where t2
    .ord_date t1.ord_date),
      
    4                  (select max(ord_datefrom t2 
      5                    where t2
    .ord_date t1.ord_date)
      
    6                 )
      
    7from 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.

  4. #4
    Join Date
    Nov 2003
    Posts
    6

    Smile thanks

    thanks for your reply

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width