Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
GROUP BY boolean field invalid
Message
 
 
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
00557354
Message ID:
00557369
Views:
9
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--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform