| how do execute this dynamically [message #36694] |
Thu, 13 December 2001 00:31  |
previn
Messages: 3
Registered: December 2001
|
Junior Member |
|
|
DECLARE
G_add varchar2(100);
G_ORDER_DATE_FROM Date;
G_ORDER_DATE_TO Date;
G_INS_DATE_FROM Date;
G_INS_DATE_TO Date;
BEGIN
:G_ORDER_DATE_FROM := '&&1';
:G_ORDER_DATE_TO := '&&2';
:G_INS_DATE_FROM := '&&3';
:G_INS_DATE_TO := '&&4';
if :G_INS_DATE_FROM is null then
G_add := 'and (to_date(install_date) between nvl(:G_INS_DATE_FROM,install_date) and nvl(:G_INS_DATE_TO,install_date))';
else
G_add := ' ';
end if;
select order_number, line_number, order_line_id
, part_number, item_description,install_date
, sum(decode(category_type,'ILMI',1,'ILM',1,0)) ilm_count
, sum(decode(category_type, 'ITM',1,0)) itm_count
, sum(decode(category_type,'IDT',1,0)) idt_count
from atrd_serial_num_ship_v sn
where date_ordered between nvl(:G_ORDER_DATE_FROM,date_ordered)
and nvl(:G_ORDER_DATE_TO,date_ordered)
and decode(:G_INS_DATE_FROM,null,null,.......
if :G_INS_DATE_FROM is not null then we need to get the G_add statement in this place. I tried as ||&G_add but it never worked.
group by order_number, line_number, order_line_id
, part_number, item_description,install_date;
END;
----------------------------------------------------------------------
|
|
|
|
| Re: dynamically [message #36719 is a reply to message #36694] |
Fri, 14 December 2001 05:33  |
SAlapati
Messages: 12
Registered: November 2001
|
Junior Member |
|
|
Read documentation on Dynamic SQl
and (if using oracle 8i) read doc's on this command
EXECUTE IMMEDIATE
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
