Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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 8205M
 
Posts: 25 | Registered: May 18, 2017Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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 8205M
 
Posts: 25 | Registered: May 18, 2017Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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 


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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 8205M
 
Posts: 25 | Registered: May 18, 2017Report 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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders