Solved

SQL Syntax Union with Where Question

Posted on 2011-03-15
8
442 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
8 Comments
 
LVL 4

Assisted Solution

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

Assisted Solution

by:cnemcse1
cnemcse1 earned 100 total points
Comment Utility
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 68

Expert Comment

by:Qlemo
Comment Utility
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
 

Author Comment

by:rwheeler23
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rwheeler23
Comment Utility
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 68

Accepted Solution

by:
Qlemo earned 300 total points
Comment Utility

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot 2 32
query question 4 26
encyps queries mssql 15 24
How to place a condition in a filter criteria in t-sql? 12 31
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now