ODBC Source- incorrect number of result columns.

deepak.shanthamurthy · Thu Apr 13, 2006 12:36 pm

Hi,

In one of my ODBC sources am having an user defined SQL Query with a group by clause in it.
Inspite of the source definition having all the columns required the job fails giving the following error message :
"SQL statement has incorrect number of result columns"
The sql works fine and am also able to view data in the source stage...

Any Idea whats happening??

~
Deepak

kcbland · Post by **kcbland** » Thu Apr 13, 2006 12:49 pm

Post your SQL please.

dwandbi · Post by **dwandbi** » Thu Apr 13, 2006 12:51 pm

To see whether you have same columns listed on Source inputs stage and Target output stage.

thanks,
Tom

deepak.shanthamurthy · Thu Apr 13, 2006 12:58 pm

it is exactly similar to this query here....

Select
dept_id,
department_name,
sum(salary)
From
employee
group by
dept_id,
department_name

kcbland · Post by **kcbland** » Thu Apr 13, 2006 1:03 pm

I know you're new here, so welcome aboard. Please post your exact SQL because soooo many times the "just like this SQL" isn't just like the SQL.

deepak.shanthamurthy · Thu Apr 13, 2006 1:09 pm

Ok here it comes....

select
dept_id,
sub_dept_no,
dept_seq_no,
sum(salary) as sum_salary
from
Employee
group by
dept_id,
sub_dept_no,
dept_seq_no

My source stage has all the four columns in the select clause...

deepak.shanthamurthy · Thu Apr 13, 2006 1:14 pm

well.....
Thanks for the help ....
I just had to tick the Group check box in the source columns tab.....

kcbland · Post by **kcbland** » Thu Apr 13, 2006 1:16 pm

You should try recreating the job using just ODBC-->XFM-->SEQ. If you're on DS 5, there could be something flaky where the job isn't correctly put together underneath the covers.

You don't need to use the "as sum_salary" because DS doesn't care. It runs the query as a cursor and just reads from the cursor. Columns are positional in nature, so when using user-defined SQL you must make sure datatypes, size, and scale match your definitions.

The group by shouldn't be an issue. If you switched your SQL back to generated and just streamed out the data, does it work still?

gateleys · Post by **gateleys** » Thu Apr 13, 2006 1:18 pm

deepak.shanthamurthy wrote:well.....
Thanks for the help ....
I just had to tick the Group check box in the source columns tab.....

There you go

. Anyways, job's done.

gateleys

kcbland · Post by **kcbland** » Thu Apr 13, 2006 1:24 pm

Hmmm, that shouldn't have made any difference. With generated SQL, that matters because it is writing the query for you and would add a GROUP BY clause with your columns. You would also have to put something into the DERIVATION column to MIN/MAX/SUM/COUNT/etc the non-grouping columns.

It sounds like somehow your query was generated and not user-defined.

deepak.shanthamurthy · Thu Apr 13, 2006 2:36 pm

Well.... the job is quite not done....
it works when i just have this source write to a file....
but when i join this source with another table it continues to plague me with the same error............

ray.wurlod · Post by **ray.wurlod** » Thu Apr 13, 2006 4:18 pm

Does the other table have exactly the same four columns defined exactly the same way?

deepak.shanthamurthy · Thu Apr 13, 2006 5:57 pm

No the other table just has the 3 key columns.....
the job is working now....but then i dont know what exactly the problem was.....
As it was just a group by clause .... i changed the query to a generated query and added the sum clause in the derivation as suggested by one of the responses above...and it worked fine....
but iam still not sure why it did not work when i used a user defined query which was exactly the same......
Thanx for the help

chulett · Post by **chulett** » Thu Apr 13, 2006 7:40 pm

More than likely because it wasn't exactly the same. The trick is figuring out what the difference was...