How do we create a Query as Select DISTINCT ....

ekwo

Hi experts
How do I create a query that returns distinct values from a Business Object, as we do it the SQL way:
Select distinct DepartmentID from Employee

Is there a simular way of doing this using the FIND statement?

Best regards
Evelyn

BLOMASKY

as far as I know, you can't. Some workarounds:

1). In a query you can call a stored procedure and have it return a "Virtual" result set. So the syntax would look like:

EXEC_SP "RunMySelectDistinct" RETURN BOName
This is easy but the results can ONLY be viewed. You can not "select" a record for more processing.

2). Call a Stored Procedure to Delete and then populate a BO that you can then run a query on. So you would have a 2 line process
line 1: Call the SP, line 2: DISPLAY queryName.

This will create a BO that is a 1st class citizen. You can then treat it as any other AwareBO.

With either Step 1 or 2, there are 2 "hidden" fields you have to return beside the ID. They are BASVERSION (a 1 is fine for this) and BASTIMESTAMP (plug any date in here).

Bruce

ekwo

Thank you Bruce 🙂
this was a cumbersome way of doing it 🙁

Evelyn

BLOMASKY

I think the problem is that with Aware the ENTIRE record is always read. You do NOT specify which column(s) you want returned, therefore a DISTINCT clause can never work since each record has a unique primary key.

There is a 3rd way, you can create a VIEW which has a SELECT DISTINCT and then create a BO that points to an external file (the View!).

Bruce

p.s. good luck.

PointsWell

ekwo wrote
Hi experts
How do I create a query that returns distinct values from a Business Object, as we do it the SQL way:
Select distinct DepartmentID from Employee

Is there a simular way of doing this using the FIND statement?

Best regards
Evelyn

The example you've given suggests that you have two BOs:

Department
Employee

If this is the case then what you are looking for is a query on Departments not Employees

Paraphrasing you need to find Departments where Employees in Department > 0

So something like

FIND Department WHERE ( 
    COUNT Employees WHERE (Department=Employee.psDepartment)>0
    )

[I'm not at my desk so the above may be syntactically incorrect - search the forum for a correct example)

However if you are trying to find distinct values from a BO that does not have a relationship to another BO then the above examples are the approach to follow.

ekwo

Thank you both 🙂
The Employee.Department was only an example.
I don't really have any table that the column relates to.
I've done what Bruce suggested, creating a view.