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

Thread: SQL question

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    83
    Hiya, quick question on how to do IF... Then type queries.

    Variables are REF_NO, LINE_OBJECTS and LINE_ACTION. There are 2 types of data that I want returned:
    a) where REF_NO<>0 and LINE_OBJECT in (11, 27) and LINE_ACTION =603
    and
    b)where REF_NO=0 and LINE_OBJECT=25 and LINE_ACTION=650

    What I want to avoid is to return rows where it only has LINE_ACTION= 11 and 25 against it. I want either 11, 27 and 25 or just 11 and 27.

    Probelm is, I have no idea how to go about putting such conditions in my SQL (which is simple SELECT statement)

    Any help will be welcome!

    Fiona

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    we need more information in order to help you.
    Jeff Hunter

  3. #3
    Join Date
    Nov 2000
    Location
    London
    Posts
    83
    Full syntax is (you asked for it)

    SELECT c.store_no, b.store_name, c.entry_date_time, c.transaction_no, a.reference_no, SUM(a.gross_line_amount - a.pos_discount_amount)*a.db_cr_none) as SUM1,a.line_object, SUM(a.gross_line_amount * a.db_cr_none) as SUM2, a.line_action
    FROM AUDITWORKS.sv_av_transaction_line a, AUDITWORKS.sv_store b,
    AUDITWORKS.sv_av_transaction_header c
    WHERE c.transaction_id=a.transaction_id
    AND c.store_no=b.store_no
    AND c.transaction_date >= '17/09/02')
    AND 1=1
    AND a.line_action not in 246
    GROUP BY c.store_no,b.store_name,c.transaction_date,c.entry_date_time,c.transaction_no,a.reference_no,a.line_ object,a.line_action

    I want to add the condtions I described inthe first post to this above syntax.

  4. #4
    Join Date
    Jun 2002
    Posts
    22
    SELECT c.store_no, b.store_name, c.entry_date_time, c.transaction_no, a.reference_no, SUM(a.gross_line_amount - a.pos_discount_amount)*a.db_cr_none) as SUM1,a.line_object, SUM(a.gross_line_amount * a.db_cr_none) as SUM2, a.line_action
    FROM AUDITWORKS.sv_av_transaction_line a, AUDITWORKS.sv_store b,
    AUDITWORKS.sv_av_transaction_header c
    WHERE c.transaction_id=a.transaction_id
    AND c.store_no=b.store_no
    AND c.transaction_date >= '17/09/02')
    AND 1=1
    AND a.line_action not in 246
    /* MY ADD-ON */
    AND ( CASE when REF_NO<>0 and LINE_OBJECT in (11, 27) and LINE_ACTION =603 then 1
    when REF_NO=0 and LINE_OBJECT=25 and LINE_ACTION=650
    then 1
    else 0 ) = 1
    /* END ADDON */
    GROUP BY c.store_no,b.store_name,c.transaction_date,c.entry_date_time,c.transaction_no,a.reference_no,a.line_ object,a.line_action
    Aleš

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