DSXchange

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

Post your SQL please.

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

thanks,
Tom

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

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

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.

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...

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

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?

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

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.

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............

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

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

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