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

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
ekwo
Posts: 15
Joined: Mon Nov 29, 2021 11:16 pm

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

Post by 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
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

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

Post by 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
Posts: 15
Joined: Mon Nov 29, 2021 11:16 pm

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

Post by ekwo »

Thank you Bruce :)
this was a cumbersome way of doing it :(

Evelyn
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

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

Post by 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
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

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

Post by PointsWell »

ekwo wrote: Thu Apr 07, 2022 8:24 am 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

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.
ekwo
Posts: 15
Joined: Mon Nov 29, 2021 11:16 pm

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

Post by 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.
Post Reply