creating reminders

[tyb]

dear frends

i have an action calender table and i want to put reminders for these actions on the date in the calender table.

i think it can be done in this way using an sql query

select cal_id
from cal
where cal_val1 = truncate(sysdate)
or cal_val2 = truncate(sysdate)
or cal_val3 = truncate(sysdate);

1. can any body tell me if it can be done through pl-sql in some simple way (something like "IN") instead of using much " OR " (as i have 40 date columns in this table).

2. how can i make the reports run against these multiple values retrieved by the query, can it be done using data parameter as it stores an array.


plz help, its most urgent

 

[sem]

1. select cal_id
from cal
where truncate(sysdate) in (cal_val1,cal_val2,cal_val3);
2. You've answered your own question

Regards, Dima

http://seminihin.narod.ru

 

[tyb]

thanks dima

but my 2nd question is abt pasing data parameter to report.
heres all i'v done


i've a data parameter "REM" within my form and it contains multiple IDs (say record group), now i want my report to run for these IDs.

i've following code on the when-new-from-instance to run the report on form starting

DECLARE
rg_name VARCHAR2(40) := 'due_dates';
rg_id RecordGroup;
errcode NUMBER;
pl_id paramlist;

BEGIN
rg_id := Find_Group(rg_name);
IF Id_Null(rg_id) THEN
rg_id := Create_Group_From_Query(rg_name,'select styleid from styles
where styles.styleid =( select tnid from tna where PFAUDIT_DT = trunc(sysdate)+2
or
FAUDIT_DT = trunc(sysdate)+2
or
HO_DT = trunc(sysdate)+2)');

END IF;
errcode := Populate_Group(rg_id);


pl_id := get_parameter_list('rem');
if not id_null(pl_id) then
destroy_parameter_list(pl_id);
end if;
pl_id := create_parameter_list('rem');
add_parameter(pl_id,'rem',data_parameter,rg_name);


run_product(reports,'E:\reports+forms\reports\tnareminders.rep',synchronous,runtime,filesystem,pl_id);

END;


and i've used following query

select * from styles
where styles.styleid in :rem

in the report. but nothing gets passed to the report.

 

[sem]

If you need the IN operator, you may use TEXT parameter representing that list and LEXICAL (not bind!) parameter in your report. Your current problem is that SQL engine knows nothing about data parameters as well as about other client application specific features. Another solution is to use that data parameter as a source of your reports but populate other items using formula columns or anything similar.

Regards, Dima

http://seminihin.narod.ru

 

[tyb]

thanks dima

i'll try solving my problem some way else.

ur precious advices always helped me a lot

Best Regards
tyb