Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] SQL.DTDIFF
 Login/Join
 
Platinum Member
posted
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
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I believe function DB_EXPR has replaced SQL. as the preferred method to pass SQL functions directly to the DB.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Reply With QuoteReport This Post
Master
posted Hide Post
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.
 
Posts: 919 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.