Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
GROUP BY boolean field invalid
Message
 
 
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Divers
Thread ID:
00557354
Message ID:
00557369
Vues:
7
This message has been marked as the solution to the initial question of the thread.
You can use T-SQL CASE function to convert bit field into number.
Also in the select field list you can only use columns listed in group by clause or aggregate functions.
SELECT A.InvoiceID, A.VenID, A.InvDate, 
     <b>CASE WHEN A.Closed=1 THEN 1 ELSE 0 END AS Closed</b>, 
     B.Vendorname, SUM(C.Subtotal) AS InvoiceTotal
  FROM Invoice A JOIN Vendor B ON A.VenID=B.VenID
  JOIN LineItem C ON A.InvoiceID=C.InvoiceID
  WHERE A.InvoiceID=1000
  GROUP BY A.InvoiceID, A.VenID, <b>B.Vendorname</b>, A.InvDate, 
    <b>CASE WHEN A.Closed=1 THEN 1 ELSE 0 END</b>
>SELECT A.*, B.Vendorname, SUM(C.Subtotal) AS InvoiceTotal
>FROM Invoice A JOIN Vendor B ON A.VenID=B.VenID
>JOIN LineItem C ON A.InvoiceID=C.InvoiceID
>WHERE A.InvoiceID=1000
>GROUP BY A.InvoiceID, A.VenID, A.InvDate, A.Closed
>
>The "Closed" field above in the Invoice table is a bit field and in SQL7 I get "Cannot group by a bit column." error. How do I pull this off so it works? TIA.
--sb--
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform