CODEWITHSUNDEEP
google-bigquerycaseconcatenation
Praxiteles
Praxiteles

Reputation: 6020

How to use a CONCAT statement with a CASE statement in BigQuery?

Is it possible to use CONCAT with a CASE statement in BigQuery? If so, how?

When we try this code, it gives the error below: 

SELECT 

CONCAT(
     "Age:",
     CASE WHEN Age IS null THEN "" ELSE Age END AS Age
) AS AgeString

FROM [our table]

Error: Encountered " "AS" "AS "" at line 5, column 49. Was expecting: ")" ...

Upvotes: 1

Views: 2467

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

As an alternative - if for some reason that alias is "important" for you for example from documenting prospective and/or making code more verbose/descriptive - you can wrap up CASE with SELECT as below 

#standardSQL
SELECT CONCAT(
  "Age:",
  (SELECT CASE WHEN Age IS NULL THEN "" ELSE Age END AS Age)
) AS AgeString
FROM `project.dataset.table`

Upvotes: 2

Elliott Brossard
Elliott Brossard

Reputation: 33745

The problem is here:

SELECT 

CONCAT(
     "Age:",
     CASE WHEN Age IS null THEN "" ELSE Age END AS Age  -- <- this alias
) AS AgeString

FROM [our table]

Since you can't put an alias inside an expression, this query returns an error using either legacy or standard SQL. The fix is to remove the alias:

SELECT 

CONCAT(
     "Age:",
     CASE WHEN Age IS null THEN "" ELSE Age END
) AS AgeString

FROM [our table]

Upvotes: 3

Related Questions