3

I'm trying to write this query and the idea is to look at the field "MessageName" and when that field value is equal to 'Text 1 Response', I want to copy the field value from another field called MessageText and write that value as Text1Response in the target data extension called "Responses"

If the MessageName value is "Text 2 Response" then I want to write the MessageText value into Text2Response in the target data extension.

SELECT
Date,
MessageName,
MessageType,
MobileNumber,
CASE MessageName
WHEN MessageName = 'Text 1 Response'
THEN MessageText AS Text1Response
WHEN MessageName = 'Text 2 Response'
THEN MessageText AS Text2Response
END
FROM Responses_Report

I wrote up this query but I'm getting an error: "Incorrect syntax near '='."

I've been digging around online but haven't found anything.

1

I think there is a problem with your "CASE" Function.

Try this:

SELECT
Date,
MessageName,
MessageType,
MobileNumber,
CASE WHEN MessageName = 'Text 1 Response'
THEN 'Text1Response' WHEN MessageName = 'Text 2 Response'
THEN 'Text2Response'  END AS TextResponse
FROM Responses_Report

If your use case requires you to split the responses into different columns, try this:

SELECT
Date,
MessageName,
MessageType,
MobileNumber,
CASE WHEN MessageName = 'Text 1 Response' THEN Text1Response END AS Text1Response 
,CASE WHEN MessageName = 'Text 2 Response' THEN Text2Response END AS Text2Response
FROM Responses_Report

If you want to just select the columns with these values, try this:

SELECT
 Date
,MessageName
,MessageType
,MobileNumber
,MessageText as Text1Response
FROM 
    Responses_Report
WHERE 
    MessageName = 'Text 1 Response' OR MessageName = 'Text 2 Response'

For more information you can read the official documentation HERE

| improve this answer | |
0

I was overthinking this problem and was able to come to a solution. I essentially broke this up into two separate queries, probably not the most eloquent solution but it works just fine.

Query 1:

SELECT
    Date, MessageName, MessageType, MobileNumber, MessageText as Text1Response,
FROM 
    Responses_Report
WHERE 
    MessageName = 'Text 1 Response'

Query 2:

SELECT
    Date, MessageName, MessageType, MobileNumber, MessageText as Text1Response,
FROM 
    Responses_Report
WHERE
    MessageName = 'Text 2 Response'
| improve this answer | |
  • Updating my answer to combine these queries into 1 (save time) – Samuel Dare Mar 24 '17 at 23:21
  • Also you would need to remove the ' , ' after the Text1Response – Samuel Dare Mar 24 '17 at 23:25
-2

This is not valid SOQL query. You cannot do anything that is valid in SQL (this "Case... When..." is not valid ).

You should first query the record, and manipulate the logic in apex code.

Something like:

list<Responses_Report> l_response = [SELECT Date, MessageName, MessageType, MobileNumber
FROM Responses_Report];

for(Responses_Report response : l_response){
    if(response.messagename == 'Text 1 Response)
        response.MessageText = response.text1Response;
    else if(response.messagename =='Text 2 Response)
        response.MessageText = response.test2Response;
}

update l_response;
| improve this answer | |
  • This is a marketing cloud question not Salesforce core – Samuel Dare Mar 24 '17 at 10:53

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.