i need to make a time dimension table.as i understand the custom functions within DI give the number(for week, day or year). wish to give the value say January to the first month. my questions are as following;
1.) how can i do it using DI functions
2.) SQL server has a function DATENAME for it. How can I use it as part of the DI job. would use of sql function be fine. i was trying the following syntax:
sql(‘target’,‘DATENAME(MM,getdate())’) . the function DATENAME is working fine with SQL server but not here.
3.) How can I use Case functionality of SQL server within DI
to_char(date, ‘MON’) should help a little bit at least
the sql() function sends the entire string to the sql server. I assume if you type just this: “DATENAME(MM,getdate())” in the isql program of sql you get an error as well, it has to be something like “select DATENAME(MM,getdate()) from …” Actually, if you go along that route, I woud consider creating a stored procedure in sql server.
decode() is pushed down into a case (at least for Oracle, not sure about SQL Server).
Did ‘decode’ definitely push down to the database? We’ve been looking at the AL_FUNCINFO table and it seems to suggest that decode maps onto an ACTA function… ie. is processed in the jobserver, rather than on the database.
(BOB member since 2004-12-17)