Group by clause not working with Ole DB source
I'm querying the Oracle db via the OraOLEDB.Oracle (Native Oracle driver not the msdaora.1).I have a select statement that uses the SUM function but I had to wrap theat column with a CAST function since the pkg didn't like the SUM by itself ..it was complaining about precision not being set even if I set it in the advance tab..there is help on the forum about that..Anyway my issue is that I get a I can click on the preview button and test the statment also it does return a result set. I also copied the sql statement to oracle sql developer and it runs. So I'm not sure why is not running once I execute the pkg in design mode? any suggestions welcome..remember though I can see the result set in the preview button.
July 13th, 2009 8:06pm

Do you get any error? please post any messages you are getting.Also make sure you use an aliasin the sum/cast column. I am guessing that the source may be having issues to derive the column metadata.Rafael Salas | Dont forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2009 8:11pm

here is the output error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E14 Description: "ORA-00937: not a single-group group function".here is my sql statementselect column_a, Cast(Sum(column_b) as number(15, 2)) as column_Totalfrom table_awhere column_id =123group by column_aOrder by column_a
July 13th, 2009 8:18pm

That's weird. It seems like the Oracle engine does not see the group by clause. Would you be able to capture the queryactivity in the ORACLE DB side? the idea is to 'see' the query that SSIS is sending.I just tested this in my side using the ORACLE Attunity source and the OLE DB source components and worked with no problem. I used a query identical to the one you provided above (table and column names different obviously)Rafael Salas | Dont forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2009 8:42pm

Rafael,I found the error... it was a commented line in the sql statement...I deleted the line now it runs....can you believe that...I mean the line was commented out to help me fine another issue with my query. So I didn't want to delete it and like I mentioned the preview button would work...thanks for your help in trying...before...select column_a, Cast(Sum(column_b) as number(15, 2)) as column_Totalfrom table_awhere column_id =123--and column_a = 'Microsoft'group by column_aOrder by column_aafter.....select column_a, Cast(Sum(column_b) as number(15, 2)) as column_Totalfrom table_awhere column_id =123group by column_aOrder by column_a
July 13th, 2009 8:46pm

Yes, I can believe it. I found that comments in a query cause issues the hard way long time ago :) . I was about to ask you if the query you provided was the original one or a simplified version you created for this post, but for reason I did not.I am glad you help yourself. I am going to mark this thread as answered.Rafael Salas | Dont forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2009 8:56pm

Wow- I am so glad I found this topic!I just experienced the exact same thing. Once I took out my commented line, it ran just fine- crazy!Thank You very much!
September 11th, 2009 9:25pm

Thanks for this post.
Free Windows Admin Tool Kit Click here and download it now
April 15th, 2011 10:20am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics