World Writer data selection with current date

[SteveP]

In the DREAM Writer, you can use a date selection like somedate LT *TODAY +21 to get records where somedate is in the next 21 days. However, for World Writer, I get an SQL0104 error.

I want to select records based on the current system date. From the Price file F4106 if that matters. I want to select as follows

Expired Date GT current-date
Effective Date LT current-date.

I must be using the wrong search terms as I am sure this is a common problem.

Camn anyone tell me the syuntax for searching a date field with a comparison to the current date?

 

[Fred Hargett]

I have not done it as I have entered the date as 108094.
You would have to convert UDATE "AS400 reserved word" to the JD format.

 

[Jeff_MontAssoc]

It's been a long time since I have worked with World Writers, but I believe there is a file F00365 (or something similar) that you must link to in your World Writer. Maybe this will help jog some memories of people with more recent experience.

Jeff

 

[Eric Lehti]

Steve, you are probably on an older release, just like me (I am on A73
sp9).

The quick solution is what I posted a few days ago

*TODAY for World writer if at releases older than A73 SP 13
#130752 03/07/08 04:56 PM

Join to F55365 file which you populate each morning with one record.



http://www.jdelist.com/ubb/showthreaded.php?Cat=3D&Number=3D130752&page=3D 0&vi
ew=3Dexpanded&sb=3D5&o=3D31&fpart=3D

Permanent solution, giving you full Date arithmetic capability, is to
upgrade.

 

[SteveP]

Re: RE: World Writer data selection with current date

Thanks for the help. By the way, I am a power user in a company with no IT department, not a proper programmer.

Based on the clues here, I joined to our file F5800 which has a single record representing todays date (field name $aupmj) and created a field called EXPDAYS with the definition

EXPDAYS = BPEFTJ - $AUPMJ

and then select when this is GE 0. This worked!!!

I guess I was hoping for the syntax to use current date and I should have given a crisper example. Here is what I mean.

I would creat a user defined field FDAYS which is something like

BPEFTJ - current(date)

Then I would select where FDAYS GT 0. This would be a self updating solution that would use the system date as the selection at run time without joining to F5800. Unfortunately, the syntax for current(date) above is wrong and I have been fooling around with variations with no success.

Anyway, I now have a solution that works and I thank you folks. In hindsight, I should have figured this out myself but I think I got trapped in the details.

Lastly, I know I am on A7.3 becuase it is on my menu but I do not know how to figure out the SP I am on. Can anyone tell me how to look it up? I have full command line authority and stuff like that (scary eh?)

Adios

 

[scott_parker]

RE: RE: World Writer data selection with current date

That will not work when you go from a date in 1 year to a date in
another year.

I really thought there was an update to WW for 7.3 that allows you to
use *Today in the data selection. I know there is for 8.1


Scott Parker
Programmer Analyst/Technical Specialist
JDE World 8.1/SalesLogix 6.2
Grote Industries LLC

 

[szmyd]

Re: RE: RE: World Writer data selection with current date

There is an update. We put in on a couple of years ago. It was pretty straightforward, and it didn't take too long.

 

[SteveP]

Re: RE: RE: World Writer data selection with current date

Are you sure this won't work? I am not doing date math, just seeing if the effective/expiry dates are GT or LT the system date.

I agree that going from 107350 to 108010 yields some interesting arithmetic but I would expect that just checking GT or LT should still be valid?

The business problem was locating only prices that are "current". With current defined as expiry date later than today and effective date less than or equal to today. Is there a better way of finding "current"? I have the same issue with current items in the bills of material.

And thanks again for the feedback. We have no IT and no-one is going to update JDE here for these small problems when there is a workaround

[ QUOTE ]
That will not work when you go from a date in 1 year to a date in<br>another year.<br><br>I really thought there was an update to WW for 7.3 that allows you to<br>use *Today in the data selection. I know there is for 8.1<br><br><br>Scott Parker<br>Programmer Analyst/Technical Specialist<br>JDE World 8.1/SalesLogix 6.2<br>Grote Industries LLC<br><br><br><br>

[/ QUOTE ]

 

[tpayne]

Re: RE: RE: World Writer data selection with current date

I thought the updates to WW to aalow you to select on *TODAY only work on Cum 13 onwards, but I'm not 100% sure.
It's a very handy patch to apply, it works nicely.
Without it, there isn't really a simple way to check the number of days difference between 2 dates.

 

[marlowmt]

Re: RE: RE: World Writer data selection with current date

I know we are on A7.3, cum 12, and I have no problem using the *TODAY in a WW. But there could have been a SAR done at some point that I wasn't aware of that allows this, not sure.