Hi all,

I have a scheduled job that has this code:
SUBSTR(LC.COMMENTS, 1, 50) LINESUBCOMMENT

but it's getting this error:
Task error: ERROR IN SCALAR FUNCTION SUBSTR - FIRST PARAMETER IS NOT A STRING

How do I write scalar functions?

Thanks

quote:

I have a scheduled job that has this code:
SUBSTR(LC.COMMENTS, 1, 50) LINESUBCOMMENT

but it's getting this error:
Task error: ERROR IN SCALAR FUNCTION SUBSTR - FIRST PARAMETER IS NOT A STRING

How do I write scalar functions?

Also, here's the entire SQL statement.

SQL
SELECT

W.INSTALL_START_DATE,

P.PO_SUBMIT_DATE,
P.VENDOR_PROMISE_SHIP_DATE,
P.VENDOR_CODE,
P.VENDOR,

L.CUSTOMER_ID,
L.WO_NUMBER,
L.PO_NUMBER,
L.LINE_NUMBER,
L.LINE_REV_NUMBER,
L.MFG_PART_NUMBER,
L.EQPT_DESC,
L.BOM_FLAG,
L.QUANTITY,
SUBSTR(LC.COMMENTS, 1, 50) LINESUBCOMMENT

FROM
M_WORK_ORDER W,
M_PURCHASE_ORDER P,

M_LINE_ITEMS L LEFT JOIN M_LINE_COMMENTS LC
ON L.CUSTOMER_ID = LC.CUSTOMER_ID
AND L.WO_NUMBER = LC.WO_NUMBER
AND L.PO_NUMBER = LC.PO_NUMBER
AND L.LINE_NUMBER = LC.LINE_NUMBER

You need SQL SQLORA or SQL SQLMSS NOT SQL on its own otherwise you DO NOT pass native SQL through the adapter.

Unless you have the

SET SQLENGINE=SQLMSS/SQLORA/DB2

statement before the SQL SELECT statement.

Good point Mr Mariani

I checked also that its not a null value input to a SCALAR function (one that is not SUM. CNT. AVE. etc) and although math operators baulk at nulls scalar functions do not.