UNION QUERY not acting same in Alpha as in SQLServer

[Anita Headley]

I have a UNION query that runs just fine in SQLServer query window, but when I run it in alpha at the SQL Command window or in XBasic with and execute, it only does the second part (after the UNION). It does not give an error of any kind, it just does not return the data from the first part of the query (part before UNION).

SELECT h.TBL_ID, h.TRIBE_NUM as Tribe, h.DEALER_LICENSE_ID as Dealer, d.SPECIES_CODE as Species, d.CATCH_AREA_CODE as Catch_Area, SUM(RPTD_CNT) as Units, SUM(RPTD_LBS_QTY) as Pounds, COUNT(DISTINCT FISH_TICKET_NUM) as Ticket_Count, Count(Distinct INDIAN_ID) as Effort_Fisherman FROM TICKET_MASTER_VIEW h LEFT JOIN TICKET_DETAIL_VIEW d ON h.FISH_TICKET_ID = d.FISH_TICKET_ID WHERE h.fisher_type_code in ('1','2','5') AND d.SPECIES_CODE in ('001','002','003','004','005','006') AND h.TRIBE_NUM in ('28','29','31') AND h.FISHER_TYPE_CODE = '2' AND h.tribe_num IN (SELECT tribe_code FROM tocas_dba.user_tribe WHERE username = 'dholmgren') GROUP BY h.TBL_ID,h.TRIBE_NUM,h.DEALER_LICENSE_ID,d.SPECIES_CODE,d.CATCH_AREA_CODE
UNION
SELECT h.TBL_ID, h.TRIBE_NUM as Tribe, h.DEALER_LICENSE_ID as Dealer, d.SPECIES_CODE as Species, d.CATCH_AREA_CODE as Catch_Area, SUM(RPTD_CNT) as Units, SUM(RPTD_LBS_QTY) as Pounds, COUNT(DISTINCT FISH_TICKET_NUM) as Ticket_Count, Count(Distinct INDIAN_ID) as Effort_Fisherman FROM TICKET_MASTER_VIEW h LEFT JOIN TICKET_DETAIL_VIEW d ON h.FISH_TICKET_ID = d.FISH_TICKET_ID WHERE h.fisher_type_code in ('1','2','5') AND d.SPECIES_CODE in ('001','002','003','004','005','006') AND h.TRIBE_NUM in ('28','29','31') AND h.FISHER_TYPE_CODE = '2' AND h.tribe_num NOT IN (SELECT tribe_code FROM tocas_dba.user_tribe WHERE username = 'dholmgren') GROUP BY h.TBL_ID,h.TRIBE_NUM,h.DEALER_LICENSE_ID,d.SPECIES_CODE,d.CATCH_AREA_CODE ORDER BY h.TRIBE_NUM,h.DEALER_LICENSE_ID,d.SPECIES_CODE,d.CATCH_AREA_CODE;

Any ideas? Has anybody else run into this?

[Davidk]

Re: UNION QUERY not acting same in Alpha as in SQLServer

Try turning off portable SQL... I believe this has worked for me in the past...

cn.PortableSQLEnabled = .f.

[Anita Headley]

Re: UNION QUERY not acting same in Alpha as in SQLServer

Tried that - did not work.

[Anita Headley]

Re: UNION QUERY not acting same in Alpha as in SQLServer

Solved the problem in XBasic by breaking into two querys and running them separately. Am still curious as to why, even in the SQL Command window, it ignores/mis-runs UNIONs. Can't put them in grids either - tried that too.

[Davidk]

Re: UNION QUERY not acting same in Alpha as in SQLServer

I run a SQL Statement with 2 Unions... had the same issue as you until I turned off portable sql. It ran fine under MySQL, but not Alpha. I figured I'd have to do the same... split it into 3 statements... which would have been ok... but didn't need to.

[frankbicknell]

Re: UNION QUERY not acting same in Alpha as in SQLServer

Why don't you just create a sql view using the query then using that view for your data source.

[Anita Headley]

Re: UNION QUERY not acting same in Alpha as in SQLServer

