Solved

SQL Syntax Union with Where Question

Posted on 2011-03-15
8
443 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
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 68

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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 68

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
INSERT DATE FROM STRING COLUMN 18 46
Help writing a query 6 71
subtract 1 in Access 2003 query 7 37
SQL Error in WHERE Clause 5 38
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

12 Experts available now in Live!

Get 1:1 Help Now