How to use UNION All in the query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to use UNION All in the query

Author

Topic

itchocks
Starting Member

44 Posts

Posted - 2003-09-17 : 07:46:13

Hai,

If I write the query without the UNION All as below then the output is showing correct and working fine. I am writing this query in VB. My DB version is 6.5.

Actually i am taking two tables, both are Sales Tables.

ST030100 is the Sales table name for company SA
ST030200 is the Sales table name for Company ME

Both the tables contains the sames set of fields with the same names.

ST03015 is the SoldDate
ST03018 is the Product Name
ST03020 is the QtySold
ST03021 is the SellingCost
ST03022 is the DiscountAmount
ST03023 is the TotalCost

"select ST03018," & _
"sum((ST03020 * ST03021)-((ST03020 * ST03021)*(ST03022)/100))," & _
"sum(ST03020 * ST03023)," & _
"sum(((ST03020 * ST03021) - ((ST03020 * ST03023) * (ST03022)/100)) - (ST03020 * ST03023)) " & _
"from ST030200 where ST03015 >='" & Text2.Text & "' And ST03015 <='" & Text3.Text & "' Group by ST030100.ST03018,ST030200.ST03018 order by ST03018", cnn, adOpenStatic, adLockReadOnly, adCmdText

If I use the UNION ALL in the query all below.

rst.Open "select ST03018," & _
"sum((ST03020 * ST03021)-((ST03020 * ST03021)*(ST03022)/100))," & _
"sum(ST03020 * ST03023)," & _
"sum(((ST03020 * ST03021) - ((ST03020 * ST03023) * (ST03022)/100)) - (ST03020 * ST03023)) " & _
"from ST030100 where ST03015 >='" & Text2.Text & "' And ST03015 <='" & Text3.Text & "'" & _
" UNION ALL " & _
"select ST03018," & _
"sum((ST03020 * ST03021)-((ST03020 * ST03021)*(ST03022)/100))," & _
"sum(ST03020 * ST03023)," & _
"sum(((ST03020 * ST03021) - ((ST03020 * ST03023) * (ST03022)/100)) - (ST03020 * ST03023)) " & _
"from ST030200 where ST03015 >='" & Text2.Text & "' And ST03015 <='" & Text3.Text & "' Group by ST030100.ST03018,ST030200.ST03018 order by ST03018", cnn, adOpenStatic, adLockReadOnly, adCmdText

then I am getting the Following message.

Run-time error '-2147217900(80040e14)'"
Column 'ST030100.ST03018 is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY caluse.

kindly correct me, i don't know where i am mistaking.

Thank you,
Chock.

Chock

dsdeming

479 Posts

Posted - 2003-09-17 : 08:16:04

When you use an aggregate function ( SUM, COUNT, etc. ) in a SELECT statement, and columns that are not contained in the aggregate function must be contained in a GROUP BY clause.

Dennis

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-17 : 08:28:21

The first section of the query above the UNION ALL needs the GROUP BY clause included.

itchocks
Starting Member

44 Posts

Posted - 2003-09-17 : 09:30:05

Hai Dennis and drymchaser,

Thank you for your reply to this post.

I write the query as you said by adding the Group By in the query which is above UNION ALL as below.

rst.Open "select ST03018," & _
"sum((ST03020 * ST03021)-((ST03020 * ST03021)*(ST03022)/100))," & _
"sum(ST03020 * ST03023)," & _
"sum(((ST03020 * ST03021) - ((ST03020 * ST03023) * (ST03022)/100)) - (ST03020 * ST03023)) " & _
"from ST030100 where ST03015 >='" & Text2.Text & "' And ST03015 <='" & Text3.Text & "'" & _
" UNION ALL " & _
"select ST03018," & _
"sum((ST03020 * ST03021)-((ST03020 * ST03021)*(ST03022)/100))," & _
"sum(ST03020 * ST03023)," & _
"sum(((ST03020 * ST03021) - ((ST03020 * ST03023) * (ST03022)/100)) - (ST03020 * ST03023)) " & _
"from ST030200 where ST03015 >='" & Text2.Text & "' And ST03015 <='" & Text3.Text & "' Group by ST030100.ST03018,ST030200.ST03018 order by ST03018", cnn, adOpenStatic, adLockReadOnly, adCmdText

then it gives the error as below

Run-time error'-2147217900(80040e14)'"
ORDER BY items must appear in the select list if the statement contains a UNION operator.

so i decided the error is coming because i didn't include Order By in the query which is above UNION ALL, so i added the Order By in the query. But now the below error is shown.

Run-time error'-2147217900(80040e14):
Incorrect syntax near the keyword 'UNION'.

I don't know where's the mistake is from.
Kindly view this and reply me.

Thank you very much,
Chock.

Chock

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 09:45:02

You need to debug and look at the string you are generating.

Create your string in the first step, and then just display on the web page as is, and then debug it that way. Don't even connect to the database, never mind try to execute it, until you are sure the SQL is well-formed and is what you are looking for.

It is really hard to figure out from a bunch of concatenations whether or not you are generating valid SQL. Look at the final results and make sure that it looks good. Better yet, cut and paste the final results into Query Analyzer and figure out what needs to be done that way, and then alter your string-concatenation to handle changes needed.

This is especially tough when you have table and column names that are numbers !! I am constantly amazed at some of the "naming conventions" out there. Out of morbid curiousity, what kind of table is this? what data is it storing, and how do you keep track of what all those "ST" numbers mean?

- Jeff

itchocks
Starting Member

44 Posts

Posted - 2003-09-17 : 10:24:10

Hai Jeff,

Actually the Database is an ERP Product. The given a Access database with this software. that describes the meaning for the Field names, as below

ST03018 - Product Code like this.

I am not experineced with Ms Sql server. Actual the query Analyzer everything is in the Database server. So I could not try any thing in the server, just i am devoloping only the front end in VB for the users to show all Product sales between the periods given by user. I didn't understand regarding the one which you say about display in the web page. If you have any example which is doing some calculation in the UNION ALL query. It will be helpful for me.

Thank you very much,
Chock.

Chock

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-17 : 11:32:41

This is how the query should look to work. I'll leave the concatenating to you.

SELECT	ST03018, 
	sum((ST03020 * ST03021)-((ST03020 * ST03021)*(ST03022)/100)), 
	sum(ST03020 * ST03023), 
	sum(((ST03020 * ST03021) - ((ST03020 * ST03023) * (ST03022)/100)) - (ST03020 * ST03023))  
FROM	ST030100
WHERE	ST03015 >=  Text2.Text   And ST03015 <=  Text3.Text
	-- you could use this instead
	-- between Text2.Text and Text3.Text
GROUP BY ST03018

UNION ALL  

SELECT	ST03018, 
	sum((ST03020 * ST03021)-((ST03020 * ST03021)*(ST03022)/100)), 
	sum(ST03020 * ST03023), 
	sum(((ST03020 * ST03021) - ((ST03020 * ST03023) * (ST03022)/100)) - (ST03020 * ST03023))  
FROM	ST030200
WHERE	ST03015 >=  Text2.Text   And ST03015 <=  Text3.Text  
	-- you could use this instead
	-- between Text2.Text and Text3.Text
GROUP BY ST03018
ORDER BY ST03018
	-- if problem you could do ORDER BY 1 (1 meaning the first field returned)

itchocks
Starting Member

44 Posts

Posted - 2003-09-20 : 08:56:09

Hai,

Thank you for your reply, I get the query worked fine, it is below. Eighter Between And >= both works. Here i given Group By in both the Query (i.e) before UNION ALL and after UNION ALL, then only its working. If i remove the Group By Any where or if I add the Order by cluase in the Above Query (i.e) before UNION ALL then its showing Error.

rst.Open "select ST03018,
sum((ST03020 * ST03021)-((ST03020 * ST03021)*(ST03022)/100)),
sum(ST03020 * ST03023),sum(((ST03020 * ST03021) -
(ST03020 * ST03021)*(ST03022)/100)-(ST03020 * ST03023))
from ST030100 where
ST03015 >='" & Text2.Text & "' And ST03015 <='" & Text3.Text & "' Group By ST03018 UNION ALL select ST03018,sum((ST03020 * ST03021)-((ST03020 * ST03021)*(ST03022)/100)),sum(ST03020 * ST03023),sum(((ST03020 * ST03021)-(ST03020 * ST03021)*(ST03022)/100)-(ST03020 * ST03023)) from ST030200 where ST03015 >='" & Text2.Text & "' And ST03015 <='" & Text3.Text & "' Group By ST030200.ST03018 Order By ST03018", cnn, adOpenStatic, adLockReadOnly, adCmdText

Now its working.

Thank you very much,
Chock.

Chock

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources