Solved

SQL Syntax Union with Where Question

Posted on 2011-03-15
8
449 Views
Last Modified: 2012-06-21
Why do I get an invalid syntax near Where on this SQL statement? I need to restrict these records to only those that have not already been imported.
select Number,DocumentType,coalesce(Reference,'') as Reference,coalesce(BatchName,'') as BatchName,PurchaseDate,PostDate,coalesce(CRI,'') as PONUMBER,VENDORID, 1 as DBCR,Purchases, Debit, Credit,itemprice,ItemGLCombination,wasimported
from PEF_VendorBatchLoadTransaction
where wasimported = 0
union all
select Number,DocumentType, coalesce(Reference,'') as Reference,coalesce(BatchName,'') as BatchName,PurchaseDate,PostDate,coalesce(CRI,'') as PONUMBER,VENDORID,0 as DBCR,Purchases, sum(Debit) DebitAmt, sum(Credit) CreditAmt,sum(itemprice),ItemGLCombination,wasimported
from PEF_VendorBatchLoadTransaction
group by Number,DocumentType,Reference,BatchName,PurchaseDate,PostDate,CRI,VENDORID, Purchases,itemprice,ItemGLCombination,wasimported
where wasimported = 0
order by Number, Purchases

Open in new window

0
Comment
Question by:rwheeler23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 4

Assisted Solution

by:Alex Matzinger
Alex Matzinger earned 100 total points
ID: 35143179
the where clause needs to go before the group by clause.
0
 
LVL 2

Assisted Solution

by:cnemcse1
cnemcse1 earned 100 total points
ID: 35143186
Your problem is in the group by statement, try running it without it. If you need them groupped by, create a view using the union all statement and then query the view using your group by.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 35143268
armatzinger is correct. The syntax error is resulting from the misplaced WHERE.

However, there is a logical error in the second select: you group by itemprice, though you build the sum of it - one of both is useless, you will get only single values if you group by that column, so max(itemprice) = min(itemprice) = avg(itemprice) = sum(itemprice).
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:rwheeler23
ID: 35143450
My mistake on this but your catch brings me to a larger problem. In the second select I need an aggregate total of the itemprice field. If you notice the 0 and 1 in the select statements that is my indicator for the source of the record. 1 is from the records and 0 is the total. Excuse me for being dense but I am not quote sure of the syntax your are suggesting.
0
 

Author Comment

by:rwheeler23
ID: 35143462
By the way, the whole point of this query is to import data. So I need all the data elements but I only need to group on the number field.
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 300 total points
ID: 35143463

select Number,DocumentType,coalesce(Reference,'') as Reference,coalesce(BatchName,'') as BatchName,PurchaseDate,PostDate,coalesce(CRI,'') as PONUMBER,VENDORID, 1 as DBCR,Purchases, Debit, Credit,itemprice,ItemGLCombination,wasimported
from PEF_VendorBatchLoadTransaction
where wasimported = 0
union all
select Number,DocumentType, coalesce(Reference,'') as Reference,coalesce(BatchName,'') as BatchName,PurchaseDate,PostDate,coalesce(CRI,'') as PONUMBER,VENDORID,0 as DBCR,Purchases, sum(Debit) DebitAmt, sum(Credit) CreditAmt,sum(itemprice),ItemGLCombination,wasimported
from PEF_VendorBatchLoadTransaction
where wasimported = 0
group by Number,DocumentType,Reference,BatchName,PurchaseDate,PostDate,CRI,VENDORID, Purchases,ItemGLCombination,wasimported
order by Number, Purchases

Open in new window

0
 

Author Comment

by:rwheeler23
ID: 35143596
In my dataset I have 4 records for ItemGLCombination. Lets say they are A,B,C and D.
The itemprice for each is $100. When I run this query I get the 4 records for A,B,C and D but I also get four records for each from the group by query without total sum on any of them. So now I have 8 records with an itemprice of $100 instead of 4 with $100 and one with $400.
0
 

Author Comment

by:rwheeler23
ID: 35143623
Nevermind, I just answered my own question. ItemGLCombinaiton was part of the group. Thanks for your help. You pointed me in the correct direction.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question