"Date math" problem is really "where" clause changed during query

[Chanan & Gaila Morrison <chananm@NETVISION.NET.IL>]

Sorry Charlie, but even SQR won't be able to handle a "where" clause which
you change dynamically during the query itself. When Oracle parses your
"where" clause, the value of #factor is not yet defined and thus = 0.

I don't understand your tables 100%, but I assume that you would be best
off doing a "join" between the WORKORDER and PM tables, so that you can
limit "targcomdate" according to the "pmnum".

Have a good week,

Chanan Morrison

---------------------------------------------------------------
>Here's the code:
>
>begin-procedure Get-WO
>begin-select
>
>wonum  &wonum (1,1)
>description  (,15) wrap 30 2 keep-top
>pmnum   &wopmnum
>wopriority  (,45)
>
>  do get_freq
>
>sysdate-targcompdate (,54) EDIT 9999
>
> PRINT #FACTOR  (,65) EDIT 999
>
>  next-listing no-advance need=2
>
>from workorder
>
>where targcompdate < sysdate - #factor
>and status not in ('CLOSE', 'CAN')
>
> order by targcompdate
>
>end-select
>end-procedure
>
>begin-procedure get_freq
>begin-select
>
>TO_NUMBER(DAYFREQUENCY)  &FREQ1
>
> let #factor = ROUND((.6 * &freq1), 0)
>
> FROM PM
>
>where pm.pmnum = &wopmnum
>
>end-select
>end-procedure
>
>