Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    Ireland
    Posts
    9

    Unanswered: Insert Select Command Failing

    I have the following INSERT statement which runs sucessfully from sqlplus but if I add it to a procedure the procedure just will not compile with the following error:

    MGR-00072: Warning: PROCEDURE INSERTINTOCKQISTATUSTEMP created with compilation errors.

    Is these a problem with the way that I have selecting the final value in the insert?

    Anyone any ideas?

    INSERT INTO CKQISTATUSTEMP
    (CONTRACTID, KQICLASSNAME, CKQIVALUE, CKQISEVERITY,
    EFFECTIVETIME, WARNINGTHRESHOLD, VIOLATIONTHRESHOLD, STARTTIME,RESOURCETYPE, RESOURCENAME, STATEDURATION, STATETRANSITION,KQIUNIT)
    VALUES
    (new_CONTRACTID, new_KQICLASSNAME, new_CKQIVALUE,
    new_CKQISEVERITY, new_EFFECTIVETIME, new_WARNINGTHRESHOLD, new_VIOLATIONTHRESHOLD,
    new_STARTTIME, new_RESOURCETYPE, new_RESOURCENAME, new_STATEDURATION, new_STATETRANSITION,
    (SELECT a.unit FROM ismadmowner.combinedkqimodel a
    WHERE a.modelname=new_KQICLASSNAME));

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    yes, load the procedure, when you get that statement type: SHOW ERRORS

    post the result
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2003
    Location
    Ireland
    Posts
    9
    Originally posted by The_Duck
    yes, load the procedure, when you get that statement type: SHOW ERRORS

    post the result

    Hi,
    Ok the results from the SHOW ERRORS is
    SVRMGR> show errors
    Errors for PROCEDURE INSERTINTOCKQISTATUSTEMP:
    LINE/COL ERROR
    --------------------------------------------------------------------------------
    26/2 PLS-00103: Encountered the symbol "SELECT" when expecting one of the fo
    27/44 PLS-00103: Encountered the symbol ")" when expecting one of the followi
    Any help?

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Insert Select Command Failing

    What was the error raised?
    SQL-> Show Errors;

    Try this

    PHP Code:
    INSERT INTO CKQISTATUSTEMP (CONTRACTIDKQICLASSNAME
        
    CKQIVALUECKQISEVERITYEFFECTIVETIME
        
    WARNINGTHRESHOLDVIOLATIONTHRESHOLDSTARTTIME,
        
    RESOURCETYPE,  RESOURCENAMESTATEDURATION
        
    STATETRANSITION,KQIUNIT)
    SELECT a.unit new_CONTRACTIDnew_KQICLASSNAME
                
    new_CKQIVALUEnew_CKQISEVERITY
                
    new_EFFECTIVETIMEnew_WARNINGTHRESHOLD
                
    new_VIOLATIONTHRESHOLD,new_STARTTIME
                
    new_RESOURCETYPE,  new_RESOURCENAME
                
    new_STATEDURATIONnew_STATETRANSITION,
    FROM ismadmowner.combinedkqimodel a
    WHERE a
    .modelname=new_KQICLASSNAME); 

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Do what he said.
    Should work
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Nov 2003
    Location
    Ireland
    Posts
    9
    Originally posted by The_Duck
    Do what he said.
    Should work
    That's perfect. Have been playing around with that for ages and it was doing my head in. it's simple when you know how I suppose,
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •