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
How do we create a Query as Select DISTINCT ....
Re: How do we create a Query as Select DISTINCT ....
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
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
Re: How do we create a Query as Select DISTINCT ....
Thank you Bruce
this was a cumbersome way of doing it
Evelyn
this was a cumbersome way of doing it
Evelyn
Re: How do we create a Query as Select DISTINCT ....
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.
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.
-
- Posts: 1459
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: How do we create a Query as Select DISTINCT ....
The example you've given suggests that you have two BOs:
- Department
- Employee
Paraphrasing you need to find Departments where Employees in Department > 0
So something like
Code: Select all
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.
Re: How do we create a Query as Select DISTINCT ....
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.
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.