BusinessObjects Board

unable to Pars SQL () statement in DI

dataservices job design
#1

Hi DI Experts,

I’m new to this forum and I like to ask for some expert advice please.

I’m trying to run the following script in DI 11.7:

if ($G_HODH IS NULL OR $G_HODH_ = 1)

begin

sql('Datastore_temp','insert into test2 
(VARIABLE_NAME, VARIABLE_VALUE, TIMESTAMP) 
	VALUES ("Check1","99" \, sysdate from dual)');

end

I tried using escape character () before every (’) but still no luck. Can you kindly assist?

Mamou (BOB member since 2012-06-27)

Datetime variable issue
#2

Can you post the insert statement alone? Do you need those Double quotes in the data?

Don’t know what datatype your columns are…

sql(‘Datastore_temp’,‘insert into test2 (VARIABLE_NAME, VARIABLE_VALUE, TIMESTAMP) VALUES (’“Check1”’,’“99”’,sysdate from dual)’);

ganeshxp :us: (BOB member since 2008-07-17)

#3

Hi ganeshxp,

The double quotes not part of the statement and it can be removed.
I tried -
Sql(‘Datastore_temp’,‘insert into test2 (VARIABLE_NAME, VARIABLE_VALUE, TIMESTAMP) Values (‘Check1’,‘99’, sysdate from dual)’)

It pars but when i try to run it it fail in this with error SQL not terminated correctly or missing comma

Please help - thanks!

Mamou (BOB member since 2012-06-27)

#4

I’m working at a SQL Server site at the moment and so can’t test your statement. How about using the DS sysdate() function like this:-

$L_Any_variable_name = sysdate();

if ($G_HODH IS NULL OR $G_HODH_ = 1)

begin

sql(‘Datastore_temp’,‘insert into test2
(VARIABLE_NAME, VARIABLE_VALUE, TIMESTAMP)
VALUES (‘Check’,‘99’, {$L_Any_Variable_name})’);

end

Nemesis :australia: (BOB member since 2004-06-09)

#5

I have created a local variable and assigned sydate() value as you suggested

sql(‘Datastore_temp’,‘insert into test2
(VARIABLE_NAME, VARIABLE_VALUE, TIMESTAMP)
VALUES (‘Check’,‘99’, {$L_Any_Variable_name})’);

when tried to run it throughs new error :

2620 3732 RUN-050304 6/6/2013 13:57:42 : <ORA-01861: literal does not match format string

Oracle seems to point to column’s data type error even through the column is date type and data type should match
:hb:

Mamou (BOB member since 2012-06-27)

#6

What are your data formats for columns ‘VARIABLE_NAME’ and "VARIABLE_VALUE’? I think the error message may be referring to one of these columns. In particular, I’m suspicious of VARIABLE_VALUE. If this a numeric column you won’t need the quotes around the value to insert.

Nemesis :australia: (BOB member since 2004-06-09)

#7

sql(‘Datastore_temp’,‘insert into test2
(VARIABLE_NAME, VARIABLE_VALUE, TIMESTAMP)
VALUES (‘Check’,‘99’, [$L_Any_Variable_name])’);

That should make the magic I bet…

ganeshxp :us: (BOB member since 2008-07-17)

#8

Change it to this. Your syntax is written like it is part of a SELECT, which it isn’t.

Sql('Datastore_temp','insert into test2 (VARIABLE_NAME, VARIABLE_VALUE, TIMESTAMP) Values (\'Check1\',\'99\', sysdate)');

eganjp :us: (BOB member since 2007-09-12)

#9

I tried the following:

Sql(‘Datastore_temp’,‘insert into test2 (VARIABLE_NAME, VARIABLE_VALUE, TIMESTAMP) Values (‘Check1’,‘99’, [$L_Sysdate])’)

it through error (RUN-050304 6/6/2013 15:12:59 : <ORA-00917: missing comma)

Please note -(VARIABLE_NAME, VARIABLE_VALUE, TIMESTAMP) just table’s column names that I use to manipulate data and trace ETL progress.

Mamou (BOB member since 2012-06-27)

#10

I will go with what Jim said…It never striked my head…

ganeshxp :us: (BOB member since 2008-07-17)

#11

mamou, you are running into a slew of issues here, possibly because you aren’t familiar with some of the ways Data Services does things.

  1. Passing in a date as a string without specifying the format is a violation one of my cardinal rules. Never, never, ever pass a date as a string unless you wrap it in a function that specifies the format. Why? Because the default date format on the client (job server) is never guaranteed to be the same default date format on the database server. Is 12/24/01 supposed to be MM/DD/YY, DD/MM/YY or YY/DD/MM? If it is the second then you have an invalid date.

Instead, you always do something like this: to_date(‘12/24/2001’,‘MM/DD/YYYY’). That way there is NEVER any question of what the date really is.

In my opinion, not specifying the format is sloppy programming. I have fixed many ETL jobs for clients where this was coded. Keep doing it, that’s how I get paid!

  1. When the square brackets [ and ] are used within a string in Data Services the code or variable within the brackets is evaluated and the resulting string is substituted into the parent string. This would NEVER work: VALUES (‘Check’,‘99’, [$L_Any_Variable_name]) because the value within the variable is a string and the result would be an unquoted string - and a syntax error by the database.

  2. When the squiggly (aka curly) braces { and } are used then the substitution value is wrapped in single quotes. The database won’t give you a syntax error but it could either give you a date conversion error or - even worse - it could incorrectly convert the string to a date.

eganjp :us: (BOB member since 2007-09-12)

#12

guys,

I finally managed to run the insert statement using the following syntax

Sql(‘Datastore_temp’,‘insert into test2 Values (‘Check1’,‘99’, to_date({$L_Sysdate},‘YYYY.MM.DD HH:MI:SS’))’);

Thank you guys for you help advises,

Much appreciated

:lol: :lol:

Mamou (BOB member since 2012-06-27)

#13

Spot on ! :+1: :+1: :+1:

eganjp -I do understand now where it all went wrong and I appreciate your input.

However, i really find it confusing where to use the escape character , comma, curly bracket { and [. (I’hv gone through all SAP BODI documentation and tutorials) but I would really appreciate if some one can direct to me a book/online-resource or can explain the scripting Technics in DI/DS.

Thanks

Mamou (BOB member since 2012-06-27)

#14

You’re welcome. Look in the Technical Manual under “Variable interpolation” in the Data Services Scripting Language section.

eganjp :us: (BOB member since 2007-09-12)