CODEWITHSUNDEEP
sqlgoogle-bigquery
Charl
Charl

Reputation: 161

Conditional SELECT in BIgQuery/SQL

Busy putting together a bit of an abstract dataset by my recent standards and I've hit a bit of a snag.

SELECT
  FORMAT_TIMESTAMP('%d-%m-%y', TIMESTAMP_MICROS(Event_Time)) AS Date,
  Campaign_ID,
  (SELECT Activity_ID WHERE Activity_ID IN ("9024844","9033733","9022293","9062686") as Sales),
  (SELECT Activity_ID WHERE Activity_ID IN ("9024799","9001112","9001133","9021942") as Leads),
  Site_ID_DCM,
  Ad_ID,
  REGEXP_EXTRACT(Other_Data, ';u1=(.+?);u') AS ProductCode, 
  REGEXP_EXTRACT(Other_Data, ';u5=(.+?);u') AS Big3OfferCode,
  REGEXP_EXTRACT(Other_Data, ';u7=(.+?);u') AS CID,
  REGEXP_EXTRACT(Other_Data, ';u50=(.+?);u') AS URL,
  DBM_Line_Item_ID
FROM
  `fifth-tangent-233009.dtf_gmp_cm.p_activity_1187025`
WHERE
  DATE(_PARTITIONTIME) = "2021-04-29"

Now clearly those extra SELECTs can't be there, but how would I select conditionally from the same column, Activity_ID, based on the value of the particular ID, one set if IDs being Sales, and the other Leads, and then display the respective ID in the line.

Upvotes: 0

Views: 1989

Answers (2)

Chris Maurer
Chris Maurer

Reputation: 2625

Or, building on Gordon Linoff's answer, instead of two columns with True/False, make one column with the string 'sales' or 'leads' since the two sets do not overlap.

    CASE WHEN Activity_ID IN ('9024844', '9033733', '9022293', '9062686') THEN 'Sales'
         WHEN Activity_ID IN ('9024799', '9001112', '9001133', '9021942') THEN 'Leads' END as activity_type

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271131

Does case do what you want?

(CASE WHEN Activity_ID IN ('9024844', '9033733', '9022293', '9062686') THEN Activity_ID) as Sales,
(CASE WHEN Activity_ID IN ('9024799', '9001112', '9001133, '9021942') THEN Activity_ID END) as Leads,

Note: I might suggest just using a boolean column as a flag:

(Activity_ID IN ('9024844', '9033733', '9022293', '9062686')) as is_sale,
(Activity_ID IN ('9024799', '9001112', '9001133', '9021942')) as is_Lead,

Upvotes: 2

Related Questions