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

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Using Keywords in DB_EXPR
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Using Keywords in DB_EXPR
 Login/Join
 
Member
posted
Hi all,

I'm attempting to use a DB_EXPR to extract the year from a Postgres timestamp. I'm using InfoAssist+ in WebFOCUS 8205.

In Postgres the SQL would be:
 select EXTRACT (YEAR FROM my_timestamp) 


So I created a DB_EXPR saying:
 DB_EXPR(EXTRACT (YEAR FROM "my_timestamp")) 


However, when I do this, I get an error saying:
quote:
Encountered : "FROM" at line 5, column 39 Was expecting one of...


My assumption is that it is finding the word "FROM" in my DB_EXPR and because this is failing the validation because InfoAssist+ thinks I'm using a reserved word incorrectly.

Does anyone know a workaround for this? We do have a need to use InfoAssist+ (I'm sure I could just write the FOCUS code and it'd work fine).

Thanks in advance!

This message has been edited. Last edited by: FP Mod Chuck,


Dan B.
WebFocus 8203M
 
Posts: 13 | Registered: May 18, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
It may not answer your question, but are you following the rules from this doc ?
Using Functions DB_EXPR

Have you tried to use your expression outside IA+ with another WF tools (AS, IA) and see if it run as expected ?


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2225 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
Martin, thanks for the response.

Yes, it conforms to the DB_EXPR help and I've confirmed I can write it as in the original post in AS and it runs fine. It really seems to be the IA+ validation that's not happy about the FROM keyword.

Any suggestions on getting around that?


Dan B.
WebFocus 8203M
 
Posts: 13 | Registered: May 18, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
At this point, if it works with AS, I think that you should contact Tech Support


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2225 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
It does beg the question if you just want to extract the YEAR from a date or datetime value why not use the FOCUS function for that purpose:
DTPART(MY_TIMESTAMP,YEAR) 

However for SQL expressions for which there is no FOCUS equivalent you could test the core functionality by running a FOCEXEC containing
DEFINE FILE tablename 
 YR/I4=DB_EXPR(extract (YEAR FROM "my_timestamp") );
END
TABLE FILE tablename
PRINT YR
IF READLIMIT EQ 1
END 
 
Posts: 386 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
Originally posted by Clif:
It does beg the question if you just want to extract the YEAR from a date or datetime value why not use...


Can even be simplest
DEFINE FILE GGSALES
YR /YY = DATE;
END
TABLE FILE GGSALES
BY DATE
BY YR
END


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2225 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
Thank you all for the suggestions!

Our main goal is to ensure that the extraction resolves to SQL for SQL Pass Thru. We had used DATECVT to get the Year (and month) but this doesn't resolve to SQL which caused significant performance issues.

There are several Postgres functions (analytic functions) that would be nice to be able to use in DB_EXPR from IA+ but this validation prevents it from happening. I'll write up a case and see where that goes.

Thanks for the suggestions, everyone!


Dan B.
WebFocus 8203M
 
Posts: 13 | Registered: May 18, 2017Reply With QuoteReport This Post
Master
posted Hide Post
Try using the expression DTPART(date, component)

Our retail samples is on our Hyperstage postgress db and when execuring the following code:
  
DEFINE FILE retail_samples/wf_retail
SHIP_YEAR/I5=DTPART( TIME_DATE , YEAR );
END

TABLE FILE retail_samples/wf_retail
BY SHIP_YEAR
BY TIME_DATE
END

returned this SQL trace:
  
 0 NUMBER OF RECORDS IN TABLE=      527  LINES=    527
 0 NUMBER OF RECORDS IN TABLE=      527  LINES=      1
 AGGREGATION DONE ...
 SELECT
 EXTRACT(YEAR FROM T5."TIME_DATE"),
 T5."TIME_DATE"
 FROM
 wrd_wf_retail_time T5
 GROUP BY
 EXTRACT(YEAR FROM T5."TIME_DATE"),
 T5."TIME_DATE"
 ORDER BY
 EXTRACT(YEAR FROM T5."TIME_DATE"),
 T5."TIME_DATE";
 0 NUMBER OF RECORDS IN TABLE=        0  LINES=      0


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 485 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Using Keywords in DB_EXPR

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