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]

1 Answer 1

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? Commented Mar 3, 2021 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. Commented Mar 3, 2021 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. Commented Mar 3, 2021 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> Commented Mar 3, 2021 at 15:23
  • Just use 'true' AS [phoneUpdated] - no need to convert. Commented Mar 3, 2021 at 20:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .