Check language? Query for Union. 1

[Kurt6905]

I tried to save this query (qGOLOM, for those who have tuned in before) but it didn't like my From statement. What is incorrect or missing? Also, does the rest of it look acceptable? Thanks for any help!


Select License, County, EPA_Reg, Brandname, Chem, Active, Pestcode

From
(
Select County, EPA_Reg, Brandname, Chem1[As Chem], Active1[As Active], Pestcode
From myTable
Where Active1 > 0

Union All

Select County, EPA_Reg, Brandname, Chem2[As Chem], Active2[As Active], Pestcode
From myTable
Where Active1 > 0

Union All

Select County, EPA_Reg, Brandname, Chem3[As Chem], Active3[As Active], Pestcode
From myTable
Where Active1 > 0

Union All

Select County, EPA_Reg, Brandname, Chem4[As Chem], Active4[As Active], Pestcode
From myTable
Where Active1 > 0
)

 

[PHV]

Why not simply this ?
SELECT License, County, EPA_Reg, Brandname, Chem1 As Chem, Active1 As Active, Pestcode
FROM myTable WHERE Active1 > 0
UNION ALL SELECT License, County, EPA_Reg, Brandname, Chem2, Active2, Pestcode
FROM myTable WHERE Active2 > 0
UNION ALL SELECT License, County, EPA_Reg, Brandname, Chem3, Active3, Pestcode
FROM myTable WHERE Active3 > 0
UNION ALL SELECT License, County, EPA_Reg, Brandname, Chem4, Active4, Pestcode
FROM myTable WHERE Active4 > 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrbarnett]

Kurt

There is no field "License" generated from the inner query for your outer query to select on.
This is most likely to be the cause of an error.

John

 

[Golom]

Your bracketing of "As" clauses is incorrect and you need to be testing the "Active[red]n[/red]" field that each SELECT refers to rather than testing "Active[red]1[/red]" in all of them.

As jrbarnett says, you need to include the "License" field in the various SELECTs that are being UNIONed.

Have a look at PHV's code.

Here is your code with the appropriate changes.

Select License, County, EPA_Reg, Brandname, Chem, Active, Pestcode

From
(
Select [red]License, [/red]County, EPA_Reg, Brandname, Chem1 As Chem, Active1 As Active, Pestcode
From myTable
Where Active1 > 0

Union All

Select [red]License, [/red]County, EPA_Reg, Brandname, Chem2, Active2, Pestcode
From myTable
Where Active[red]2[/red] > 0

Union All

Select [red]License, [/red]County, EPA_Reg, Brandname, Chem3, Active3, Pestcode
From myTable
Where Active[red]3[/red] > 0

Union All

Select [red]License, [/red]County, EPA_Reg, Brandname, Chem4, Active4, Pestcode
From myTable
Where Active[red]4[/red] > 0
)

 

[PHV]

I quite don't understand why a SELECT ... FROM (SELECT ...) !