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

chuang4630
chuang4630 used Ask the Experts™
on
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;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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?
awking00Information Technology Specialist

Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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;

Author

Commented:
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;

Author

Commented:
I delete the record immediately after the insertion for the sole purpose of closing the test.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
CommunicationProfiles : ID = 2, Name = 'Test File'
tt_TMP is a physical table. two fields:
RowNo NUMBER
PageNo NUMBER
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.

Author

Commented:
Each page has <=25 rows.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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".
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
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;

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial