Select Distinct In Query?

RentProperty

Hi Guys, I want to do a ListLine of a Attribute on a Child Object on the ParentObject but only want unique values. For example.

ParentObject
Field --> Categories = LIST_LINE(ParentObject.Children,',',','Category')

However... I Only want 1 instance of each of the children found for each Category. So if there are 5 children but 3 of them belong to one category and 2 to the other category, then I only want the list line to have the 2 category values.
Otherwise my LIST_LINE will look like: Category1,Category1,Category1,Category2,Category2.

In SQL I believe you could use 'SELECT DISTINCT' or something but is it possible to do this in Aware LIST_LINE Function? Any uncomplicated ideas on achieving this would be greatly appreciated. Thnx in advance.

Hope my question makes sense. 🙂

tford

Hi Hein,

I'm not aware of a way to directly do a SELECT DISTINCT, but you could add another reference attribute to ParentObject called Categories and have a rule which adds a category of child record only if it does not exist yet.

Then you can use the new attribute in LIST_LINE.

RentProperty

Thanks Tom, Yeah I thought I might have to go that route but Hoped there was an easier way to do something that is really so simple.

Anyways, thanks for the feedback. 🙂

BLOMASKY

Whats wrong with writing a SQL Select statement to to the SELECT DISTINCT. The results can easily feed a query. (be happy to help.)

Bruce

crisg0112

Hi Bruce,

BUT you still need to create a BO to hold the data. I wish there is a way to use NON-PERSISTENT Objects to hold the results of the query.

I am thinking about ad-hoc queries and per user.

Do you have any solution for this ?? It will very valuable and helpful to us non-sql gurus