error when loading through ODBC

[curiousfellow]

Per student per schoolyear we add a new record. This record contains  (among toher fields) the date of arrival.

Mostly date of arrival equals the start of the schoolyear.

For a report I need to have per student the first date of arrival.

Data is loaded through an ODBC connection from a SQL-server

When I use this script :

FirstDayOfArrival :

Load Key_Studentnumber,

Min(DateOfArrival) as FirstDayOfArrival Group By Key_Studentnumber;

SQL SELECT DateOfArrival,

  DepartmentCode,

  Studentnumber as Key_Studentnumber

FROM CompDB.dbo.Students;

I don't get an error

However, when I want to add the departmentcode for example, I get an error saying : ODBC read failed SQL Select DateOfArrival ......(rest of SQL script)

Script that gives error

FirstDayOfArrival :

Load Key_Studentnumber,

DepartmentCode,,

Min(DateOfArrival) as FirstDayOfArrival Group By Key_Studentnumber;

SQL SELECT DateOfArrival,

  DepartmentCode,

  Studentnumber as Key_Studentnumber

FROM CompDB.dbo.Students;

What do I have to change ?

[quwok]

It looks like you have two commas behind DepartmentCode

Script that gives error

FirstDayOfArrival :

Load Key_Studentnumber,

DepartmentCode,,      <--- too many commas

Min(DateOfArrival) as FirstDayOfArrival Group By Key_Studentnumber;

[sasiparupudi1]

try with a resident load

FirstDayOfArrival :

SQL SELECT DateOfArrival,

  DepartmentCode,

  Studentnumber as Key_Studentnumber

FROM CompDB.dbo.Students;

NoConcatenate

final:

Load Key_Studentnumber,

DepartmentCode,

Min(DateOfArrival) as FirstDayOfArrival

Resident FirstDayOfArrival

Group By Key_Studentnumber;

drop Table FirstDayOfArrival;

hth

Sasi

[jonathandienst]

All non-aggregated fields need to be in the group by (and remove the extra comma):

FirstDayOfArrival :

Load Key_Studentnumber,

DepartmentCode,

Min(DateOfArrival) as FirstDayOfArrival

Group By Key_Studentnumber, DepartmentCode;

SQL SELECT DateOfArrival,

  DepartmentCode,

  Studentnumber as Key_Studentnumber

FROM CompDB.dbo.Students;

[curiousfellow]

Thnx to all for your quick response. I tried jonathans's script and that worked. I will look at the others too.

The double comma was a typo .

Strange that all the other fields have to be in the group by

I want to load much more fields from that table, but when this is necessary I think I use an extra table