Home » SQL & PL/SQL » SQL & PL/SQL » converting sql query into a procedure
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
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:36:36 CDT 2024
|