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

Thread: Query Help !

  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Query Help !

    I need to retrieve invoices from 3 tables.
    Table Project_Detail has my project details
    Table Project_Item storing items for my project. each project have 1 or more items.
    Table Project_Item_Invoice will store invoices for each items in each project. 1 item may have more than 1 invoice also.

    I can select the data with:

    SELECT A.Project_No, B.Item_ID, B.Item_Name,
    C.Invoice_ID, C.Date, C.Amount, C.Invoice_No
    FROM Project_Detail A
    LEFT JOIN Project_Item B ON A.Project_No = B.Project_No
    LEFT JOIN Project_Item_Invoice C ON A.Project_No = C.Project_No AND
    B.Item_ID = C.Item_ID
    WHERE A.Project_No LIKE 'WPJ%' AND A.Project_Status = 'Closed'
    ORDER BY A.Project_No DESC, B.Item_ID, C.Invoice_ID

    output:
    Project_No Item_ID Item_Name Invoice_ID Date Amount Invoice_No
    WPJ-0906-1693 1 TNA0052941 1 2009-08-25 1207.58 0907-1141
    WPJ-0906-1693 1 TNA0052941 2 2009-08-26 2836.70 0906-1153

    as the result above, when I bind the output to my gridview, the same project no and item will show twice.

    the output I want:
    Project_No Item_ID Item_Name Invoice_ID Date Amount Invoice_No
    WPJ-0906-1693 1 TNA0052941 1 2009-08-25 1207.58 0907-1141
    _____________ _ __________ 2 2009-08-26 2836.70 0906-1153

    Is it possible I can achieve the result with only select statement? current practice is by loading the data into datatable, set the duplicate project no to blank, which is not a good solution.
    the output I want

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    You can try


    Code:
    select * from (
    SELECT 
    A.Project_No, B.Item_ID, B.Item_Name,
    C.Invoice_ID, C.Date, C.Amount, C.Invoice_No,
    row_number() 
      over (partition by A.Project_No, B.Item_ID, B.Item_Name
                order by A.Project_No DESC, B.Item_ID, C.Invoice_ID ) rn
    FROM Project_Detail A
    LEFT JOIN Project_Item B ON A.Project_No = B.Project_No
    LEFT JOIN Project_Item_Invoice C ON A.Project_No = C.Project_No 
    AND B.Item_ID = C.Item_ID
    WHERE A.Project_No LIKE 'WPJ%' AND A.Project_Status = 'Closed' 
    )
    where rn=1

  3. #3
    Join Date
    Aug 2009
    Posts
    3
    Thanks for the reply, but the result I expect is like : -
    The selected data from database:
    __________________________________________________________________
    |Project_No | Item_ID | Item_Name | Invoice_ID | Date | Amount | Invoice_No |
    ---------------------------------------------------------------------------
    |WPJ-0906-1693 | 1 | TNA0052941 | 1 | 2009-08-25 | 1207.58 | 0907-1141 |
    |WPJ-0906-1693 | 1 | TNA0052941 | 2 | 2009-08-26 | 2836.70 | 0906-1153 |
    |WPJ-0906-1693 | 1 | TNA0052941 | 3 | 2009-08-27 | 1234.70 | 0906-1157 |
    |WPJ-0906-1693 | 2 | TNA0052942 | 1 | 2009-08-26 | 5678.90 | 0906-1150 |
    |WPJ-0906-1693 | 2 | TNA0052942 | 2 | 2009-08-27 | 4356.20 | 0906-1158 |

    The result I want is like this:
    __________________________________________________________________
    |Project_No | Item_ID | Item_Name | Invoice_ID | Date | Amount | Invoice_No |
    ---------------------------------------------------------------------------
    |WPJ-0906-1693 | 1 | TNA0052941 | 1 | 2009-08-25 | 1207.58 | 0907-1141 |
    |___(blank)____ | _ | ___(blank)__ | 2 | 2009-08-26 | 2836.70 | 0906-1153 |
    |___(blank)____ | _ | ___(blank)__ | 3 | 2009-08-27 | 1234.70 | 0906-1157 |
    |___(blank)____ | 2 | TNA0052942 | 1 | 2009-08-23 | 5678.90 | 0906-1150 |
    |___(blank)____ | _ | ___(blank)__ | 2 | 2009-08-25 | 4356.20 | 0906-1158 |

    I'm not sure is it able to do that without programming, only by select statement.

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Code:
    select 
    decode (t.Project_No,t.Prev_Project_No,'') Project_No,
    decode (t.Item_ID,t.Prev_Item_Id,'') Item_Id,
    decode (t.Item_Name,t.Prev_Item_Name,'') Item_Name,
    t.Invoice_ID, t.Date, t.Amount, t.Invoice_No
    from
    (
    SELECT 
    A.Project_No, B.Item_ID, B.Item_Name,
    C.Invoice_ID, C.Date, C.Amount, C.Invoice_No,
    lag(A.Project_No,1,0) 
      over (partition by A.Project_No
              order by A.Project_No DESC, B.Item_ID, C.Item_Name )Prev_Project_No,
    lag(B.Item_ID,1,0) 
      over (partition by B.Item_ID
              order by A.Project_No DESC, B.Item_ID, C.Item_Name )Prev_Item_Id,
    lag(B.Item_Name,1,0) 
      over (partition by C.Item_Name
              order by A.Project_No DESC, B.Item_ID, C.Item_Name )Prev_Item_Name,
    FROM Project_Detail A
    LEFT JOIN Project_Item B ON A.Project_No = B.Project_No
    LEFT JOIN Project_Item_Invoice C ON A.Project_No = C.Project_No 
    AND B.Item_ID = C.Item_ID
    WHERE A.Project_No LIKE 'WPJ%' AND A.Project_Status = 'Closed' 
    ) t
    Last edited by hrishy; 09-01-2009 at 02:04 PM.

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Talking Break it baby!

    Try this:
    Code:
    SQL> BREAK on Project_No on Item_ID on Item_Name on report
    SQL> WITH t1 AS
      2       (
      3          SELECT 'WPJ-0906-1693' project_no, '1' item_id,
      4                 'TNA0052941' item_name, '1' invoice_id, '2009-08-25' idate,
      5                 1207.58 amount, '0907-1141' invoice_no
      6            FROM DUAL UNION
      7          SELECT 'WPJ-0906-1693', '1', 'TNA0052941', '2', '2009-08-26', 2836.70, '0906-1153'
      8            FROM DUAL UNION
      9          SELECT 'WPJ-0906-1693', '1', 'TNA0052941', '3', '2009-08-27', 1234.70, '0906-1157'
     10            FROM DUAL UNION
     11          SELECT 'WPJ-0906-1693', '2', 'TNA0052942', '1', '2009-08-26', 5678.90, '0906-1150'
     12            FROM DUAL UNION
     13          SELECT 'WPJ-0906-1693', '2', 'TNA0052942', '2', '2009-08-27', 4356.20, '0906-1158'
     14            FROM DUAL)
     15  SELECT   *
     16      FROM t1
     17  ORDER BY 1, 2, 3;
    
    PROJECT_NO    I ITEM_NAME  I IDATE          AMOUNT INVOICE_N
    ------------- - ---------- - ---------- ---------- ---------
    WPJ-0906-1693 1 TNA0052941 1 2009-08-25    1207.58 0907-1141
                               2 2009-08-26     2836.7 0906-1153
                               3 2009-08-27     1234.7 0906-1157
                  2 TNA0052942 1 2009-08-26     5678.9 0906-1150
                               2 2009-08-27     4356.2 0906-1158
    
    SQL>
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Aug 2009
    Posts
    3
    Thanks hrishy and LKBrwn_DBA. Thanks for the help !
    I've tested the query with decode and partition and it works GREAT. However, for the break, it return "Invalid SQL Statement". I'm using Toad for Oracle.

  7. #7
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Script it

    In TOAD you execute as "script" (the lightning button located upper left of the SQl Editor window).
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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