Syntax error.........

dataservices job design
1

$GV_Count =sql(‘AMP_DEV’,‘SELECT COUNT(*) FROM SOURCE WHERE
INTCODE IS NULL OR
(((to_char(sysdate( ),‘yyyy’)) - to_char(EMP_DOB,‘yyyy’)) <= ‘15’) OR
LOCATION IS NULL’);

Any body rectyfy the syntax.

getting error between of this

](((to_char(sysdate( ),‘yyyy’)) - to_char(EMP_DOB,‘yyyy’)) <= ‘15’) [/color]

Note : (Sysdate-EMP_DOB)<=15

Ex : 2009-2040>=15

Thanks in advance

Siva.dwh (BOB member since 2008-11-13)

2
  1. You try to calculate two varchar fields.

you should try to_number(to_char($date1)) - to_number(to_char($date2)).

  1. sysdate() is not supported in sql

just type sysdate :wink:

zeuhl :fr: (BOB member since 2006-03-06)

3

Thanks zeuhl,

it absolutely correct in oracle only not only this what i have given earlier that one also correct in oracle.

but my issue is in BODI.in BODI there is no to_number function.

Thanks

Siva.dwh (BOB member since 2008-11-13)

4

Hi Siva.dwh -

As zeuhl says, it’s sysdate not sysdate().

Also, the contents of the second part of the SQL function, namely ‘SELECT COUNT(*) FROM SOURCE WHERE…’ will get passed straight to the database. As such, BODI’s functions are not relevant in this. The only BODI function being used here is the SQL one.

The only change from what works in Oracle and what should work in BODI is the “escaping” of the single-quotes.

finbo :uk: (BOB member since 2006-12-15)