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

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;
LVL 1
chuang4630Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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?
0
slightwv (䄆 Netminder) 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?
0
awking00Information Technology SpecialistCommented:
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
Determine the Perfect Price for Your 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 with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) 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.
0
chuang4630Author 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;
0
chuang4630Author 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;
0
chuang4630Author Commented:
I delete the record immediately after the insertion for the sole purpose of closing the test.
0
slightwv (䄆 Netminder) 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.
0
slightwv (䄆 Netminder) 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.
0
chuang4630Author Commented:
CommunicationProfiles : ID = 2, Name = 'Test File'
tt_TMP is a physical table. two fields:
RowNo NUMBER
PageNo NUMBER
0
slightwv (䄆 Netminder) 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.
0
chuang4630Author 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.
0
chuang4630Author Commented:
Each page has <=25 rows.
0
slightwv (䄆 Netminder) 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.
0
chuang4630Author 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".
0
slightwv (䄆 Netminder) 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;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chuang4630Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.