Does anyone know what Baan equivelant funtion to use in place of sql "DISTINCT". I am trying to do a select to return a distinct recordset. I have a table with the following structure

Order Skid Item Weight
12345 1 1234 0
12345 1 5754 0
12345 1 4543 0

I would like the returned recordset to display only one record from the above which I could globally update the weight. On the form it should look like

Order Skid Weight
12345 1

What I am getting is

Order Skid Weight
12345 1 0
12345 1 0
12345 1 0

Any Ideas?

Use Group by command. Baan sql does not not have command like "DISTINCT"

Hi

You can also use "as set with 1 rows" (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_set_specification). Here are some example links:

http://www.baanboard.com/baanboard/showthread.php?t=12294
http://www.baanboard.com/baanboard/showthread.php?p=83492

Here is the Group By link (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_group_by)

I hope it helps,

- George

I dont think u can use group by if you trying to update some data in your table.

A possible sql stmt that you can write is -

domain tcorno prev.order

prev.order = 0

select table.*
from table for update
selectdo
if prev.order <> table.order then
<Update Weight>
else
continue
endif
prev.order = table.order
endselect

Note if you want to make sure that your output is a unique combination of Order and Skid, then you'll need to have another variable prev.skid and your if stmt would change to

if prev.order <> table.order and prev.skid <> table.skid ...

Needless to say you'll have to assign prev.skid = table.skid after teh endif.

Hope this helps.

MG.

If you use the selecteos section in your query, all actions there will be performed only once per criteria.

From a performance perspective and based on your example, the set command will be the easiest and fastest solution.

Francesco,
I believe Selecteos section will be executed only once for the entire SQL and not once per criteria. So if your query returns 10 records, then selecteos will execute once only at the end of processing the 10th record.

MG.