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 Go to next message
kumarvk
Messages: 211
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 #282655 is a reply to message #282651] Thu, 22 November 2007 22:16 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member


Tough to read the codes. Format it.

Kiran
Re: HELP TO BUILD A PL/SQL WITH THE FOLLOWING SQL [message #282685 is a reply to message #282651] Fri, 23 November 2007 00:25 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, read the OraFAQ Forum Guide to learn how to properly format your code.

Quote:

... insert the records into a temp table with the same column header

What does the bold text mean?

Converting SQL to PL/SQL is a simple task: just put it between BEGIN and END keywords, include INSERT INTO and that should do it:
BEGIN
  INSERT INTO some_table (ro_no, po_no, ..., svc_dt
    SELECT a.ro_no, c.po_no, ... null
    FROM ...
END;
/
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 Go to previous messageGo to next message
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 #282700 is a reply to message #282651] Fri, 23 November 2007 01:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you want to store the results in an intermediate table?
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 Go to previous messageGo to next message
skooman
Messages: 912
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! Wink

[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 Go to previous messageGo to next message
kumarvk
Messages: 211
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 #282713 is a reply to message #282707] Fri, 23 November 2007 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

DON'T POST IN UPPER CASE.

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: multiple SQL statements in dynamic SQL query
Next Topic: How to duplicate Record
Goto Forum:
  


Current Time: Sat Dec 03 08:00:32 CST 2016

Total time taken to generate the page: 0.10434 seconds