on 02-24-2014 7:45 PM
Please help me in converting the below logic into Sybase IQ. Below is from oracle.
NEXT_DAY(a.date_data - 366, TO_CHAR(a.date_data,'DAY'))
RUC,
IQ allows simple date math around whole dates. You could easily implement the Oracle logic like this:
--- NEXT_DAY(a.date_data - 366, TO_CHAR(a.date_data,'DAY'))
-- add the +1 to get the "next day"
-- use DAYNAME() to return the text based day of week name: sunday, monday, etc
DAYNAME ( a.date_data - 366 + 1 )
Mark
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Just to add: NEXT_DAY() in Oracle doesn't just give you the next day (as in: day+1).
When you specify NEXT_DAY(some-date, 'monday'), it'll return the date of the first Monday following 'some-date'. I think there is no direct equivalent available in IQ for this which is why it would need to be written custom.
Now, I just realized I actually have such a SQL function available - if someone wants it, please contact me (rob.verschoor@sap.com). Earlier comments about possible performance penalties for SQL functions in IQ apply.
IQ doesn't have a function corresponding to next_day() so you should write that yourself. It returns the first weekday of the specified type (e.g. a Monday, a Tuesday, or whatever the TO_CHAR() expression evaluates to) which is greater than the date resulting from ( a.data_data - 366), i.e. 366 days back in time.
For the other bits:
- a.data_data - 366 can be implemented in IQ as: DATEADD(day, -366, a.date_data)
- TO_CHAR(a.date_data,'DAY')) can be implemented in IQ as DAYNAME(a.date_data)
HTH,
Rob V.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi RUC,
Rob is right, there is no such a function in IQ. However, if you are going for a custom function, please investigate the UDF chapter and implement it in Java or C (see: SyBooks Online).
Be aware that the SQL "traditional" functions are executed by the catalog engine, so they will be very SLOW. The solution is Java or C.
Dan
i used UDF once (in version 12?) in year 2008/2009 and i found that udf functions where applied to the column BEFORE the rows where selected which means that it was executed once for every row of the table.
This had two implications: You need to make the UDF "null resistent" and it takes longer then necessary, so we couldn´t use it really, the traditional quering was much faster.
is this behaivor fixed in newer IQ versions?
regards
dj
User | Count |
---|---|
96 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.