Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett, rschirm, roy
-
xch2005
- Participant
- Posts: 85
- Joined: Fri Apr 29, 2005 3:13 am
- Location: India
Post
by xch2005 »
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
- Participant
- Posts: 85
- Joined: Fri Apr 29, 2005 3:13 am
- Location: India
Post
by xch2005 »
To mention the error,
The error is "Undefine data type 0"
Thanks.
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
-
Contact:
Post
by ray.wurlod »
Is isnull() really the function you want to use, and does it really take two arguments?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
xch2005
- Participant
- Posts: 85
- Joined: Fri Apr 29, 2005 3:13 am
- Location: India
Post
by xch2005 »
Yes, the syntax is as,
ISNULL ( check_expression , replacement_value )
Thanks.
-
chulett
- Charter Member
- Posts: 43085
- Joined: Tue Nov 12, 2002 4:34 pm
- Location: Denver, CO
Post
by chulett »
Are you getting this error from DataStage or the source database? Can you post an unedited / complete version of the error please?
-craig
"You can never have too many knives" -- Logan Nine Fingers
-
qt_ky
- Premium Member
- Posts: 2895
- Joined: Wed Aug 03, 2011 6:16 am
- Location: USA
Post
by qt_ky »
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.
Choose a job you love, and you will never have to work a day in your life. - Confucius