Issue in SQL statement

xch2005 · Post by **xch2005** » Thu Apr 19, 2012 3:03 pm

Hi,

I am facing a strange issue with try to execute a "user defined sql" which has sum function on a case statement.

sum(case when (rd.rollup_data_point_id = 3 and rd.entity_type_id = 1) then isnull(rd.data_point_float,0) else 0 end) as msref_ltv,

Here the same query is working fine when executing through SQL Advantage (as the source is Sybase)

Is there anything I need to modify in this?

Thanks for your time.

xch2005 · Post by **xch2005** » Thu Apr 19, 2012 3:05 pm

To mention the error,

The error is "Undefine data type 0"

Thanks.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 19, 2012 4:48 pm

Is isnull() really the function you want to use, and does it really take two arguments?

xch2005 · Post by **xch2005** » Fri Apr 20, 2012 2:18 pm

Yes, the syntax is as,

ISNULL ( check_expression , replacement_value )

Thanks.

chulett · Post by **chulett** » Fri Apr 20, 2012 5:21 pm

Are you getting this error from DataStage or the source database? Can you post an unedited / complete version of the error please?

qt_ky · Post by **qt_ky** » Sat Apr 21, 2012 6:24 am

Also post actual data types of rd.data_point_float according to the source systrem and msref_ltv according to metadata in your job columns tab.