can any one tell me how can i get list of system variables

him121 · Post by **him121** » Thu Oct 28, 2004 11:52 am

hi..
all.
i want to know where i can get help of getting list of
different system variables like

%CurrentDateIn,%DateTimeIn(?) ,%DateTimeOut..
this are the variables..i am firign in DRS with User defined SQL query....

but i am not able to get the help topics on this..
please let me knw if some help there
thanx

kcbland · Post by **kcbland** » Thu Oct 28, 2004 12:00 pm

Write SQL appropriate to the database you are using.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 28, 2004 3:26 pm

For example, what is SYSDATE for one database's SQL might be CURRENT_DATE in another. If you're writing user-defined SQL it must be correct for the database/driver with which you're communicating.

him121 · Post by **him121** » Thu Oct 28, 2004 11:55 pm

ok..
the syntax is depend upon the Database,,,
but my question is whther all the above sys variables related to date..will be provided by...
ascential..?

bcoz the job i am creating is goin to run on Oracle,DB2,OS/390 and MS-SQL server databases...

so syntax in DRS stage i am not going to change..
so can any one tell me..

are any other sysvariables are there..
if then ..tell the document...also..
thanx

kcbland · Post by **kcbland** » Fri Oct 29, 2004 12:14 am

The stage does not rewrite your query to take advantage of SQL extensions specific to the database. So, make sure you use portable SQL that works across all platforms. If you need values like a system date, you will need to use job parameters and feed them into your job/query. You will NOT be able to write an Oracle query using Oracle functions/hints/SQL-extensions and DataStage 'figures' out the SQL-Server equivalents when pointing the job at SQL-Server.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 29, 2004 12:47 am

DataStage system variables are documented in the DataStage BASIC manual. Some of them are documented in on-line help (the topic System Variables will get you started).

Values generated by these can not, in general, be inserted directly into SQL. You use the system variables in a Transformer stage to generate the appropriate format for your target database. For example:

Code: Select all

Oconv(@DATE, "D-YMD[4,2,2]")

will generate an ISO 8601 format version of the date that the job started.