• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

SQL: SUM in query with text fields

Gurus,

I have a query that populates a report of any open jobs. A job is closed when billing submits the invoice and checks "closed." This works fine except sometimes there is a delay and jobs that have been shipped show up on the open jobs report.

What I'd like to do is compare the Qty (number needed) to the sum of the ShipQty (there may be several shipments to arrive at the total needed.)

Example 1 works OK and gives me what I want with limited fields. However, if I try this same thing with the larger query, Example 2, I get errors on text fields. The error is "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Is there any way to include text fields in a query where I'm using a SUM Group By?
Example 1
SELECT     dbo.Jobs.JOBNO, dbo.Jobs.AllShipped, dbo.Jobs.QTY, dbo.Jobs.Closed, SUM(dbo.Shipments.ShipQty) AS Shipped
FROM         dbo.Jobs RIGHT OUTER JOIN
                      dbo.Shipments ON dbo.Jobs.JOBNO = dbo.Shipments.Job_ID
GROUP BY dbo.Jobs.JOBNO, dbo.Jobs.AllShipped, dbo.Jobs.Closed, dbo.Jobs.QTY
HAVING      (dbo.Jobs.Closed = 0) AND (SUM(dbo.Shipments.ShipQty) < dbo.Jobs.QTY)


Example 2
SELECT     TOP (100) PERCENT dbo.Jobs.JOBNO, dbo.Jobs.[DATE ORDERED], dbo.Contacts.Companies_ID, dbo.Companies.CUSTOMER, dbo.Jobs.Contacts_ID, 
                      dbo.Contacts.CONTACT, dbo.Jobs.[P O NO], dbo.Jobs.ITEM, dbo.Items.ItemDesc, dbo.Jobs.QTY, dbo.Jobs.Items_ID, dbo.Items.[DWG NO], dbo.Items.[PART NO], 
                      dbo.Jobs.[Ship Date Comment], dbo.Jobs.Closed, dbo.Jobs.[Due Comment], dbo.Jobs.[Date ordered comment], dbo.Jobs.ID, dbo.Shipments.DueDate, 
                      dbo.Shipments.DueQty, dbo.Shipments.ShipDate, dbo.Shipments.ShipQty, dbo.Items.size, dbo.Jobs.InvoiceDate, dbo.Jobs.material, dbo.Jobs.JbDescription, 
                      dbo.Jobs.Shop
FROM         dbo.Jobs LEFT OUTER JOIN
                      dbo.Contacts LEFT OUTER JOIN
                      dbo.Companies ON dbo.Contacts.Companies_ID = dbo.Companies.ID ON dbo.Jobs.Contacts_ID = dbo.Contacts.ID LEFT OUTER JOIN
                      dbo.Items ON dbo.Jobs.Items_ID = dbo.Items.ID LEFT OUTER JOIN
                      dbo.Shipments ON dbo.Jobs.JOBNO = dbo.Shipments.Job_ID
WHERE     (dbo.Jobs.Closed = 0)
ORDER BY dbo.Jobs.JOBNO

Open in new window

0
Michaelj42
Asked:
Michaelj42
  • 2
  • 2
3 Solutions
 
SharathData EngineerCommented:
Convert the text columns to varchar in the SELECT and GROUP BY clauses.

CONVERT(varchar(200),dbo.Jobs.JOBNO) as JOBNO
0
 
Anthony PerkinsCommented:
And since you appear to be using at least SQL Server 2005, consider replacing the deprecated data types: text or ntext columns for varchar or nvarchar.
0
 
