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     [SOLVED]about using Global variable in filter...
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]about using Global variable in filter...
 Login/Join
 
Platinum Member
posted
Hi,

I have following global variable within edasprof.prf :

-SET &&LAST_M = DTADD( '&DATEYYMDm' , MONTH, -1);
-SET &&LAST_MD = DTADD('&DATEYYMDm', DAY, -1);
-SET &&THIS_MONTH_FIRST_DAY = EDIT(&YYMD,'9999$$$$') | EDIT(&YYMD,'$$$$99$$') | '01';

  


So what I expected for each of value, should be:
  
&&LAST_M = '2020/08/01' given YYMD type
&&LAST_MD = '2020/08/31' given YYMD type
&&THIS_MONTH_FIRST_DAY = '20200801' given A8


and as following with:
 
&&LAST_M = 43,678.00 (without given type)
&&LAST_MD = 43,708.00 (without given type)
&&THIS_MONTH_FIRST_DAY = 20,2009,901.00 (without given type) 

 


when I try using above global variable within SQL like:

 
SQL PREPARE SQLIN FROM 
SELECT 
   T1.product_id 
FROM 
   order_list T1 
 WHERE 
   T1.shipping_date < '&&LAST_M' 
END

 


and I get following error:

1  FILE(S) LOADED
(FOC14050) DATE IS EXPECTED ON LINE 27 AT ''43678''
(FOC009) Request failed validation, not executed.

  


I assuming &&LAST_M should be '2020/08/01' but as it appear it does not.
So I'm guessing within SQL the global variable is not given as Date type(where &&LAST_M = 43678) to compare as date format. Is there a way to SET global variable as Date format WITHOUT Define any field to specify format for &&LAST_M?(Or any other specific format)

This message has been edited. Last edited by: nox,


WebFOCUS v8.2.06 , Windows
 
Posts: 129 | Registered: August 29, 2019Reply With QuoteReport This Post
Platinum Member
posted Hide Post
43678 is the number of days that 2020/08/01 is after the base date. You can convert this to a YYMD date with the DATECVT function.
-SET &&LAST_M = DATECVT( DTADD('&DATEYYMDm' , MONTH, -1), 'MDYY', 'A8YYMD');
-TYPE &&LAST_M 

If you need to have the slashes in the date:
-SET &&LAST_M = EDIT( DATECVT( DTADD('&DATEYYMDm' , MONTH, -1), 'MDYY', 'A8YYMD'), '9999/99/99');
-TYPE &&LAST_M 

Or you may find it simpler to use the AYM function:
-SET &&LAST_M = AYM(&DATEYY.EVAL&DATEM.EVAL, -1, 'I6YYM') ;
-SET &&LAST_M = EDIT(&&LAST_M, '9999/99/01') ;
-TYPE &&LAST_M 


WebFOCUS 8.2.06
 
Posts: 203 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Awesome, thank you dbeagan!


WebFOCUS v8.2.06 , Windows
 
Posts: 129 | Registered: August 29, 2019Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]about using Global variable in filter...

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