Solved

SQL Syntax Union with Where Question

Posted on 2011-03-15
8
448 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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