Problem:

I received an email from a developer regarding a hyphen in a column name that did not compile clean on the mainframe but did compile OK in MFE - as you can see from the email I've pasted below, the last statement in the wrere clause references a field called EXP-DT. In the help, it says the default is NOALLOW-HYPHENS and even when I specified explicitly the NOALLOW-HYPHEN, it still compiled in MFE.

I defined a DB2 cursor in my new program and compiled it in MFE with 'No errors'.

When I moved this program to Endevor and compiled it, it came back with SQLCODE -206.

That's when I realized I had Typo on column name.

But MFE compiled this without problems. Do we know why?

Here is the query and the Typo column name in red.

EXEC SQL                                               

   DECLARE  RMC_CURSOR_2 CURSOR FOR                    

       SELECT                                          

            INT_DIV_ID                                 

           ,HMO_ID                                    

           ,NTW_ID_NBR                                 

           ,NTW_ID                                    

           ,NCPDP_STATE_ID                             

           ,FED_CNTY_CD                                

           ,EFF_DT                                    

           ,EXP_DT                                    

           ,MAC_CD                                    

           ,SERV_TYP_CD                                

           ,DIV_ORIDE_LVL_NO                           

           ,ORIDE_XCEPT_CD                             

           ,ALL_INDEP_CD                               

           ,NCPDP_ID                                   

           ,NPI_ID                                    

           ,CHAIN_CD                                   

           ,FM_USER_ID                                 

           ,FM_TS                                      

           ,STATE_CD                                   

           ,CNTY_NM                                    

       FROM RXNMAC_DIV_CRIT                            

       WHERE    INT_DIV_ID    = :RMC-INT-DIV-ID        

         AND    NTW_ID_NBR    = :RMC-NTW-ID-NBR        

         AND    STATE_CD     >= :RMC-STATE-CD          

         AND    FED_CNTY_CD  >= :RMC-FED-CNTY-CD       

         AND   (NCPDP_ID     >= :RMC-NCPDP-ID          

            OR  CHAIN_CD     >= :RMC-CHAIN-CD)         

         AND   (EFF_DT       <= :RMC-EFF-DT            

            AND EFF_DT       >= :RMC-EFF-DT)           

         AND    EXP-DT       >= :RMC-EXP-DT            

       ORDER BY STATE_CD ASC, FED_CNTY_CD ASC,         

                NCPDP_ID ASC, EFF_DT DESC, EXP_DT DESC

      FOR FETCH ONLY                                   

      OPTIMIZE FOR 1 ROW                               

END-EXEC.                                              

Resolution:

  This is not an E Level for us.  E Level on the mainframe is decided by a number of factors.  They can force the SQL errors to be E Level with the VALIDATE-ERR-LVL directive:

VALIDATE-ERR-LVL

Specifies the error level to use for errors produced when the VALIDATE directive is used.

Syntax:

VALIDATE-ERR_LVL = (error-lvl-code)

Parameters:

error-lvl-code  A one-byte character code to indicate what error level to generate when an SQL error results from validating an SQL statement. Choose from one of the following codes: I = Informational, W = Warning, or S = Severe error.

Properties:

Default:        W

Try adding VALIDATE-ERR-LVL=E and see if this resolves the problem.