Distinct values from an IDO

Suppose that I have SQL:

Select DISTINCT co_num, due_date

From coitem

where [some condition here]

Here the code will return DISTINCT values. For example if co_num 123 has 5 lines with same due date, then I will get only one row.

How can we achieve DISTINCT feature in IDO Collection?

Is there a way that I can specify that I want to pull only the distinct rows and not duplicates?

Even in Dataview, is there a way to fetch distinct data?

I know this is something very common in data word. But perhaps I am missing something.

0909090854053232.pdf

Find more posts tagged with

Accepted answers

All comments

unknown

Hi, you need to join the table coitem back on itself, joining on the distinct columns and only return the record with the lowest, or highest, primary key value. Assuming you have a primary key (unique field) on the coitem table and lets assume its itemid what you are trying to achieve is this:

SELECT a.co_num, a.due_date
FROM coitem a
LEFT JOIN coitem b ON a.co_num = b.co_num AND a.due_date = b.due_date AND a.itemid > b.itemid
WHERE b.itemid is Null

Create an IDO extension to the IDO for coitem.
Add a second instance of the coitem table to the IDO with an alias different to the original table, in the example I have used a and b
In the join expression, join the fields you want to be distinct and also where the primary key in the original table is greater then (or less than works too) the second instance
When you call the IDO, you will need to add a where clause that the joined table does not exist, i.e. where the primary key is least (or greatest)

Stephen Cena

IDO's support the "Distinct" keyword, but I can't find anything on how its implemented (ie: which column/properties are you considering "distinct"). Depending on how that functions it should work for you.

Shuaib Nazir

Wow... I am amazed... This is a wonderful SQL Script. I really liked the logic of self-join and then getting the largest or smallest.

Thanks Richards, I am grateful.

For benefit of others, here is the working query.

SELECT a.co_num, a.due_date
FROM coitem a
LEFT JOIN coitem b ON a.co_num = b.co_num AND a.due_date = b.due_date AND a.co_line > b.co_line
WHERE b.co_line is Null

Shuaib Nazir

I was also thinking that somewhere I have used distinct in the past. Then I recalled that in IDO Rest API Web Services,, in LoadCollection we can use distinct. But not in IDOs directly.

Copyright © 2025 Infor. All rights reserved.