CODEWITHSUNDEEP
sql-server
mjconl6291
mjconl6291

Reputation: 29

Having a Case statement for one table in a SQL Union

I am trying to have a CASE statement for one table in a SQL union, but can't quite figure it out. My first thought gave me an unequal number of columns error and now I am getting a syntax error for the column alias.

SELECT
    isd_nm  as "isd",
    CASE 
        WHEN (school_student_id = null) THEN row_id as "student_id"
        ELSE cast(school_student_id as text) as "student_id"
        END,
    school_nm as "school_nm",
    'FormStack' as "source",
    
FROM
    school_reg 
        
UNION

I have multiple tables in the union, but this is the only table I need a CASE statement as it has null values and I need them to be replaced with a different column when that happens.

Upvotes: 0

Views: 615

Answers (1)

forpas
forpas

Reputation: 164139

When you compare against NULL you must use the operator IS and not =.
Also the alias of the column must be placed only once after END of the CASE expression.
You are casting school_student_id to text. Why?
Unless its data type is VARCHAR the casting will fail, but if it is VARCHAR then there is no need to cast it.
Use this:

CASE 
  WHEN school_student_id IS null THEN row_id
  ELSE school_student_id
END AS student_id

Another way to get the same result with COALESCE():

COALESCE(school_student_id, row_id) AS student_id

Upvotes: 2

Related Questions