Solved

How to use With AS Insert into select in PL/SQL correctly

Posted on 2014-02-25
17
721 Views
Last Modified: 2014-02-25
I have the followint script but it does not work. I always get the error: PL/SQL: ORA-00928: missing SELECT keyword. Just cannot figure it out.

set serveroutput on
declare
  v_ProfileName VARCHAR2(32000) :='';
  v_SortBy NUMBER := 1;  -- 1 = Name
  v_SortOrder CHAR := 'A' ;
  v_PageNo NUMBER := 1;
  v_PageLength NUMBER := 25;  
  v_CommProfileIDToFind NUMBER := 2;  
  cv_1 SYS_REFCURSOR := null;
 
   v_PageNoi NUMBER(10,0) := v_PageNo;
   v_Query VARCHAR2(32000);
   v_Where VARCHAR2(32000);
   v_OrderBy VARCHAR2(32000);
   v_OrderDir VARCHAR2(4);
   v_FirstRow NUMBER(10,0);
   v_LastRow NUMBER(10,0);
t1 NUMBER;
t2 NUMBEr;
t3 NUMBEr;

BEGIN


WITH ND (RowNo, PageNo, CommProfileID)
 AS
(SELECT
  ROW_NUMBER() OVER(ORDER BY Name ASC),
  TRUNC(((ROW_NUMBER() OVER(ORDER BY Name ASC)-1)/25)+1),  
  CommProfileID
 FROM    
  CommProfiles
 )
INSERT INTO tt_TMP
SELECT
 (SELECT  NVL(MAX(RowNo),0) FROM ND),
 (SELECT MAX(PageNo) FROM ND WHERE CommProfileID = 2)  ;
DELETE FROM tt_TMP;
END;
0
Comment
Question by:chuang4630
  • 8
  • 8
17 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39885810
That looks pretty much all messed up.  First thing I see is the first SELECT after the INSERT doesn't select FROM anything.

I'm thinking there is a much easier way to do what you want.

Can you provide some sample data and expected results?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39885850
Still think this can be simplified but try:


...
INSERT INTO tt_TMP
VALUES
 ((SELECT  NVL(MAX(RowNo),0) FROM ND),
 (SELECT MAX(PageNo) FROM ND WHERE CommProfileID = 2) );
DELETE FROM tt_TMP;
...

Why are you inserting into tt_tmp then immediately deleting from it?
0
 
LVL 31

Expert Comment

by:awking00
ID: 39886691
begin
for rec in
(select max(rn) rowno, max(pn) pageno from
(select row_number() over (order by Name asc) rn,
trunc(((row_number() over (order by Name asc)-1)/25)+1) pn
from commprofiles
where comprofileid=2))
loop
insert into tt_tmp values(rec.rowno,rec.pageno);
end loop;

delete from tt_tmp; ==>not sure what the purpose of this is as slightwv questioned
end;

Also not sure why you're declaring all of those variables
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39886703
awking00,

Double check your code.

I was initially thinking something like that as well but there is no WHERE on the
(SELECT  NVL(MAX(RowNo),0) FROM ND),


I was thinking the second select might go something like:
case when CommProfileID = 2 then PageNo end

Then a MAX on that.


With the MAX, it should only return one row so not sure why you have the loop.
0
 
LVL 1

Author Comment

by:chuang4630
ID: 39886740
Here are two versions of the script: Script 1 works. Script 2 does not work

Script 1
declare
  v_ProfileName VARCHAR2(32000) :='';
  v_SortBy NUMBER := 1;  -- 1 = Name
  v_SortOrder CHAR := 'A' ;
  v_PageNo NUMBER := 1;
  v_PageLength NUMBER := 25;  
  v_CommProfileIDToFind NUMBER := 2;  
  cv_1 SYS_REFCURSOR := null;
 
   v_PageNoi NUMBER(10,0) := v_PageNo;
   v_Query VARCHAR2(32000);
   v_Where VARCHAR2(32000);
   v_OrderBy VARCHAR2(32000);
   v_OrderDir VARCHAR2(4);
   v_FirstRow NUMBER(10,0);
   v_LastRow NUMBER(10,0);
t1 NUMBER;
t2 NUMBEr;
t3 NUMBEr;  
begin
WITH ND (RowNo, PageNo, CommProfileID)
 AS
(SELECT
  ROW_NUMBER() OVER(ORDER BY Name ASC),
  TRUNC(((ROW_NUMBER() OVER(ORDER BY Name ASC)-1)/25)+1),  
  CommProfileID
 FROM    
  CommunicationProfiles
 )
SELECT
 (SELECT  NVL(MAX(RowNo),0) FROM ND),
 (SELECT MAX(PageNo) FROM ND WHERE CommProfileID = 2)
INTO t1, t2
FROM ND;
DELETE FROM tt_NMS_TMP;
end;
0
 
LVL 1

Author Comment

by:chuang4630
ID: 39886745
script 2:

