Error - not a valid number string '10, 10' at implicit type conversion

In below query i am getting error -

1). [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;339 invalid number: not a valid number string '10, 10' at implicit type conversion 'Backend scheduling job' (OBSJ) (at pos 97)

WHEN T1."TransType" = '1470000049' THEN 
(
    SELECT STRING_AGG(TO_NVARCHAR(OL."Code"), ', ')
    FROM OITM A11
    JOIN OLCT OL ON OL."Code" = A11."Location"
    WHERE A11."ItemCode" IN (
        SELECT DISTINCT A0."ItemCode" 
        FROM ACQ1 A0 
        WHERE A0."DocEntry" = T1."CreatedBy" 
          AND A0."ObjType" = '1470000049'
    )
)

Thanks

Hello Ramco,

If OL."Code" is of a numeric type (INTEGER) and you're doing:

STRING_AGG(OL."Code", ', ')

it tries to do string aggregation without converting the number to a string first, which may work but if you explicitly wrap it in TO_NVARCHAR, and somewhere the result is expected to be numeric it fails.

Try explicitly casting the result to NVARCHAR outside the CASE as well:

WHEN T1."TransType" = '1470000049' THEN
    (
        SELECT STRING_AGG(TO_NVARCHAR(OL."Code"), ', ')
        FROM OITM A11
        JOIN OLCT OL ON OL."Code" = A11."Location"
        WHERE A11."ItemCode" IN (
            SELECT DISTINCT A0."ItemCode"
            FROM ACQ1 A0
            WHERE A0."DocEntry" = T1."CreatedBy"
              AND A0."ObjType" = '1470000049'
        )
    )

And if you’re doing something like this:

CASE ...
    WHEN ... THEN (subquery)
    ELSE (some number)
END

Then this will also fail because all THEN and ELSE clauses in a CASE must return the same type. If ELSE is a number and THEN is a string, you'll get an implicit conversion error.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi1y ago
Hello Ramco,

If OL."Code" is of a numeric type (INTEGER) and you're doing:
```
STRING_AGG(OL."Code", ', ')
```
it tries to do string aggregation without converting the number to a string first, which may work but if you explicitly wrap it in TO_NVARCHAR, and somewhere the result is expected to be numeric it fails.

Try explicitly casting the result to NVARCHAR outside the CASE as well:
```
WHEN T1."TransType" = '1470000049' THEN
    (
        SELECT STRING_AGG(TO_NVARCHAR(OL."Code"), ', ')
        FROM OITM A11
        JOIN OLCT OL ON OL."Code" = A11."Location"
        WHERE A11."ItemCode" IN (
            SELECT DISTINCT A0."ItemCode"
            FROM ACQ1 A0
            WHERE A0."DocEntry" = T1."CreatedBy"
              AND A0."ObjType" = '1470000049'
        )
    )
```
And if you’re doing something like this:
```
CASE ...
    WHEN ... THEN (subquery)
    ELSE (some number)
END
```
Then this will also fail because all THEN and ELSE clauses in a CASE must return the same type. If ELSE is a number and THEN is a string, you'll get an implicit conversion error.
0