We help IT Professionals succeed at work.

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

trojan_uk
trojan_uk asked
on
1,079 Views
Last Modified: 2011-04-14
Hi,

I'm getting the subject error on the SP code below, but I can't figure out why, any ideas?

EXEC('
SELECT DISTINCT TOP ' + @TOPTOTAL +
'C.MOBDEVID,
TELNUMBER,
CALLDATE,
CALLTIME,
DURATION,
DIALLEDNUMBER,
CONVERT(DECIMAL(10,2),CHARGE) AS CHARGE,
DESTTYPE,
DAYOFWEEK,
PERSONAL_NUMBER,
A.COSTID,
LOCATION,
DISPLAY_NAME,
CLAIMID
FROM ((((wbAssetTrack A LEFT JOIN wbCompany B ON A.CompID = B.CompID) LEFT JOIN wbMCD C ON A.AssID = C.AssID) LEFT JOIN wbVoiceOut D ON D.MobDevID = C.MobDevID) LEFT JOIN wbReclaim E ON E.Personal_Number = D.DIALLEDNUMBER)
WHERE B.COMPID= ' +  @COMPID + ' AND A.COSTID = ' +  @COSTID + ' AND TelNumber <> ''00000000000'' AND C.VISIBLE <> 0 AND MONTH(CALLDATE) = ' +  @MONTH + ' AND YEAR(CALLDATE) = ' + @YEAR +
' ORDER BY
           CASE WHEN ' + @ORDERBY  + ' = 1 THEN CALLDATE  
                      WHEN ' + @ORDERBY + ' = 2 THEN CALLTIME  
                      WHEN ' + @ORDERBY + ' = 3 THEN CHARGE
           END
DESC')
Comment
Watch Question

Jai STech Arch

Commented:
have you declared the variables properly
@TOPTOTAL...

can you give us the full sp..
Sham HaqueSenior SAP CRM Consultant
CERTIFIED EXPERT

Commented:
do a PRINT of your dynamic SQL to check....

PRINT ('
SELECT DISTINCT TOP ' + @TOPTOTAL +
'C.MOBDEVID,
TELNUMBER,
CALLDATE,
[...]
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
What exactly is the error ?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Canadean that did the trick, if you have a few moments I would like to know why this worked?

Many thanks

Commented:
No problem,

When using SELECT DISTINCT your results can only be ordered on columns (or expressions) that are in the SELECT part of the statement.

Now, even though what you are ultimately ordering by IS there (e.g. CALLDATE) what you are actually ordering by is, in effect, the result of an expression (your case statement).

So all we do is include the same case statement in the SELECT portion so it can order your results by that. So when you see your results, it will actually be ordering by the last column (the one just added), not, for example, the CALLDATE column ealier in the SELECT although the results will look the same.

Hope this makes sense.....

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.