Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1046
  • Last Modified:

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

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')
0
trojan_uk
Asked:
trojan_uk
1 Solution
 
Jai STech ArchCommented:
have you declared the variables properly
@TOPTOTAL...

can you give us the full sp..
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
do a PRINT of your dynamic SQL to check....

PRINT ('
SELECT DISTINCT TOP ' + @TOPTOTAL +
'C.MOBDEVID,
TELNUMBER,
CALLDATE,
[...]
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
What exactly is the error ?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
CanadeanCommented:
Try this

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,

           CASE WHEN ' + @ORDERBY  + ' = 1 THEN CALLDATE  
                      WHEN ' + @ORDERBY + ' = 2 THEN CALLTIME  
                      WHEN ' + @ORDERBY + ' = 3 THEN CHARGE
           END

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')
0
 
trojan_ukAuthor Commented:
Thanks Canadean that did the trick, if you have a few moments I would like to know why this worked?

Many thanks
0
 
CanadeanCommented:
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.....
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now