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