Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    p_cust_nn job executing SQL gets: "invalid identifier" or "invalid number"

    • Article Type: General
    • Product: Aleph
    • Product Version: 20

    Description:
    I'm trying to create a p_cust_09 job which executes an SQL script.

    I have this in the $aleph_proc/p_cust_09:

    set BEGDATE = $p1
    set ENDDATE = $p2

    sqlplus abc50/abc50 @$alephe_root/mysql/cust_09.sql


    And this in the $alephe_root/mysql/cust_09.sql script:

    and (z30_process_status_date >= BEGDATE and z30_process_status_date <= ENDDATE)


    When I submit the job like this:

    csh -f $aleph_proc/p_cust_09 ABC50,20100901,20100908 > & ... &


    it executes, but gets errors on the BEGDATE and ENDDATE parameters.

    If I have this:

    and (z30_process_status_date >= BEGDATE and z30_process_status_date <= ENDDATE)
    *
    ERROR at line 6:
    ORA-00904: "ENDDATE": invalid identifier

    or

    and (z30_process_status_date >= 'BEGDATE' and z30_process_status_date <= 'ENDDATE')
    *
    ERROR at line 6:
    ORA-01722: invalid number


    Though it's not really relevant to this problem, here are the lines from the pc_b_eng xml file:

    <control>
    <date_time>
    <argname>F01</argname>
    <label>* Start Date</label>
    </date_time>
    </control>
    <control>
    <date_time>
    <argname>F02</argname>
    <label>* End Date</label>
    </date_time>
    </control>
    </dialog>


    <!-- Param: P-CUST-09. -->
    <!-- Param: P-BEGDATE 9(8). F01 -->
    <!-- Param: P-ENDDATE 9(8). F02 -->

    (Note: KB 16384-4242 describes the custom setup more completely.)

    Resolution:
    I did this:

    in the $aleph_proc/p_cust_09:

    set BEGDATE = $p1
    set ENDDATE = $p2

    sqlplus abc50/abc50 @$alephe_root/mysql/cust_09.sql $BEGDATE $ENDDATE

    and in the cust_09.sql:

    and (z30_process_status_date >= '&1' and z30_process_status_date <= '&2')

    That worked.


    • Article last edited: 10/8/2013