Home » Developer & Programmer » Reports & Discoverer » Updating a column (Oracle 10g Forms & Reports 6i)
Updating a column [message #507437] Sun, 15 May 2011 05:13 Go to next message
aijazsabir
Messages: 2
Registered: February 2011
Location: Saudi Arabia
Junior Member

Hello Experts!
Hope u all doing fine.
First of all let me clear u that I am a beginner.
i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'

(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.

Please help me in this regard.

I will be thankful to one and all.
Re: Updating a column [message #507438 is a reply to message #507437] Sun, 15 May 2011 05:46 Go to previous messageGo to next message
ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Dear,
1 - Create a procedure that 'll update your STATUS Column on the bases of Sysdate.
2 - Make a batch file.
3 - Run this batch file through Scheduled Tasks (Daily/weekly/Monthly).

4 - After Updation of Status Column you'll get your report based on EXPIRED / NON EXPIRED.


Hope you'll get idea.


Regards,
Irfan

Re: Updating a column [message #507440 is a reply to message #507437] Sun, 15 May 2011 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle has an internal scheduler (even 2).
Use it to schedule the procedure mentioned by Irfan.
See DBMS_JOB.

A good book for you:
Database SQL Reference

Also, Please read OraFAQ Forum Guide.

Regards
Michel
Re: Updating a column [message #507449 is a reply to message #507440] Sun, 15 May 2011 10:00 Go to previous message
Littlefoot
Messages: 21806
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.
Previous Topic: Access to mulitple business areas
Next Topic: Weblogic, Forms and reports 11g - help with report server
Goto Forum:
  


Current Time: Fri Mar 29 00:47:29 CDT 2024