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,

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 ?

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?

At this point, if it works with AS, I think that you should contact Tech Support

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 

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

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!

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