0

I tried to follow the example used in this link, but I'm having trouble getting it to work. I get an err msg (Error saving the Query field.An unmatched parenthesis occurs in the query.). for Mobileconnect to work for SMS I need to append a 1 in front of the number.

SELECT CAST('1' AS VARCHAR(1)) 
+ CAST([Event:Who:Lead:MobilePhone] 
AS VARCHAR(10)) 
AS [Event:Who:Lead:MobilePhone] 
FROM
[SC_TEST2_LEADS_Booking_Reminder]
0

Try using CONVERT (my comparision vs CAST):

SELECT 
    CONCAT('1', CONVERT(VARCHAR, el.MobilePhone))
FROM [EventLead] el

Query Studio has problems with CAST in SELECT.

21
  • thx much this works with some tweaking to add required columns. Now, if I run this in the Automation studio multiple times of course this keeps adding 1's.. Any suggestions on how I can only update each record once? Mar 3 '21 at 0:46
  • 1
    add a column "phoneUpdated" to your de. in the same query set it to true and add a condition WHERE phoneUpdated != true. that way the first execution sets the true marker and all following executions pass on the record. Mar 3 '21 at 7:36
  • Or, if you have clean MobilePhone data and you are sure there won't be any values starting with 1 before the change, you can use check what is the first character of your MobilePhone. If you can make such an assertion, it will let you not extend your tables. Mar 3 '21 at 12:27
  • Thank you both for your suggestions. I've attempted to do the phoneUpdated first but I'm having trouble with the set phoneUpdate == 'true' in my syntax. Here's what I have for the updatePhone:<CODE> SELECT [Event:Id],[Event:Who:Lead:Id], CONCAT('1', CONVERT(VARCHAR, el.[Event:Who:Lead:MobilePhone])) as [Event:Who:Lead:MobilePhone],CONVERT(el.[phoneUpdated],'true') as [phoneUpdated] FROM [SC LEADS Journey - 20201201 - 2021-02-24T111741157] el WHERE phoneUpdated != 'true'</CODE> Mar 3 '21 at 15:23
  • Just use 'true' AS [phoneUpdated] - no need to convert. Mar 3 '21 at 20:11

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.