Query using CASE WHEN erroring

Question

I am working on a query to reduce duplicates in our dataset, and I keep running into an error:

"An error occurred while checking the query syntax. Errors: Incorrect syntax near '077'."

The following is my code:

 SELECT x.ContactKey,
case WHEN x.Authority_State_Code__c  =   'AK'   AND x.Authority_Product__c = ['077 SPORTSMAN/TT – 17'] THEN 'AK'
 WHEN x.Authority_State_Code__c  =   'AL'   AND x.Authority_Product__c = ['077 SPORTSMAN/TT – 17'] THEN 'AL'    
 WHEN x.Authority_State_Code__c  =   'AR' AND x.Authority_Product__c = ['077 SPORTSMAN/TT – 17'] THEN 'AR'
 WHEN x.Authority_State_Code__c  =   'WY' AND x.Authority_Product__c = ['077 SPORTSMAN/TT – 17'] THEN  'WY' end as 077SPORTSMANTT

FROM "Base - Accounts Contacts SPA" as x   

We are trying to create a list of authority states based on a product for each record. There are multiple records that I'm trying to combine into one record, with a list of states for the product.

What I have now:

ContactKey	Authority_State_Code__c	Authority_Product__c
user1	077 SPORTSMAN/TT – 17	AK
user1	077 SPORTSMAN/TT – 17	WY

What I am trying to get to:

ContactyKey	077SPORTSMANTT
user1	AK,WY

Any idea's what could be causing the error? I've checked over my syntax and can't find where I'm going wrong.

Answer 1

Looks like you are using column names paired with value incorrectly in the when clause, as per the Data table provided.Also, you need to remove the brackets [ ] from there. So, the correct Query should be like which will generate result in multiple rows :

SELECT x.ContactKey,
case WHEN x.Authority_Product__c =   'AK'   AND x.Authority_State_Code__c= '077 SPORTSMAN/TT – 17' THEN 'AK'
 WHEN x.Authority_Product__c =   'AL'   AND x.Authority_State_Code__c= '077 SPORTSMAN/TT – 17' THEN 'AL'    
 WHEN x.Authority_Product__c =   'AR' AND x.Authority_State_Code__c= '077 SPORTSMAN/TT – 17' THEN 'AR'
 WHEN x.Authority_Product__c =   'WY' AND x.Authority_State_Code__c= '077 SPORTSMAN/TT – 17' THEN  'WY' end as 077SPORTSMANTT

FROM "Base - Accounts Contacts SPA" as x;

So, to get concatenated result use the given SQL query :

SELECT ContactKey, GROUP_CONCAT(077SPORTSMANTT SEPARATOR ', ') AS 077SPORTSMANTT
FROM (
SELECT ContactKey,
CASE
WHEN Authority_Product__c = '077 SPORTSMAN/TT – 17' AND Authority_State_Code__c = 'AK' THEN 'AK'
WHEN Authority_Product__c = '077 SPORTSMAN/TT – 17' AND Authority_State_Code__c = 'AL' THEN 'AL'
WHEN Authority_Product__c = '077 SPORTSMAN/TT – 17' AND Authority_State_Code__c = 'AR' THEN 'AR'
WHEN Authority_Product__c = '077 SPORTSMAN/TT – 17' AND Authority_State_Code__c = 'WY' THEN 'WY'
END AS 077SPORTSMANTT
FROM "Base - Accounts Contacts SPA"
) subquery
GROUP BY ContactKey;