Sparse lookup failed on different data type.

[allavivek]

Hello ,

I was tryng to do a sparse lookup, the column in table is decimal and the orchestarte column i am passing is varchar.The reason i am passing varchar is, i need to simulate (RT.ID) IN (1093,1094) in query as (RT.ID) IN (ORCHESTRATE.Input). From input i am getting 1093,1094 as varchar in single column, to perform IN operation. where ID is decimal.

Datastage is expecting the ORCHESTRATE column to be as decimal. Throwing out error and aborting job.

Error:
Un-handled conversion error on field "ID " from source type "string" to destination type "decimal[10,0]":
source value="1093,1094 "; the result is non-nullable and there is no handle_null to specify a default value.

Any ideas how to achieve this.
Thank You

[ray.wurlod]

Use CAST(RT.ID AS VARCHAR(10)) in the SELECT statement.

[allavivek]

Use CAST(RT.ID AS VARCHAR(10)) in the SELECT statement.

Yes, Thanks Ray. I totally forgot this CAST. I did tried this one and it ran fine.

One question though. If the source integer column ID is 10 in length and iam doing IN as

(RT.ID) IN (1093,1094,1095)

where the data inside IN is 14, does it perform any truncate on data during sparse lookup?
In this case can i use CAST(RT.ID AS VARCHAR(16)) to avoid possible truncation?

Thanks

[ray.wurlod]

The IN list in the WHERE clause is still being evaluated as INTEGER values, so there is no danger of truncation. The CAST is in the SELECT clause, which only affects what is "displayed" (returned). You should get one row back for each value matched.

[allavivek]

Thanks Ray