SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Problem with Syntax CASE WHEN END

    Hi there, I need your help.

    With this sql query I need this output:

    Code:
    +--------+--------------------+
    | Xtr    | Codigo_FLGHT       |
    +--------+--------------------+
    | RAS_SD | Zone FlightCA      |
    | RAS_SD | Zone FlightN       |
    | RAS_SD | Zone FlightOR      |
    | RAS_SD | Zone FlightS       |
    | RAS_SD | Zone FlightO       |
    | RAS_SD | Xtr_SAR            |
    +--------+--------------------+
    But I have in column `Codigo_FLGHT` ALL `null` values.

    Can you help me?

    Please check my query below.

    Thanks in advance.

    Code:
    SELECT
    	Xtr,
    	CASE
    WHEN INSTR(Codigo_FLGHT, 'Zone' > 0) THEN
    	Codigo_FLGHT = MID(Codigo_FLGHT, 6, 100)
    WHEN INSTR(Codigo_FLGHT, 'Xtr' > 0) THEN
    	Codigo_FLGHT = Codigo_FLGHT
    END Codigo_FLGHT
    FROM
    	tbl_myflight
    WHERE
    	1
    AND INSTR(Codigo_FLGHT, 'Zone')
    AND (Xtr LIKE 'RAS_SD%')
    GROUP BY
    	Codigo_FLGHT;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    change this (which is hard to read) --
    Code:
    CASE
    WHEN INSTR(Codigo_FLGHT, 'Zone' > 0) THEN
    	Codigo_FLGHT = MID(Codigo_FLGHT, 6, 100)
    WHEN INSTR(Codigo_FLGHT, 'Xtr' > 0) THEN
    	Codigo_FLGHT = Codigo_FLGHT
    END Codigo_FLGHT
    to this (which also contains your syntax corrections) --
    Code:
    CASE WHEN INSTR(Codigo_FLGHT, 'Zone' > 0) 
         THEN MID(Codigo_FLGHT, 6, 100)
         WHEN INSTR(Codigo_FLGHT, 'Xtr' > 0) 
         THEN Codigo_FLGHT
     END AS Codigo_FLGHT
    the THEN and ELSE portions of the CASE expression must specify values, whereas you were specifying true or false results of an equality comparison

    be aware that if you do not specify an ELSE value, then NULL is the result if all the WHEN tests fail

    and you said that all your results are showing NULLs, so that means both of your WHEN tests are failing

    ask yourself what might be causing INSTR(Codigo_FLGHT, 'Zone' > 0) and INSTR(Codigo_FLGHT, 'Xtr' > 0) to fail

    if you can't see it, you'll need to look up the syntax of the INSTR() function and examine it very carefully

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Now I can, thank you for help
    Code:
    SELECT
    	Xtr,
    	CASE
    WHEN INSTR(Codigo_FLGHT, 'Zone') > 0 THEN
    	MID(Codigo_FLGHT, 6, 100)
    WHEN INSTR(Codigo_FLGHT, 'Xtr') > 0 THEN
    	Codigo_FLGHT
    END AS Codigo_FLGHT
    FROM
    	tbl_myflight
    WHERE
    	1
    AND (
    	INSTR(Codigo_FLGHT, 'Zone')
    	OR INSTR(Codigo_FLGHT, 'Xtr')
    )
    AND (Xtr LIKE 'RAS_SD%')
    GROUP BY
    	Codigo_FLGHT;
    
    +--------+--------------------+
    | Xtr    | Codigo_FLGHT       |
    +--------+--------------------+
    | RAS_SD | Zone FlightCA      |
    | RAS_SD | Zone FlightN       |
    | RAS_SD | Zone FlightOR      |
    | RAS_SD | Zone FlightS       |
    | RAS_SD | Zone FlightO       |
    | RAS_SD | Xtr_SAR            |
    +--------+--------------------+


Bookmarks

Posting Permissions

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