Home » SQL & PL/SQL » SQL & PL/SQL » HELP TO BUILD A PL/SQL WITH THE FOLLOWING SQL
HELP TO BUILD A PL/SQL WITH THE FOLLOWING SQL [message #282651] |
Thu, 22 November 2007 21:56  |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
Dear Experts,
I have the following SQL for which I need to write a PL/SQL and insert the records into a temp table with the same column header I have in the SQL. Please can anyone help me in this.
SELECT a.RO_NO,c.PO_NO,c.ISS_DATE,c.SUPNAME,d.PUINVNO,d.PUINVDTE,
SUM(b.QTY*b.UNIT_price) "PO AMT",'' "SVC INV",TO_DATE('') "SVC DT"
FROM MROH a, MPOI b, MPOH c,MPUI d
WHERE
a.ID=b.ROHD_ID AND
b.STS='O' AND
(b.FULL_SETT_FLAG='Y' OR b.PARTIAL_SETT_FLAG='Y') AND
b.PO_NO=c.PO_NO AND
C.ISS_DATE < '01-NOV-07' AND
c.PO_NO=d.PO_NO
GROUP BY a.RO_NO,c.PO_NO,c.ISS_DATE,c.SUPNAME,d.PUINVNO,d.PUINVDTE
[Updated on: Thu, 22 November 2007 22:44] Report message to a moderator
|
|
|
|
|
Re: HELP TO BUILD A PL/SQL WITH THE FOLLOWING SQL [message #282688 is a reply to message #282651] |
Fri, 23 November 2007 00:39   |
rebeccah@falcorp.co.za
Messages: 26 Registered: October 2007 Location: midrand
|
Junior Member |

|
|
SELECT a.RO_NO,c.PO_NO,c.ISS_DATE,c.SUPNAME,d.PUINVNO,d.PUINVDTE,
SUM(b.QTY*b.UNIT_price) "PO AMT",'' "SVC INV",TO_DATE('') "SVC DT"
FROM MROH a, MPOI b, MPOH c,MPUI d
WHERE
a.ID=b.ROHD_ID
AND b.STS='O'
AND (b.FULL_SETT_FLAG='Y'
OR b.PARTIAL_SETT_FLAG='Y')
AND b.PO_NO=c.PO_NO
AND C.ISS_DATE < '01-NOV-07'
AND c.PO_NO=d.PO_NO
GROUP BY .RO_NO,c.PO_NO, c.ISS_DATE,
c.SUPNAME, d.PUINVNO, d.PUINVDTE;
i guess your code should be in almost a format like above
could you please sent your table script so that i can understand your question clearer
|
|
|
|
Re: HELP TO BUILD A PL/SQL WITH THE FOLLOWING SQL [message #282701 is a reply to message #282651] |
Fri, 23 November 2007 01:40   |
skooman
Messages: 913 Registered: March 2005 Location: Netherlands
|
Senior Member |
|
|
Quote: | insert the records into a temp table with the same column header I have in the SQL.
|
Do you perhaps mean create a new table and insert the results of the SQL statement?
If so, just do:
CREATE TABLE <table_name> AS
SELECT <here is your statement>
Quote: | SUM(b.QTY*b.UNIT_price) "PO AMT"
,'' "SVC INV"
,TO_DATE('') "SVC DT"
|
It's not a very nice habit to name columns with quotes (and thus keeping the spaces in the name), this can become inconvenient while selecting them later on.
Quote: | C.ISS_DATE < '01-NOV-07'
|
It is a nice habit to use proper date formatting instead of plain strings and put your chance of succes in the hands of implicit conversion, so, this should be something like:
C.ISS_DATE < to_date('01-NOV-2007','dd-mon-yyyy')
This is, if iss_date is a date column (should be, I guess).
PS Goedemorgen Frank!
[Updated on: Fri, 23 November 2007 01:41] Report message to a moderator
|
|
|
Re: HELP TO BUILD A PL/SQL WITH THE FOLLOWING SQL [message #282707 is a reply to message #282688] |
Fri, 23 November 2007 02:23   |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
MS.REBACCA AS PER YOUR REQUEST TABLE SCRIPT
DROP TABLE MROH CASCADE CONSTRAINTS;
CREATE TABLE MROH
(
ID VARCHAR2(12) NOT NULL,
STS VARCHAR2(1) NOT NULL,
RO_DTIME DATE NOT NULL,
RO_NO VARCHAR2(9),
);
DROP TABLE MPOI CASCADE CONSTRAINTS;
CREATE TABLE MPOI
(
ID VARCHAR2(12) NOT NULL,
PO_NO VARCHAR2(13) NOT NULL,
ROJB_ID VARCHAR2(12),
ROHD_ID VARCHAR2(12),
FULL_SETT_FLAG VARCHAR2(1) NOT NULL,
PARTIAL_SETT_FLAG VARCHAR2(1) NOT NULL,
QTY NUMBER(11,2) NOT NULL,
STS VARCHAR2(1) NOT NULL,
UNIT_PRICE NUMBER
);
DROP TABLE MPOH CASCADE CONSTRAINTS;
CREATE TABLE MPOH
(
PO_NO VARCHAR2(13) NOT NULL,
STS VARCHAR2(1) NOT NULL,
ISS_DATE DATE,
SUAC_ENG_NAME VARCHAR2(30)
);
DROP TABLE MPUI CASCADE CONSTRAINTS;
CREATE TABLE MPUI
(
PO_NO VARCHAR2(13) NOT NULL,
PUR_INV_NO VARCHAR2(20) NOT NULL,
AMT NUMBER,
PUR_INV_DATE DATE,
QTY NUMBER(10)
);
DROP TABLE TEMP_STK CASCADE CONSTRAINTS;
CREATE TABLE TEMP_STK
(
RO_NO VARCHAR2(9),
PO_NO VARCHAR2(13),
ISSUE_DATE DATE,
SUPP_NAME VARCHAR2(30),
SUPP_INV_NO VARCHAR2(20),
SUPP_INV_DT DATE,
JOB_AMT NUMBER,
SVC_INV_NO VARCHAR2(9),
SVC_INV_DT DATE
);
MR.FRANK
YES I WANT TO STORE IN THE INTERMEDIATE TABLE TEMP_STK
|
|
|
|
Re: HELP TO BUILD A PL/SQL WITH THE FOLLOWING SQL [message #282728 is a reply to message #282651] |
Fri, 23 November 2007 03:21   |
rebeccah@falcorp.co.za
Messages: 26 Registered: October 2007 Location: midrand
|
Junior Member |

|
|
1 SELECT a.RO_NO,c.PO_NO,c.ISS_DATE,c.SUPNAME,d.PUINVNO,d.PUINVDTE,
2 SUM(b.QTY*b.UNIT_price) "PO AMT",'' "SVC INV",TO_DATE('') "SVC DT"
3 FROM MROH a, MPOI b, MPOH c,MPUI d
4 WHERE
5 a.ID=b.ROHD_ID
6 AND b.STS='O'
7 AND (b.FULL_SETT_FLAG='Y'
8 OR b.PARTIAL_SETT_FLAG='Y')
9 AND b.PO_NO=c.PO_NO
10 AND C.ISS_DATE < '01-NOV-07'
11 AND c.PO_NO=d.PO_NO
12 GROUP BY a.RO_NO,c.PO_NO, c.ISS_DATE,
13* c.SUPNAME, d.PUINVNO, d.PUINVDTE
SQL> /
c.SUPNAME, d.PUINVNO, d.PUINVDTE
*
ERROR at line 13:
ORA-00904: "D"."PUINVDTE": invalid identifier
this column(PUINVDTE) does not exist in your
table (MPUI ) script add
declare
v1 varcha2(20);
v1 varcha2(20);
as many as you need...
begin
select a.s, z.we...
into v1,v2,
where (clause here)
insert into your_table(that you want to populate)
values(v1,v2,v3...)
end;
/
dos this helps?
|
|
|
Re: HELP TO BUILD A PL/SQL WITH THE FOLLOWING SQL [message #282732 is a reply to message #282707] |
Fri, 23 November 2007 03:43  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
kumarvk wrote on Fri, 23 November 2007 09:23 |
MR.FRANK
YES I WANT TO STORE IN THE INTERMEDIATE TABLE TEMP_STK
|
I realize that you want to do that. My question was why. The reason I ask is that many (former) SQLServer/Sybase developers tend to store intermediate results in temporary tables. This is NOT the Oracle way; there are far superior ways of doing it in Oracle.
|
|
|
Goto Forum:
Current Time: Sat Feb 15 02:29:32 CST 2025
|