?
Solved

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

Posted on 2014-02-25
17
Medium Priority
?
758 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 78

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 78

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 32

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 78

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 78

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
 
LVL 78

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 78

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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

621 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