Probelm with SQL Query using UNION

thom

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

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

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

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

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

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

tazzmaxx

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

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

tazzmaxx

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

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

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.