Solved

Missing Expression Error

Posted on 2012-03-27
7
1,159 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Industry Leaders: 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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

724 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