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.

  • 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)

  • Wow... I am amazed... Astonished 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 

Reply Children
No Data