Data Services SQL Transform Syntax error

[Former Member]

I am more familiar with SQL & new to Data services. I need to write the following in Data Services but get syntax error. Hoping you can help. I have checked other questions and attempted to correct syntax, but need assistance please.

<sql('AnalyticsDB', "SELECT HOUSEHOLD.BUYERNBR,
HOUSEHOLD.EVENTYYMMDD, HOUSEHOLD.HH,
HOUSEHOLD.TRANSDATE, HOUSEHOLD.HHID
FROM HOUSEHOLD WHERE HOUSEHOLD.BUYERNBR IN
(SELECT HOUSEHOLD.BUYERNBR FROM HOUSEHOLD
WHERE '20' + HOUSEHOLD.EVENTYYMMDD > [@g_Max_Date])")

[Former Member]

I updated the syntax but still get error. I think it is the quotes, but I cannot find any good training on SQL syntax in DS.

Found erroneous expression <sql('AnalyticsDB', 'SELECT HOUSEHOLD.BUYERNBR, HOUSEHOLD.EVENTYYMMDD, HOUSEHOLD.HH, HOUSEHOLD.TRANSDATE, HOUSEHOLD.HHID FROM HOUSEHOLD WHERE HOUSEHOLD.BUYERNBR IN (SELECT HOUSEHOLD.BUYERNBR FROM HOUSEHOLD WHERE \'20\' + HOUSEHOLD.EVENTYYMMDD > [@g_Max_Date])'). Check its syntax and fix this expression.

[Former Member]

I even simplified to

sql('AnalyticsDB', 'SELECT * FROM HOUSEHOLD WHERE HOUSEHOLD.EventYYMMDD = 171120')

and still get an error.

Please help - I know this is very easy question.

[former_member198401]

Hi Patty,

You cannot write a Select * in the SQL() function. The SQL function will return only 1 value in the script which will be assigned to a Global variable e.g. $G_Household

For more details refer Data Services Reference Guide

https://www.crystalreports.nl/downloads/8.%20Data%20Services%20en%20Data%20Integrator/SAP%20Data%20S...

[Former Member]

Thank you. I keep seeing this "SQL Transform" mentioned in posts, but I do not have that object available. DS 14.2.x

I am trying to write this in the Query transform - but the SQL Transform sounds like what I need, I just do not have it. Any ideas why not? is it a standard object or is it some kind of add on?

[xiaoming_wu]

Hi Patty,

One example below, if it is a string in a sql, you should use \'value\' here.

sql('test', 'select * from KNA1 where LAND1=\'US\'')

[Former Member]

Thanks, but still get error below. I don't know what I am doing wrong. (yes, it is a double single quote at the end)

Found erroneous expression <sql('AnalyticsDB', 'SELECT * FROM HOUSEHOLD WHERE EventYYMMDD = \'171120\'')

[xiaoming_wu]

Hi Patty,

What's the exact error your see?

I tried to use the following in my env and it worked.
sql('test', 'SELECT * FROM HOUSEHOLD WHERE EventYYMMDD = \'171120\'');

Hope it is not ";" at the end that cause your expression error.

Best regards

Helen