Probelm with SQL Query using UNION

thom · Post by **thom** » Wed Jul 08, 2009 4:08 pm

I have a workshop registration BO that allows for entry of two workshops (W1, W2) - I created a query using the SQL Form that basically gives me a list of all registrants by workshops. It is

SELECT Workshop.W1, Workshop.DisplayName FROM EDU_WORKSHOP
UNION
SELECT Workshop.W1, Workshop.DisplayName FROM EDU_WORKSHOP

When I run the query through the application it gives me a result set that is
DisplayName, W1, W2

is a Union not allowed? Should I do this another way?

thanks

thom · Post by **thom** » Wed Jul 08, 2009 4:12 pm

sorry, just noticed a typo
the query reads

SELECT Workshop.W1, Workshop.DisplayName FROM EDU_WORKSHOP
UNION
SELECT Workshop.W2, Workshop.DisplayName FROM EDU_WORKSHOP

thom · Post by **thom** » Wed Jul 08, 2009 4:26 pm

ok ... I tracked down the problem as to why I was not getting the right result set but know when I run the query

SELECT Workshop.W1, Workshop.DisplayName FROM EDU_WORKSHOP AS Workshop
UNION
SELECT Workshop.W2, Workshop.DisplayName FROM EDU_WORKSHOP
AS Workshop

I get the message
"Business Object WORKSHOP not found in the business space version"

aware_support · Post by **aware_support** » Thu Jul 09, 2009 12:44 am

Database tables in Aware IM have prefix that indicates business space name. Create a simple query not in the SQL form and then switch to the SQL tab and see how they are translated to SQL - your mistake will be obvious then

tazzmaxx · Post by **tazzmaxx** » Wed Oct 21, 2009 1:56 am

I have attempted to create a union query for a very simple join of one date attribute and one text attribute into one column and I am getting the same error as Thom was previously.

My query is the following:

SELECT Appointments.EndTime FROM MEC_APPOINTMENTS AS Appointments
UNION
SELECT Appointments.Description FROM MEC_APPOINTMENTS AS Appointments

I created a simple query and then checked the SQL Form and the exact translation was:

SELECT Appointments.EndTime FROM MEC_APPOINTMENTS AS Appointment

I'm sure that I am missing something simple so any help would be appreciated.

Thanks

aware_support · Post by **aware_support** » Wed Oct 21, 2009 10:55 am

Not sure. The next version will allow you to execute database stored procedures, so the problem can be solved this way.

tazzmaxx · Post by **tazzmaxx** » Wed Oct 21, 2009 2:25 pm

Will the executed stored procs results be visible from within the application?

If so this might help me because I really need the results of a UNION style query to display in the application and display in user defined documents.

aware_support · Post by **aware_support** » Thu Oct 22, 2009 12:49 am

Yes, you will be able to implement an Aware IM query as a stored procedure call.

tazzmaxx · Post by **tazzmaxx** » Thu Oct 22, 2009 3:32 am

Maybe that's where I am getting confused...as far as I can tell there is no way to create a UNION query in Aware (using the Simple Form, Rule Form or the SQL Form).

I am also assuming that I would have to create a proc in the database with my union query and then refer to that stored procedure via Aware to run the proc.

I was unsure how the results would then display in Aware?

RocketRod · Post by **RocketRod** » Thu Oct 22, 2009 10:01 pm

You can of course set up an existing external BO that refers to a View as well as a table. I use this quite a lot for tables external to AwareIM but I cant see why it would not work on a view that actually accessed AwareIm tables. You then have all the benefits of a BO for displaying data.

Cheers Rod

aware_support · Post by **aware_support** » Thu Oct 22, 2009 10:27 pm

Answering a question about stored procedures - yes, you would define a stored procedure which can do whatever it likes and use SQL of any complexity including UNION's. As long as the SP returns records of the correct type the Aware IM query will be able to display them using its display settings.