logic into IQ

[former_member203645]

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'))

[markmumy]

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

[former_member182090]

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.

[former_member182090]

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.

[Former Member]

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

[former_member185199]

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

[markmumy]

Dirk,

The UDFs you refer to in version 12 were only SQL and had very different processing rules.  What we have today, in addition to SQL, are Java and C/C++ UDFs.  The C/C++ UDFs are processed quite differently and much more efficiently than ever before.

Mark

[former_member185199]

Thanks for clarifing Mark, i hope to get a project again where i can use IQ

feel a little rusty already