Solved

Converting Access Query To SQL Script

Posted on 2008-10-10
2
192 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL Query to include null values 3 46
Whats wrong in this query - Select * from tableA,tableA 11 45
T-SQL: New to using transactions 9 46
Loops and updating in SQL Query 9 52
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

789 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