SQL Syntax Union with Where Question

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

rwheeler23Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
QlemoConnect With a Mentor DeveloperCommented:

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
 
Alex MatzingerConnect With a Mentor Database AdministratorCommented:
the where clause needs to go before the group by clause.
0
 
cnemcse1Connect With a Mentor Commented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
QlemoDeveloperCommented:
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
 
rwheeler23Author Commented:
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
 
rwheeler23Author Commented:
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
 
rwheeler23Author Commented:
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
 
rwheeler23Author Commented:
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
All Courses

From novice to tech pro — start learning today.