Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    10

    Question Unanswered: Query Help - SELECT - CASE Statement

    The below query gives me M, A or B depending WHERE the COLA is present.
    If present in TBLM --> gives M
    present in TBLA --> gives A
    present in TBLB --> gives B


    SELECT CASE
    WHEN A.COLA IS NOT NULL
    AND B.COLA IS NOT NULL THEN 'M'
    WHEN A.COLA IS NOT NULL THEN 'A'
    WHEN B.COLA IS NOT NULL THEN 'B'
    END
    FROM TBLM
    LEFT OUTER JOIN TBLA
    ON A.COLA = M.COLA
    LEFT OUTER JOIN TBLB
    ON B.COLA = M.COLA

    WHERE M.COLA = XXX

    I need to convert the SQL to give me 'Y' when COLA is present in
    1) TBLM and TBLA
    OR
    2) TBLM and TBLB
    OR
    3) TLM and TBLA and TBLB
    Please advise..

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The phrase "is present" is not so clear for me.
    Is that "NOT NULL"?
    Or, a row was retrieved?

    In either case, I couldn't understand the relationship of the followings.
    If present in TBLM --> gives M
    and
    WHEN A.COLA IS NOT NULL
    AND B.COLA IS NOT NULL THEN 'M'
    Because, rows of TBLM are always retrieved if the predicate "M.COLA = XXX" was true,
    regardless the rows of TBLA or TBLB were retrieved or not.

    Anyhow, answer to your question might be:
    WHEN A.COLA IS NOT NULL OR B.COLA IS NOT NULL THEN 'Y'

  3. #3
    Join Date
    Mar 2009
    Posts
    10
    Yes..I meant 'is present meaning NOT NULL'.

    TBLM is NOT NULL
    and also
    either (TBLA or TBLB) is NOT NULL.

    Sorry for confusion.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    'Y' when
    1) TBLM.COLA IS NOT NULL and TBLA.COLA IS NOT NULL
    OR
    2) TBLM.COLA IS NOT NULL and TBLB.COLA IS NOT NULL
    OR
    3) TBLM.COLA IS NOT NULL and TBLA.COLA IS NOT NULL and TBLB.COLA IS NOT NULL

    WHEN TBLA.COLA IS NOT NULL OR TBLB.COLA IS NOT NULL THEN 'Y'

    Because, if TBLM.COLA IS NULL,
    no row of TBLA nor TBLB will be joined then TBLA.COLA and TBLB.COLA are always NULL.
    Last edited by tonkuma; 04-20-09 at 01:54.

  5. #5
    Join Date
    Mar 2009
    Posts
    10
    Yes..that works..many 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
  •