Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2013
    Posts
    8

    Unanswered: Case Statement Problem

    Having a problem figuring out this case statement after the Where in my Query. maybe a case statement isnt the way to go.

    SELECT TRUNC (SYSDATE),
    DODI,
    DOD,
    ACCT_CD,
    SUM (LOT) QTY
    FROM WARSDB.QANSN_DATA
    WHERE PUR<> 'I'
    AND TRIM (DODI) IS NOT NULL
    AND COND IN ('A', 'B', 'C', 'D')
    AND CASE
    WHEN DOD IN
    ('W53XMD', 'W22PVK', 'W44XMF', 'W67G23', 'W25G1R')
    THEN
    ACCT_CD IN ('TRA', 'MAA', ' ')
    ELSE
    Q1.ACCT_CD IN ('TRA', 'MAA')
    END
    GROUP BY DODI
    Q1.DOD
    Q1.ACCT_CD

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    we don't have your tables.
    we don't have you data.
    we don't know which question you want the SQL to answer for you.

    my car won't go.
    tell me how to make my car go.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2013
    Posts
    8
    Didnt think it would matter. I just want to know if I am writing my case correctly.

  4. #4
    Join Date
    Apr 2013
    Posts
    8
    Where the DOD is IN
    ('W53XMD', 'W22PVK', 'W44XMF', 'W67G23', 'W25G1R') I want the records where the ACCT_CD is either ' ', 'TRA', or 'MAA'. For all other records I only want records where the ACCT_CD is 'TRA', 'MAA'.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I just want to know if I am writing my case correctly.
    If Oracle reports syntax error, then it is not correct.
    If you don't get the desired results, then it is not correct.
    What prevents YOU from determining the correctness of the SQL yourself?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2013
    Posts
    8
    Where the DOD is IN
    ('W53XMD', 'W22PVK', 'W44XMF', 'W67G23', 'W25G1R') I want the records where the ACCT_CD is either ' ', 'TRA', or 'MAA'. For all other records I only want records where the ACCT_CD is 'TRA', 'MAA'. I just want to know if this is the correct way to write this. I get the error ORA-00905: missing keyword.

  7. #7
    Join Date
    Apr 2013
    Posts
    8
    Because every possible way i write the Case it wont work. I guess this was the wrong place to ask this question. Ill go somewhere else where i dont have to deal with you.

  8. #8
    Join Date
    Apr 2013
    Posts
    8
    Anacedent, next time you decide to post, make it useful. If you dont have a clue how to help dont post.

    Found the answer somewhere else and i thought i would post it for others. I was stuck on using the case which screwed me up. I made it much harder than it really was. Instead of the case i used this below.

    AND ((DOD IN ('W53XMD', 'W22PVK', 'W44XMF', 'W67G23', 'W25G1R') and ACCT_CD IN ('TRA','MAA',' '))
    OR (DOD NOT IN ('W53XMD', 'W22PVK', 'W44XMF', 'W67G23', 'W25G1R') and ACCT_CD IN ('TRA','MAA')))

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >AND ((DOD IN ('W53XMD', 'W22PVK', 'W44XMF', 'W67G23', 'W25G1R') and ACCT_CD IN ('TRA','MAA',' '))
    >OR (DOD NOT IN ('W53XMD', 'W22PVK', 'W44XMF', 'W67G23', 'W25G1R') and ACCT_CD IN ('TRA','MAA')))


    how would anyone reading your first post know that the above was the needed solution?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Its impossible for acct_cd to be two different values in the same row and where is the alias Q1 defined?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Cbluum16 View Post
    Anacedent, next time you decide to post, make it useful. If you dont have a clue how to help dont post.

    Found the answer somewhere else and i thought i would post it for others. I was stuck on using the case which screwed me up. I made it much harder than it really was. Instead of the case i used this below.

    AND ((DOD IN ('W53XMD', 'W22PVK', 'W44XMF', 'W67G23', 'W25G1R') and ACCT_CD IN ('TRA','MAA',' '))
    OR (DOD NOT IN ('W53XMD', 'W22PVK', 'W44XMF', 'W67G23', 'W25G1R') and ACCT_CD IN ('TRA','MAA')))
    Actually, Anacedent was pretty accurate. While Anacedent can be sharp tongued at times, they are rarely incorrect.

    For what it is worth, I'll bet that this is NOT the correct solution. It certainly does not logically provide what you initially requested.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd bet that a better (simpler and more efficient) solution would be:
    Code:
    (   ACCT_CD IN ('TRA', 'MAA')
    OR (ACCT_CD = ' ' AND DOD IN ('W53XMD', 'W22PVK', 'W44XMF', 'W67G23', 'W25G1R'))
    )
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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