TechTip: Exploit DB2 Web Query's Defined and Computed Fields

[Matt.Tyler]

** This thread discusses the article: TechTip: Exploit DB2 Web Query's Defined and Computed Fields **
I need to develop a couple defined fields to mimic the following SQL; Vaeddt Between Current_date -14 Days And Current_date +14 Days Vaeedt is a date in *ISO format. I have attempted to create a couple defined fields using the following code; -* DEFINE BEGIN PastBBKS PastBBKS/MDYY = DATEADD (&DATE, 'D', -14); -* DEFINE END PastBBKS -* DEFINE BEGIN FutureBBKS FutureBBKS/MDYY = DATEADD (&DATE, 'D', 14); -* DEFINE END FutureBBKS However, when I include those two fields in the results they show up as; PastBBKS FutureBBKS 12/18/1900 01/14/1901 My selection criteria I created; WHERE [...] AND (MT_RIMVIAPF.MT_RIMVIAPF.VAEDDT GE PastBBKs) AND (MT_RIMVIAPF.MT_RIMVIAPF.VAEDDT LE FutureBBKS); How do I accomplish what I need? I attempted to use the SQL register value of CURRENT_DATE but that did not work either. Thanks, Matt

[efnkay]

Re:TechTip: Exploit DB2 Web Query's Defined and Computed Fields

** This thread discusses the article: TechTip: Exploit DB2 Web Query's Defined and Computed Fields **
According to the documentation, your &DATE must be in YYMD, MDY, or JUL format? DATEADD: Adding or subtracting a date unit to or from a date You can add or subtract years, months, days, weekdays, or business days from your date. Business days can take a holiday file as input. By default, a business day and a weekday are the same concept. In the following function, the date field must have a format like YYMD, MDY, or JUL. Increment must be an integer. DATEADD(date, ‘Y/M/D/WD/BD’, increment)

[Matt.Tyler]

Re:TechTip: Exploit DB2 Web Query's Defined and Computed Fields

** This thread discusses the article: TechTip: Exploit DB2 Web Query's Defined and Computed Fields **
What I mistook in the documentation is that one could use &DATE in a date function, but it seems that is not the case. I got an answer from Gene Cobb and his solution was to code the following (which follows your answer in that I needed a date field to add or subtract to). I thought &DATE was that value but it is not. -* DEFINE BEGIN Today Today/YYMD = '&DATEYYMD'; -* DEFINE END Today -* DEFINE BEGIN twoWeeksAgo twoWeeksAgo/YYMD = DATEADD (Today ,'D',-14); -* DEFINE END twoWeeksAgo -* DEFINE BEGIN twoWeeksFromToday twoWeeksFromToday/YYMD = DATEADD(Today ,'D', 14); -* DEFINE END twoWeeksFromToday END The above gave me what I wanted.

[D.Abramowitz]

Re:TechTip: Exploit DB2 Web Query's Defined and Computed Fields

** This thread discusses the article: TechTip: Exploit DB2 Web Query's Defined and Computed Fields **
My client is currently using an older (V4R5) version of Web Focus. I can't help thinking that the installation of QU2 is going to do something nasty to the current WebFocus environment. Is anyone aware of any caveats? Dave

[Guest.Visitor]

** This thread discusses the article: TechTip: Exploit DB2 Web Query's Defined and Computed Fields **
Thanks for the awesome and interesting info and its been such a useful information.... thanks for sharing........

_____________________
Credit Card Debt
Remortgage Deals