Solved

Converting Access Query To SQL Script

Posted on 2008-10-10
2
190 Views
Last Modified: 2010-03-20
Hello programmers.  I need a little help rewriting a query to SQL script.  The following Script is giving me the following errors.  Can someone help me identify a fix.  Thanks.

SCRIPT:
SELECT DISTINCT
'999998' AS exp1, oas_testit2.el1,
Sum(oas_testit2.Actual) AS SumOfActual,
Sum(oas_testit2.Budget) AS SumOfBudget,
Sum(oas_testit2.Prior_Act) AS SumOfPrior_Act,
Sum(oas_testit2.Actual_YTD) AS SumOfActual_YTD,
Sum(oas_testit2.Budget_YTD) AS SumOfBudget_YTD,
Sum(oas_testit2.Prior_YTD) AS SumOfPrior_YTD, oas_testit2.yr, oas_testit2.period, oas_testit2.base_grp
FROM oas_testit2
WHERE (((oas_testit2.el2)<>'999999' And (oas_testit2.el2)<>'299999' And (oas_testit2.el2)<>'399999' And (oas_testit2.el2)<>'003035'
And (oas_testit2.el2)<>'999994' And (oas_testit2.el2)<>'999995' And (oas_testit2.el2)<>'999996' And (oas_testit2.el2)<>'999997'
And (oas_testit2.el2)<>'990100' And (oas_testit2.el2)<>'990200' And (oas_testit2.el2)<>'990300' And (oas_testit2.el2)<>'990400'
And (oas_testit2.el2)<>'990500' And (oas_testit2.el2)<>'990600' And (oas_testit2.el2)<>'990700' And (oas_testit2.el2)<>'990800'))
GROUP BY '999998', oas_testit2.el1, oas_testit2.yr, oas_testit2.period, oas_testit2.base_grp
HAVING (((oas_testit2.el1)<>'44949' And (oas_testit2.el1)<>'44959' And (oas_testit2.el1)<>'44969' And (oas_testit2.el1)<>'44979'
And (oas_testit2.el1)<>'44989' And (oas_testit2.el1)<>'44999' And (oas_testit2.el1)<>'49999' And (oas_testit2.el1)<>'44919'))

ERRORS:
Server: Msg 164, Level 15, State 1, Line 1
GROUP BY expressions must refer to column names that appear in the select list.
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near 'oas_testit2'.
0
Comment
Question by:Mahonek
2 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22689557
You are trying to group by colums you aren't selecting:

What does this yield?
SELECT DISTINCT
'999998' AS exp1, oas_testit2.el1, 
Sum(oas_testit2.Actual) AS SumOfActual, 
Sum(oas_testit2.Budget) AS SumOfBudget, 
Sum(oas_testit2.Prior_Act) AS SumOfPrior_Act, 
Sum(oas_testit2.Actual_YTD) AS SumOfActual_YTD, 
Sum(oas_testit2.Budget_YTD) AS SumOfBudget_YTD, 
Sum(oas_testit2.Prior_YTD) AS SumOfPrior_YTD, oas_testit2.yr, oas_testit2.period, oas_testit2.base_grp
FROM oas_testit2
WHERE (((oas_testit2.el2)<>'999999' And (oas_testit2.el2)<>'299999' And (oas_testit2.el2)<>'399999' And (oas_testit2.el2)<>'003035' 
And (oas_testit2.el2)<>'999994' And (oas_testit2.el2)<>'999995' And (oas_testit2.el2)<>'999996' And (oas_testit2.el2)<>'999997' 
And (oas_testit2.el2)<>'990100' And (oas_testit2.el2)<>'990200' And (oas_testit2.el2)<>'990300' And (oas_testit2.el2)<>'990400' 
And (oas_testit2.el2)<>'990500' And (oas_testit2.el2)<>'990600' And (oas_testit2.el2)<>'990700' And (oas_testit2.el2)<>'990800'))
GROUP BY '999998', oas_testit2.el1
 
HAVING (((oas_testit2.el1)<>'44949' And (oas_testit2.el1)<>'44959' And (oas_testit2.el1)<>'44969' And (oas_testit2.el1)<>'44979' 
And (oas_testit2.el1)<>'44989' And (oas_testit2.el1)<>'44999' And (oas_testit2.el1)<>'49999' And (oas_testit2.el1)<>'44919'))

Open in new window

0
 

Accepted Solution

by:
Mahonek earned 0 total points
ID: 22689702
Thanks Brandon I had just figured it out.  your script did not work, but this script that I changed to did.

CORRECTED SCRIPT:
SELECT DISTINCT
'999998' AS exp1, oas_testit2.el1,
Sum(oas_testit2.Actual) AS SumOfActual,
Sum(oas_testit2.Budget) AS SumOfBudget,
Sum(oas_testit2.Prior_Act) AS SumOfPrior_Act,
Sum(oas_testit2.Actual_YTD) AS SumOfActual_YTD,
Sum(oas_testit2.Budget_YTD) AS SumOfBudget_YTD,
Sum(oas_testit2.Prior_YTD) AS SumOfPrior_YTD, oas_testit2.yr, oas_testit2.period, oas_testit2.base_grp
FROM oas_testit2
WHERE (((oas_testit2.el2)<>'999999' And (oas_testit2.el2)<>'299999' And (oas_testit2.el2)<>'399999' And (oas_testit2.el2)<>'003035'
And (oas_testit2.el2)<>'999994' And (oas_testit2.el2)<>'999995' And (oas_testit2.el2)<>'999996' And (oas_testit2.el2)<>'999997'
And (oas_testit2.el2)<>'990100' And (oas_testit2.el2)<>'990200' And (oas_testit2.el2)<>'990300' And (oas_testit2.el2)<>'990400'
And (oas_testit2.el2)<>'990500' And (oas_testit2.el2)<>'990600' And (oas_testit2.el2)<>'990700' And (oas_testit2.el2)<>'990800'))
GROUP BY oas_testit2.el1, oas_testit2.yr, oas_testit2.period, oas_testit2.base_grp

HAVING (((oas_testit2.el1)<>'44949' And (oas_testit2.el1)<>'44959' And (oas_testit2.el1)<>'44969' And (oas_testit2.el1)<>'44979'
And (oas_testit2.el1)<>'44989' And (oas_testit2.el1)<>'44999' And (oas_testit2.el1)<>'49999' And (oas_testit2.el1)<>'44919'))
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

803 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