declare
  v_ProfileName VARCHAR2(32000) :='';
  v_SortBy NUMBER := 1;  -- 1 = Name
  v_SortOrder CHAR := 'A' ;
  v_PageNo NUMBER := 1;
  v_PageLength NUMBER := 25;  
  v_CommProfileIDToFind NUMBER := 2;  
  cv_1 SYS_REFCURSOR := null;
 
   v_PageNoi NUMBER(10,0) := v_PageNo;
   v_Query VARCHAR2(32000);
   v_Where VARCHAR2(32000);
   v_OrderBy VARCHAR2(32000);
   v_OrderDir VARCHAR2(4);
   v_FirstRow NUMBER(10,0);
   v_LastRow NUMBER(10,0);
t1 NUMBER;
t2 NUMBEr;
t3 NUMBEr;  
begin
WITH ND (RowNo, PageNo, CommProfileID)
 AS
(SELECT
  ROW_NUMBER() OVER(ORDER BY Name ASC),
  TRUNC(((ROW_NUMBER() OVER(ORDER BY Name ASC)-1)/25)+1),  
  CommProfileID
 FROM    
  CommunicationProfiles
 )
INSERT INTO tt_NMS_TMP
SELECT
 (SELECT  NVL(MAX(RowNo),0) FROM ND),
 (SELECT MAX(PageNo) FROM ND WHERE CommProfileID = 2)
FROM ND;
DELETE FROM tt_NMS_TMP;
end;
0
 
LVL 1

Author Comment

by:chuang4630
ID: 39886758
I delete the record immediately after the insertion for the sole purpose of closing the test.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39886764
You probably shouldn't select from ND then inside two inline views, then select from ND again in the outer query.

That is horribly inefficient.

Can you post sample data and expected results and let us come up with how we would do it instead of looking at what you have and trying to decipher the requirements?

As far as your last post of: 1 works the other doesn't.  I would go with the one that works!

I assume the second one has some different requirement and you are trying to change it.

I'm not going to walk through your code and look for differences.

Please, sample data and expected results.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39886770
>>I delete the record immediately after the insertion for the sole purpose of closing the test.

If tt_NMS_TMP isn't a GLOBAL TEMPORARY TABLE (GTT) is probably should be.

When properly used a GTT doesn't need deleted from except in very specific instances when you need to reuse it in the same session.
0
 
LVL 1

Author Comment

by:chuang4630
ID: 39886813
CommunicationProfiles : ID = 2, Name = 'Test File'
tt_TMP is a physical table. two fields:
RowNo NUMBER
PageNo NUMBER
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39886831
Your last post doesn't help me.

I would like table definitions, sample data and what you would like inserted into the TMP table, in other words, the expected results after the INSERT.


>>tt_TMP is a physical table

Any reason it isn't a GTT?  The way it looks like you are using it, it is as a temp table where you 'clean up' after you are done.  GTT's do this for you automatically.
0
 
LVL 1

Author Comment

by:chuang4630
ID: 39886840
CommunicationProfiles has two fields:
ID NUMBER = 2
Name VARCHAR2(50) = 'Test Files'

tt_TMP is a physical table. two fields:
RowNo NUMBER
PageNo NUMBER

The purpose is to select the data from CommunicationProfiles  with page and row concept in mind,, insert into tt_TMP. the exepected data in tt_TMP is (1, 1) I am working with the existing system. So bear with me, I am not in the place to change it to GTT.
0
 
LVL 1

Author Comment

by:chuang4630
ID: 39886842
Each page has <=25 rows.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39886859
Getting there...  still need a LOT more information...

OK, I get 'paging'.  I do it all the time with my apps.

What I'm not understanding is:
SELECT  NVL(MAX(RowNo),0) FROM ND
SELECT MAX(PageNo) FROM ND WHERE CommProfileID = 2



I'll ask one last time then I'm giving up on this question:
Please post actual sample data and expected results.

For example:
create table CommunicationProfiles (CommProfileID number, name char(1));
insert into CommunicationProfiles values(1,'a');
insert into CommunicationProfiles values(1,'b');
insert into CommunicationProfiles values(1,'c');
insert into CommunicationProfiles values(2,'d');
commit;


Given the above data I need to retrieve:
1,'a'
2,'d'

because they are the minimum name for each CommProfileID.



This way, I can set up a test case on my end to produce the results without having to walk through you SQL and GUESS what it is supposed to be doing.
0
 
LVL 1

Author Comment

by:chuang4630
ID: 39887054
The data I gave is THE Actual data except the name value. But that does not make any difference.
I am working in the test environment, and there is only ONE record.

create table CommunicationProfiles (CommProfileID number, name varchar2(50));
insert into CommunicationProfiles values(1,'Test File');

The two select statements are fine. They have been using for a while.

My question is why the error of "misswing SELECT keyword".
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39887091
I still think this can be made much more efficient

That said:
move the INSERT outside the WITH:

INSERT INTO tt_NMS_TMP
WITH ND (RowNo, PageNo, CommProfileID)
 AS
(SELECT
  ROW_NUMBER() OVER(ORDER BY Name ASC),
  TRUNC(((ROW_NUMBER() OVER(ORDER BY Name ASC)-1)/25)+1),  
  CommProfileID
 FROM    
  CommunicationProfiles
 )
SELECT
 (SELECT  NVL(MAX(RowNo),0) FROM ND),
 (SELECT MAX(PageNo) FROM ND WHERE CommProfileID = 2)
FROM ND;
0
 
LVL 1

Author Comment

by:chuang4630
ID: 39887248
Thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now