Focal Point
[SOLVED] SQL.DTDIFF

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8097062386

August 01, 2016, 11:32 AM
Tim P.
[SOLVED] SQL.DTDIFF
Has anyone tried to use this SQL function and gotten it to work? I may be doing something wrong but when i try and run it with two valid dates ( i just hard coded them for testing sake) the translated oracle SQL comes back with:

 (FOC1410) SQL COLUMN NOT FOUND. (FOR TABLE: CHECK ALIAS NAMES)
 : ORA-00904: "DTDIFF": invalid identifier
 : Erroneous token: DTDIFF
  


Is there a specific syntax i'm not aware of?

This message has been edited. Last edited by: Tim P.,


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
August 01, 2016, 01:21 PM
Dan Satchell
I believe function DB_EXPR has replaced SQL. as the preferred method to pass SQL functions directly to the DB.


WebFOCUS 7.7.05
August 02, 2016, 07:46 AM
Tim P.
I'll have to look DB_EXPR up, not familiar with it. I've use sql.replace in a webfocus report before but this one is throwing me for a loop.


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
August 04, 2016, 07:47 AM
Tim P.
I figured it out, i was under the assumption that these functions required the 'SQL.' prefix in front of them but that is incorrect. So this:

DIFFERENCE_IN_DAYS/I6 = SQL.DTDIFF('&YYMD', SOME_DATE_FIELD, DAY);  


Should have been:

DIFFERENCE_IN_DAYS/I6 = DTDIFF('&YYMD', SOME_DATE_FIELD, DAY);  


Good news is my query got a bit faster with this change.


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
August 05, 2016, 08:47 AM
jgelona
I believe 8.1.05 has some new streamlined date functions that more closely mirror SQL functions in format and require fewer parameters. Seems I saw that at Summit.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.