Help with LET variable

[#1]

Hello

probably doing something not right but following code throwing error "ErrorSource: OraOLEDB, ErrorMsg: ORA-00933: SQL command not properly ended". I am trying to grab data from Oracle table

Let vNOWTIME =  Timestamp(Now()-1);

OLEDB CONNECT32 TO [xxxxxxxxxxxxxxxxx);

SQL SELECT

    "UPD_DT",

     CUSTOMER,
     PRODID,
     PROD,
     PRDNM

FROM "XE256"."ORA_78998465"

WHERE "UPD_DT" >= $(vNOWTIME);

UPD_DT has a timestamp where data was last updated.

When i replace statement "WHERE "UPD_DT" >= $(vNOWTIME);" with WHERE "UPD_DT" >= '01/12/2014';" it works fine. Seems like i am not using LET expression correctly?

[#2]

Hi,

It's probably a formatting tweak.

Can you try the below:

Let vNOWTIME =  Date(Timestamp(Now()-1), 'DD/MM/YYYY');

[fkeuroglian]

Hi , change your definition in your variable

create like this:

let vNOWTIME =  date(today());

the function timestamps will retunr the date,hours,second and mili seconds too.

then in the where clause change to this:

WHERE "UPD_DT" >= '$(vNOWTIME)';

try this good luck

Fernando

[#4]

Yeap seems like a formatting now getting this error

ErrorSource: OraOLEDB, ErrorMsg: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

I guess i probably need to change "UPD_DT" to Date format??

[#5]

tried this too

got following

*ErrorSource: OraOLEDB, ErrorMsg: ORA-00932: inconsistent datatypes:

expected DATE got NUMBER*

I guess i probably need to change "UPD_DT" to Date format??

On Wed, Dec 3, 2014 at 9:58 AM, Fernando Keuroglian <

[#6]

Ok,

Try using the to_date function in the where condition:

WHERE "UPD_DT" >= to_date('$(vNOWTIME)', 'dd/mm/yyyy');

[#7]

Thanks. Seems to have taken this and running successfully, but looks like it is grabbing data more then today date -1...

[danieloberbilli]

what about:

LET vNOWTIME = 'date(today,'DD/MM/YYYY')-1';

where date#(UPD_DT,'DD/MM/YYYY')>= $(vNOWTIME);

[Anonymous · Accepted solution]

Hi,

The format of the date you pass in the where clause must be the same of the database field. Since you tried '01/12/2014' and it worked, let's stick to that format. Create your variable like so:

Let vNOWTIME = Text(Date(Today(1), 'DD/MM/YYYY'));

Now, in the where clause you must enclose the dollar-sign substitution in single quotes:

WHERE "UPD_DT" >= '$(vNOWTIME)';

If you don't, your database engine will not receive a string or a date but an expression (02/12/2014) and will make the two divisions, that is, 2 divided by 12 divided by 2014, and compare the result with UPD_DT.

Hope it helps.

- Bruno

[#10]

Thanks a lot . Working well