Solved

Missing Expression Error

Posted on 2012-03-27
7
1,153 Views
Last Modified: 2012-03-27
Hi,

I'm still trying to find my feet with Oracle, having come from a MS background.

I have this loop that is giving me grief. I get the message as follows when rying to run it - I am sure it is a imple syntax error, but cannot see it for the life of me.

Error report:
ORA-00936: missing expression
ORA-06512: at line 10
00936. 00000 -  "missing expression"
*Cause:    
*Action:


BEGIN  
FOR i IN (SELECT DISTINCT a.WR_Type
     FROM qaoc_WR_Valid_States a
     LEFT JOIN qaoc_Wr_Type b
      ON a.WR_Type = b.WR_Type
    WHERE b.WR_Type_Group_ID = 2) LOOP
   
      BEGIN

         EXECUTE IMMEDIATE 'DELETE qaoc_WR_Valid_States WHERE WR_Type = || i || AND WR_State IN ( ''NotUsed0'',''NotUsed1'' )';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 6 WHERE WR_Type = || i || AND WR_State = ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 7 WHERE WR_Type = || i || AND Sequence_No = 5';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 8 WHERE WR_Type = || i || AND Sequence_No = 6 AND WR_State <> ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 9 WHERE WR_Type = || i || AND WR_State = ''Closed''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 10 WHERE WR_Type = || i || AND WR_State = ''Cancelled''';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( || i ||, ''NotUsed0'', 4, NULL, NULL )';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( || i ||, ''NotUsed1'', 5, NULL, NULL )';

      END;
   END LOOP;
END;
/
0
Comment
Question by:Kelvin Sparks
[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
7 Comments
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 200 total points
ID: 37770494
EXECUTE IMMEDIATE 'DELETE qaoc_WR_Valid_States WHERE WR_Type = || i.WR_Type || AND WR_State IN ( ''NotUsed0'',''NotUsed1'' )';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 6 WHERE WR_Type = || i.WR_Type || AND WR_State = ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 7 WHERE WR_Type = || i.WR_Type || AND Sequence_No = 5';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 8 WHERE WR_Type = || i.WR_Type || AND Sequence_No = 6 AND WR_State <> ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 9 WHERE WR_Type = || i.WR_Type || AND WR_State = ''Closed''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 10 WHERE WR_Type = || i.WR_Type || AND WR_State = ''Cancelled''';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( || i.WR_Type ||, ''NotUsed0'', 4, NULL, NULL )';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( || i.WR_Type ||, ''NotUsed1'', 5, NULL, NULL )';

Open in new window



- you are missing the expression for "i" in the 2nd begin block. change it from "i" to i.WR_Type
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37770510
I still get the same error. WR_Type will be a varchar2 datatype - does that change anything?

Kelvin
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 100 total points
ID: 37770517
yes, op_zaharin has pointed it correct, but you need to do something more besides that..

as you are using a loop variable to get it substituted properly you have to use something like this

EXECUTE IMMEDIATE 'DELETE qaoc_WR_Valid_States WHERE WR_Type = '|| i.WR_Type ||' AND WR_State IN ( ''NotUsed0'',''NotUsed1'' )';
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37770577
- ah yes. i miss that. thanks wasimibm :)
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37770585
OK, we're getting closer.

This is new error - current code below

Error report:
ORA-00904: "KPH": invalid identifier
ORA-06512: at line 10
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

What is "KPH"???


BEGIN  
FOR i IN (SELECT DISTINCT A.WR_TYPE
     FROM qaoc_WR_Valid_States a
     LEFT JOIN qaoc_Wr_Type b
      ON a.WR_Type = b.WR_Type
    WHERE b.WR_Type_Group_ID = 2) LOOP
   
      BEGIN

         EXECUTE IMMEDIATE 'DELETE qaoc_WR_Valid_States WHERE WR_Type = '|| i.WR_TYPE ||' AND WR_State IN ( ''NotUsed0'',''NotUsed1'' )';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 6 WHERE WR_Type = '|| i.WR_TYPE ||' AND WR_State = ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 7 WHERE WR_Type = '|| i.WR_TYPE ||' AND Sequence_No = 5';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 8 WHERE WR_Type = '|| i.WR_TYPE ||' AND Sequence_No = 6 AND WR_State <> ''Findings Issued''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 9 WHERE WR_Type = '|| i.WR_TYPE ||' AND WR_State = ''Closed''';
         EXECUTE IMMEDIATE 'UPDATE qaoc_WR_Valid_States SET Sequence_No = 10 WHERE WR_Type = '|| i.WR_TYPE ||' AND WR_State = ''Cancelled''';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( '|| i.WR_TYPE ||', ''NotUsed0'', 4, NULL, NULL,1 )';
         EXECUTE IMMEDIATE 'INSERT INTO qaoc_WR_Valid_States VALUES ( '|| i.WR_TYPE ||', ''NotUsed1'', 5, NULL, NULL,1 )';

      END;
   END LOOP;
END;
/
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 37770858
>>WR_Type will be a varchar2 datatype
>>What is "KPH"???

Likely one of the WR_TYPE values.

You need to add single quotes to it.  Do this for all of them.

...
WHERE WR_Type = '''|| i.WR_TYPE ||''' AND
...
0
 
LVL 22

Author Closing Comment

by:Kelvin Sparks
ID: 37772869
All good now thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ODBC in excel 2016 in Windows 10 via VBA 16 259
Where Does Time Value Come From for Database Insert or Update 4 38
Read XML values 8 58
oracle query 3 26
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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.
This video shows how to recover a database from a user managed backup

730 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