OraFAQ Forum: SQL & PL/SQL » converting sql query into a procedure

Home » SQL & PL/SQL » SQL & PL/SQL » converting sql query into a procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

converting sql query into a procedure [message #1124]

Tue, 02 April 2002 23:01

aadit
Messages: 2
Registered: January 2001

Junior Member

Can any one help in converting the following sql query
into a Procedure, which pulls records from Oracle 8.1.7.1 database.

select
a.invoice_no,d.CUSTOMER_ID,decode( b.system_id,1,'UK','Non_UK') as system_Id,
c.company,
(Round(sum(nvl(b.TOTAL,0)),2)) as order_total,
(Round((sum(b.total))*(b.discount/100),2))as Order_Discount,
(Round((sum(nvl(b.TOTAL,0)) - (sum(b.total))*(b.discount/100)),2)) as Disscted_orders_value,
(Round((sum(nvl(b.TOTAL,0)) - (sum(b.total))*(b.discount/100))*(b.tax/100),2)) as Tax_on_order_after_discount,
(Round((sum(nvl(b.TOTAL,0))) - (sum(b.total))*(b.discount/100)+(Round(sum(nvl(b.TOTAL,0)) - (sum(b.total))*(b.discount/100)))*(b.tax/100),2)) as OrderSubtotal,
(Round((sum(nvl(e.amount,0))),2)) as Paymtent_log_total,
(Round((sum(nvl(g.amount,0))),2)) as Credit_discount,
(Round((sum(nvl(F.amount,0))),2)) as CC_Trans_Total,
(Round((sum(nvl(e.amount,0)))+ (sum(nvl(f.amount,0))) ,2)) as Credit_Total,
(Round((sum(nvl(e.amount,0)))+(sum(nvl(f.amount,0))) ,2)) as Cerdit_Sub_total_Non_UK,
((Round((sum(nvl(b.TOTAL,0))) - (sum(b.total))*(b.discount/100)+(Round(sum(nvl(b.TOTAL,0)) -(sum(b.total))*(b.discount/100)))*(b.tax/100),2))) -
((Round((sum(nvl(e.amount,0)))+(sum(nvl(f.amount,0))) ,1))) as outstanding_Non_UK
from payment_log e,
orders b,
cc_transactions f,
credit_discount g,
customer d,contact c,
invoice a
where
b.order_id=f.order_id(+) and
b.order_id=e.order_id(+) and
b.order_id=g.order_id(+) and
c.CONTACT_ID=d.CUSTOMER_CONT_ID and
b.customer_id=d.customer_id and
a.invoice_no=b.invoice_no(+) and
(b.total is not null and b.total != 0) and
(b.status not in (1,64))
and a.ISSUE_DATE>='1-aug-1990' and
b.system_id!=1
group by
a.invoice_no, c.company,b.discount,b.tax,b.system_id,d.CUSTOMER_ID
order by c.company,outstanding_Non_UK ,a.invoice_no

Report message to a moderator

Re: converting sql query into a procedure [message #1129 is a reply to message #1124]

Wed, 03 April 2002 04:20

Epe
Messages: 99
Registered: March 2002

Member

Hello,

if you want to make a stored procedure :

create or replace procedure PROCEDURE_NAME (var1 IN type1, var2 OUT type2) as
begin
select field_x
into var2
from table_X
where field_y = var1;
end;

The IN and OUT parameters are optional.
For more information see "PL/SQL Program Units" on :
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76939/adg10pck.htm#22244

Success,

epe

Report message to a moderator

Previous Topic:	is there a system table that contains SYSDATE column
Next Topic:	column naming conversion

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 18:31:01 CDT 2024