Solved

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

Posted on 2014-02-25
17
737 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
17 Comments
 
LVL 77

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 77

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 77

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 77

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 77

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 77

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 77

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 77

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

732 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