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?
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?
Comment