Michaelj42Author Commented:
OK, Here is what I got...  Look right? I did get results with this. Will have to do some checking to see if they are good results.
SELECT     TOP (100) PERCENT dbo.Jobs.JOBNO, dbo.Jobs.[DATE ORDERED], dbo.Contacts.Companies_ID, CONVERT(varchar(200), dbo.Companies.CUSTOMER) AS CUSTOMER, 
                      dbo.Jobs.Contacts_ID, CONVERT(varchar(200), dbo.Contacts.CONTACT) AS CONTACT, CONVERT(varchar(200), dbo.Jobs.[P O NO]) AS [P O NO], CONVERT(varchar(200), 
                      dbo.Jobs.ITEM) AS ITEM, CONVERT(varchar(200), dbo.Items.ItemDesc) AS ItemDesc, dbo.Jobs.QTY, SUM(dbo.Shipments.ShipQty) AS ShipTotal, 
                      CONVERT(varchar(200), dbo.Items.[DWG NO]) AS [DWG NO], CONVERT(varchar(200), dbo.Items.[PART NO]) AS [PART NO], CONVERT(varchar(200), 
                      dbo.Jobs.[Ship Date Comment]) AS [Ship Date Comment], dbo.Jobs.Closed, CONVERT(varchar(200), dbo.Jobs.[Due Comment]) AS [Due Comment], 
                      CONVERT(varchar(200), dbo.Jobs.[Date ordered comment]) AS [Date ordered comment], dbo.Shipments.DueDate, dbo.Shipments.DueQty, dbo.Shipments.ShipDate, 
                      CONVERT(varchar(200), dbo.Items.size) AS size, dbo.Jobs.InvoiceDate, CONVERT(varchar(200), dbo.Jobs.material) AS material, CONVERT(varchar(200), 
                      dbo.Jobs.JbDescription) AS jbDescription, CONVERT(varchar(200), dbo.Jobs.Shop) AS Shop, dbo.Jobs.AllShipped, dbo.Shipments.ShipQty
FROM         dbo.Jobs LEFT OUTER JOIN
                      dbo.Contacts LEFT OUTER JOIN
                      dbo.Companies ON dbo.Contacts.Companies_ID = dbo.Companies.ID ON dbo.Jobs.Contacts_ID = dbo.Contacts.ID LEFT OUTER JOIN
                      dbo.Items ON dbo.Jobs.Items_ID = dbo.Items.ID LEFT OUTER JOIN
                      dbo.Shipments ON dbo.Jobs.JOBNO = dbo.Shipments.Job_ID
WHERE     (dbo.Jobs.Closed = 0)
GROUP BY dbo.Jobs.JOBNO, dbo.Jobs.[DATE ORDERED], dbo.Contacts.Companies_ID, dbo.Jobs.Contacts_ID, dbo.Contacts.CONTACT, dbo.Jobs.QTY, dbo.Jobs.Items_ID, 
                      dbo.Jobs.ID, dbo.Shipments.DueDate, dbo.Shipments.DueQty, dbo.Shipments.ShipDate, dbo.Jobs.InvoiceDate, CONVERT(varchar(200), dbo.Items.ItemDesc), 
                      CONVERT(varchar(200), dbo.Jobs.[Ship Date Comment]), dbo.Jobs.Closed, CONVERT(varchar(200), dbo.Jobs.[Due Comment]), CONVERT(varchar(200), 
                      dbo.Jobs.[Date ordered comment]), CONVERT(varchar(200), dbo.Jobs.material), dbo.Jobs.AllShipped, dbo.Shipments.ShipQty, CONVERT(varchar(200), 
                      dbo.Jobs.[P O NO]), CONVERT(varchar(200), dbo.Jobs.ITEM), CONVERT(varchar(200), dbo.Items.size), CONVERT(varchar(200), dbo.Jobs.JbDescription), 
                      CONVERT(varchar(200), dbo.Jobs.Shop), CONVERT(varchar(200), dbo.Companies.CUSTOMER), CONVERT(varchar(200), dbo.Contacts.CONTACT), 
                      CONVERT(varchar(200), dbo.Items.[DWG NO]), CONVERT(varchar(200), dbo.Items.[PART NO])
HAVING      (dbo.Jobs.AllShipped = 0) AND (SUM(dbo.Shipments.ShipQty) < dbo.Jobs.QTY)
ORDER BY dbo.Jobs.JOBNO

Open in new window

0
 
Anthony PerkinsCommented:
>>OK, Here is what I got...  Look right?<<
Except that doing SELECT TOP(100) PERCENT ... ORDER BY is totally pointless.  Your results will not be sorted and besides it is a very bad idea to use an ORDER BY clause in a VIEW.
0
 
Michaelj42Author Commented:
OK I took those off and began converting my ntext and text to nvarchar.
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.

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