troubleshooting Question

How can i get my Stored Procedure to drop and Create a parameter Table

Avatar of Olukayode Oluwole
Olukayode OluwoleFlag for Canada asked on
DatabasesPostgreSQLSQL
23 Comments1 Solution99 ViewsLast Modified:
This problem is a follow up to ny earlier post

"How do i provide a column definition list in my stored procedure"

The slight difference now from my earlier post is that I want the script to

1. Drop a physical table parametertable

2.  Based on 2 parameters supplied (recordidx and companycodex) i want the parameter re written based on the UNION  views

3. I attach a sample  postgresqlsql database contain 3 tables (appurchase, appurchasedetail,, syscompany and parametertable)  to
test the script.

My current working script is shown below

CREATE OR REPLACE FUNCTION public.spqrypurchaseorder_postrecordy(
    IN companycodex text,
    IN recordidx text)
  RETURNS TABLE(postingaccount text, sourcedebitamount numeric, sourcecreditamount numeric, recordid text, company text) AS
$BODY$
BEGIN
delete from parametertable z where z.company = companycodex and z.recordid = recordidx;
insert into parametertable(postingaccount,sourcedebitamount,sourcecreditamount,recordid,company)
select (b.arsaledinventoryaccount,sum(b.appoditemtotal) ,0.00,recordidx,companycodex)
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
group by b.arsaledinventoryaccount,recordidx,companycodex
UNION
select (c.syscompurchasemiscacct,a.appomisccharges ,0.00,recordidx,companycodex)
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.appomisccharges > 0
UNION
select (c.syscompurchasefreightacct,a.apposhipping ,0.00,recordidx,companycodex)
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.apposhipping > 0
UNION
select (c.syscompurchasediscountacct,0.00,a.appodiscountamt ,recordidx,companycodex)
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.appodiscountamt > 0
UNION
select (c.syscompurchaseapacct,0.00,a.appototalamount ,recordidx,companycodex)
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.appototalamount > 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.spqrypurchaseorder_postrecordy(text, text)
  OWNER TO postgres;

I  am also supplying 2 parameters  (NNPC  and PO-1070520)  to test the script


The current error which i have with the script is shown below:

Error screen while executing the script
I will grateful for your help

Olukay
ASKER CERTIFIED SOLUTION
Tomas Helgi Johannsson
Database Administrator / Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 23 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 23 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros