DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 122

Points: 1269

Post Posted: Wed Apr 02, 2008 12:56 am Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Unix
Hi All,

We have the server job which used to check the saletodateagainstdmsale tables. We usually ran the query with parallel hint SELECT /*+ parallel (SALETODATE,8 ) */ (...). The job ran quite good for couple of months but now the job was aborted by below warnings and error

ORA-24347: Warning of a NULL column in an aggregate function
ORA-12842: Cursor invalidated during parallel execution

When we forced the job after sometime it completed with the warning of a NULL column. Need your approach in order to resolve this issue.


Thanks,
Satheesh
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51928
Location: Sydney, Australia
Points: 281959

Post Posted: Wed Apr 02, 2008 1:56 am Reply with quote    Back to top    

Stop trying to aggregate (group by) a column that might contain null. You've been lucky for two months, now your luck's turned bad - there's now a null in one of the grouping or aggregating columns i ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Sydney and Melbourne

Last edited by ray.wurlod on Fri Oct 19, 2012 6:43 am; edited 1 time in total
Rate this response:  
Not yet rated
djbarham
Participant



Joined: 07 May 2003
Posts: 34
Location: Brisbane, Australia
Points: 417

Post Posted: Thu Sep 01, 2011 12:42 am Reply with quote    Back to top    

I have repeatedly run into this error and for me it is not because of a null in a column that I am grouping on. It is a null in a column that is the subject of an aggregate function, such as MAX.

Normally, aggregate functions like MAX ignore nulls. However, when you use KEEP FIRST, it is different.

eg

max(my_column) keep (dense_rank first order by .......)

if my_column returns a null, I get the ORA-12842 error. If I use the following, I don't get the error:

max(nvl(my_column,' ') keep (dense_rank first order by .......)

However, the null is meaningful.

The error is also related to how the Oracle Connector operates. If I run the same query in Oracle SQL Developer, it does not return the error.

Admitted, it is just a warning, but I'd really prefer jobs not to routinely return warnings if they are operating as intended.
Rate this response:  
Not yet rated
akarsh
Participant



Joined: 09 May 2008
Posts: 45
Location: Pune
Points: 437

Post Posted: Fri Oct 19, 2012 5:21 am Reply with quote    Back to top    

Thanks djbarham,

It worked for me, Very Happy

_________________
Thanks,
Akarsh Kapoor
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours