Syntaxe incorrecte pour l'Union de Tous requête

Je suis en train d'essayer de combiner des requêtes avec l'union de tous:

use SalesDWH
go
select cast(COUNT([specimen id]) as varchar) +'|'+[practice name]+'|'+b.[mlis practice id]+'|'+[practice code]+'|'+[Requesting Physician]+'|'+c.salesrep+'|'+
cast(c.dateestablished as varchar)+'|'+ c.practicecity+'|'+c.practicestate
from quicklabdump a
inner join qlmlismapping b
on (b.[quiklab practice code] = a.[practice code])
inner join PracticeandPhysician c
on (a.[Requesting Physician]=c.doctorfirstname+' '+c.DOCTORLASTNAME
and a.[practice code]=c.practicecode)
where   DATEPART(yy, [DATE entered]) = 2011
and DATEPART(mm, [DATE entered]) = 12
group by a.[practice name],b.[mlis practice id],a.[practice code],
a.[Requesting Physician],c.salesrep,c.dateestablished, c.practicecity,c.practicestate
order by COUNT([specimen id]) desc
union all
select cast(COUNT([specimen id]) as varchar) +'|'+[practice name]+'|'+b.[mlis practice id]+'|'+[practice code]+'|'+[Requesting Physician]+'|'+c.salesrep+'|'+
cast(c.dateestablished as varchar)+'|'+ c.practicecity+'|'+c.practicestate
from quicklabdump a
inner join qlmlismapping b
on (b.[quiklab practice code] = a.[practice code])
inner join PracticeandPhysician c
on (a.[Requesting Physician]=c.doctorfirstname+' '+c.DOCTORLASTNAME
and a.[practice code]=c.practicecode)
where   DATEPART(yy, [DATE entered]) = 2011
and DATEPART(mm, [DATE entered]) = 11
group by a.[practice name],b.[mlis practice id],a.[practice code],
a.[Requesting Physician],c.salesrep,c.dateestablished, c.practicecity,c.practicestate
order by COUNT([specimen id]) desc

Mais j'obtiens cette erreur:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'union'.

Ce que je fais mal?

OriginalL'auteur l--''''''---------'''''''''''' | 2011-12-29

  1. 6

    supprimer le premier 

    order by COUNT([specimen id]) desc

    ou ce faire:

    select cDATEPART(mm, [DATE entered]) as Month, cast(COUNT([specimen id]) as varchar) +'|'+[practice name]+'|'+b.[mlis practice id]+'|'+[practice code]+'|'+[Requesting Physician]+'|'+c.salesrep+'|'+
cast(c.dateestablished as varchar)+'|'+ c.practicecity+'|'+c.practicestate
from quicklabdump a
inner join qlmlismapping b
on (b.[quiklab practice code] = a.[practice code])
inner join PracticeandPhysician c
on (a.[Requesting Physician]=c.doctorfirstname+' '+c.DOCTORLASTNAME
and a.[practice code]=c.practicecode)
where   DATEPART(yy, [DATE entered]) = 2011
and DATEPART(mm, [DATE entered]) in (11,12)
group by DATEPART(mm, [DATE entered]), a.[practice name],b.[mlis practice id],a.[practice code],
a.[Requesting Physician],c.salesrep,c.dateestablished, c.practicecity,c.practicestate
order by COUNT([specimen id]) desc

    Vous pouvez également dire quelque chose comme

    select CASE WHEN cDATEPART(mm, [DATE entered]) = 11 THEN 'The 11th Month' 
WHEN cDATEPART(mm, [DATE entered]) = 12 THEN 'The 12th Month' END as [when], ...
    merci beaucoup. si je devais le faire de cette façon (11,12)... j'aurais besoin de faire la différence entre les données de 11 et les données de 12, comment dois-je procéder? pouvez-vous me montrer comment je peux faire un exposé et d'ajouter une colonne de sortie, que dirais-11 ou 12?
    Il est dans le groupe. Je vais ajouter la colonne.
    c'est foutu génie merci beaucoup
    et juste par curiosité, pouvez-vous svp m'pourquoi il ne fonctionne pas avec l'UNION de TOUS lorsque j'ai suivi votre et eric conseils sur la mise cet ORDRE PAR seulement à la dernière ligne?
    Quelle erreur avez-il vous donner? Je me serais attendu à ce qu'elle fonctionne.

    OriginalL'auteur Hogan

  2. 10

    Votre order by ne peut aller que sur le dernier relevé:

    use SalesDWH
go
select cast(COUNT([specimen id]) as varchar) +'|'+[practice name]+'|'+b.[mlis practice id]+'|'+[practice code]+'|'+[Requesting Physician]+'|'+c.salesrep+'|'+
cast(c.dateestablished as varchar)+'|'+ c.practicecity+'|'+c.practicestate
from quicklabdump a
inner join qlmlismapping b
on (b.[quiklab practice code] = a.[practice code])
inner join PracticeandPhysician c
on (a.[Requesting Physician]=c.doctorfirstname+' '+c.DOCTORLASTNAME
and a.[practice code]=c.practicecode)
where   DATEPART(yy, [DATE entered]) = 2011
and DATEPART(mm, [DATE entered]) = 12
group by a.[practice name],b.[mlis practice id],a.[practice code],
a.[Requesting Physician],c.salesrep,c.dateestablished, c.practicecity,c.practicestate
union all
select cast(COUNT([specimen id]) as varchar) +'|'+[practice name]+'|'+b.[mlis practice id]+'|'+[practice code]+'|'+[Requesting Physician]+'|'+c.salesrep+'|'+
cast(c.dateestablished as varchar)+'|'+ c.practicecity+'|'+c.practicestate
from quicklabdump a
inner join qlmlismapping b
on (b.[quiklab practice code] = a.[practice code])
inner join PracticeandPhysician c
on (a.[Requesting Physician]=c.doctorfirstname+' '+c.DOCTORLASTNAME
and a.[practice code]=c.practicecode)
where   DATEPART(yy, [DATE entered]) = 2011
and DATEPART(mm, [DATE entered]) = 11
group by a.[practice name],b.[mlis practice id],a.[practice code],
a.[Requesting Physician],c.salesrep,c.dateestablished, c.practicecity,c.practicestate
order by COUNT([specimen id]) desc

    C'est parce que la commande arrive après que le résultat est atteint, ce qui est le poste de l'union. Vous ne pouvez pas commander un jeu avant qu'il revient finalement.

    merci beaucoup. juste essayé et obtenu Msg 156, Niveau 15, État 1, Ligne 1 syntaxe Incorrecte près du mot clé "ordre".

    OriginalL'auteur Eric