Solved

Converting Access Query To SQL Script

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

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.…
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. …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 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