0

I'm getting an error on my case statement in SFMC. Is it a simple mistake of syntax?

SELECT *,
CASE WHEN status <> 'active' THEN 'active' ELSE status END AS status
FROM [sample table]
WHERE status <> 'active'

The query's goal is simply to change any non-active status (in this specific data extension) to 'active.' It'll actually be based on a date field, but for now I'm simply having an issue with the case statement.

Error

An error occurred while checking the query syntax. Errors: The column 'status' was specified multiple times for 'Qry'.

5

The problem is that you already have status by select * (status was already present in the table). I would advise you either don't use the select * or change the alias of the case function e.g. Substatus.

If you target de has a primary key, we could speed this alot by Setting the Write Mode to Update and doing this:

SELECT
WhichEverColumnIsYourPrimaryKey
,'active' AS status
FROM [sample table]
WHERE status <> 'active'

If your target DE doesnt have a primary key, you could still speed things up by not using the "CASE" function. Reasoning behind this is that WHERE status <> 'active' is already filtering out all status that are not active. You could just do this.

SELECT

Column1
,Column2
,Column3
,ColumnN
,'active' AS status
FROM [sample table]
WHERE status <> 'active'

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.