Frank,
Because user is building the query by selecting fields and where criteria and then the program adds part of the where clause and a union or not union based on who logged in. Some users are allowed only data for certain criteria (not their tribe) and all data for other criteria (their tribe). Other users are allowed all data (don't need union).

[Davidk]

Re: UNION QUERY not acting same in Alpha as in SQLServer

I tried a MySQL View, 3 Selects with 2 Unions. Ran fine under Navicat, but not as the source for a List. Part of the challenge is parameters for the view.

[frankbicknell]

Re: UNION QUERY not acting same in Alpha as in SQLServer

I have several grids with the data source from MySql views using unions and they run fine. I can't see why it would be a problem for lists, I have not tried it myself.

[Davidk]

Re: UNION QUERY not acting same in Alpha as in SQLServer

My View has 3 Selects with a WHERE clause per select. Part of the WHERE clause is static... e.g. WHERE flag <> 0. But a 2nd part of the WHERE clause is variable based on a Login UserId. When I take the variable part of the Select out of the View and use a Filter in the List then I do not get all the records I should be getting. If I leave the UserId WHERE clause in the View and assign a static value to it, and remove the Filter from the List, the View does return all records. To work around this I use a Custom XBasic function to feed the List.

[frankbicknell]

Re: UNION QUERY not acting same in Alpha as in SQLServer

David couldn't you return all the records in the view and then filter it in the list or grid as in my case. This works for me.

[Davidk]

Re: UNION QUERY not acting same in Alpha as in SQLServer

Too many records to return. Even with a reduced test database there are 788 records in total, which get filtered down to about between 2 and 5 records I need to display. No reason to return hundreds of records. If I had no other choice then I'd have to live with it. But using a Custom data source for the List allows me to build a filtered Select with Unions and returns the few records I want.

[frankbicknell]

Re: UNION QUERY not acting same in Alpha as in SQLServer

My view if looked at through dbForge Studio may have thousands of records. In my grid query if I use (select blah from view where blahblah = useid) I only get the records that meet the where clause thousands are not sent to the grid. Lightning fast. MySql is handling much of the load in the background.

[Davidk]

Re: UNION QUERY not acting same in Alpha as in SQLServer

Sorry... I thought you were suggesting returning all records in the view and doing a filter at the client side. I understand now that you meant passing the filter, via Alpha, in the server side and having the view return filtered data, not all data. That's what I'd originally tried, but not all relevant records were returned.

Here's a pseudo example of my view...

Select fields from from table1
LEFT OUTER JOIN table2 on table1_field = table2_field
WHERE loginId = "myLogin"

UNION ALL

Select fields from from table3
LEFT OUTER JOIN table4 on table3_field = table4_field
LEFT OUTER JOIN table5 on table3_field = table5_field
WHERE loginId = "myLogin" AND otherfield = 0

UNION ALL

Select fields from from table6
LEFT OUTER JOIN table7 on table6_field = table7_field
LEFT OUTER JOIN table8 on table6_field = table8_field AND table8_field = table6_field
WHERE loginId = "myLogin" AND otherfield <> 0

Under Navicat this produces, for example, 5 records

With the WHERE loginId = "myLogin" statements removed from the View, and then a filter used in the List control, the View returns 2 records.

I haven't looked yet to see the statement produced by Alpha but I believe the WHERE clause is being applied globally to the View. This makes sense... how would Alpha know to apply the filter any other way.

I wanted to use parameters with the View but they're too tough to work with... not worth the effort since the List can take a Custom XBasic function as it's source.

[frankbicknell]

Re: UNION QUERY not acting same in Alpha as in SQLServer

David, I can't imagine why your getting that result. I have not tried union views with a list yet. My lists using non union views including updatable seem to work well.

Consider this view

Table_1 Table_2 Table_3
Name ID Name ID Name ID
Joe 1 Tom 1 Sue 2
Bob 2 Dave 3 Diane 3
Bill 3 Steve 1 Fran 3
Jane 4

view_1 code;
Select name, id From table_1
union
select name, id from table_2
union
select name id from table_3

would yield

view_1 data;
name id
Joe 1
Bob 2
Bill 3
Jane 4
Tom 1
Dave 3
Steve 1
Sue 2
Diane 3
Fran 3

In a grid
select name, id from view_1 where id = 3 as the datasource would yield
name id
Bill 3
Dave 3
Diane 3
Fran 3