Skip to Content
0

Data Services SQL Transform Syntax error

Nov 22, 2017 at 06:22 PM

71

avatar image
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])")

10 |10000 characters needed characters left characters exceeded
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.

0
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.

0

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%20Services%20Reference%20Guide%20ds_42_reference_en.pdf

0
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?

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Xiaoming Wu
Nov 23, 2017 at 07:24 AM
0

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\'')

Show 2 Share
10 |10000 characters needed characters left characters exceeded
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\'')
0

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

0