|
|
|
|
|
|
| Re: Updating a column [message #507449 is a reply to message #507440] |
Sun, 15 May 2011 10:00  |
 |
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
From my point of view: you don't need the STATUS column at all. Its value depends on relation between EXPIRY_DATE and SYSDATE. You didn't mention so I suppose that you, actually, mean that both these values are truncated to a DATE (i.e. no hours, minutes, seconds). If that's wrong, you'd have to run that job (suggested in above messages) all the time, round and round (which is stupid). Anyway: calculating and storing such a value into a table is useless - calculate it only when needed (which would be in your report).
Therefore: as you need to know STATUS at the time of running a report, simply use what you already have (obviously, without the STATUS column). Report query might look like this:
select <list of columns you are interested in>,
case when expiry_date < sysdate then 'Expired'
else 'Not expired'
end status
from your_table
where <conditions go here>
How to display different values, based on a parameter (let's call it PAR_STATUS) (whose value can be 'expired', 'not expired' or 'both')? You could use a lexical parameter (let's call it PAR_LEX) here - set its value in After Parameter Form trigger. Something like this:if par_status = 'expired' then
:par_lex := ' and expiry_date <= sydate';
elsif par_status = 'not expired' then
:par_lex := ' and expiry_date > sysdate';
else
:par_lex := ' and 1 = 1';
end if;
You'd use lexical parameter in report's WHERE clause:select <list of columns you are interested in>,
case when expiry_date < sysdate then 'Expired'
else 'Not expired'
end status
from your_table
where <conditions go here>
&par_lex
For more information about lexical parameters, read Reports' Online Help system.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
