Row Size is too large?
-
Sergiy, I should of said I did try that too. I've also tried using CAST instead of CONVERT. I have examined my query over and over and do not see a reason for receiving this error.
Here is my query:
select RGnameLast+', '+RGnameFirst AS Adjuster, RGnameLast, COcity AS ClientOffice, IOofficeName, WRworkOfficeId, WRid, SRnameLast+', '+SRnameFirst AS Subject, CONVERT(CHAR(8), MIN(WCcompletedDate), 112) AS FilterDate, WRbillToFileID AS ClaimNumber, SUM(WCvideoSeconds) as VideoAmount, CONVERT(VARCHAR(12), -1) AS CaseCost, CAST('' AS TEXT) AS Results, WRinjury, 0 AS Saved, CONVERT(TEXT, WRnotes) AS WRnotes INTO inet.dbo.jones_temp FROM workrequest, requestgenerator, clientoffice, subjectrecord, worktask, workcompleted, internaloffice, billmaster WHERE 1 = 1 AND BMworkrequestid = WRid AND WRrequestGeneratorID = RGid AND RGclientOfficeID = COid AND WRsubjectRecordID = SRid AND WTlinkItemID = WRid AND WClinkItemID = WTid AND WRworkOfficeId = IOid AND WCcompletedDate BETWEEN '8/1/2006' AND '8/30/2006' GROUP BY RGnameLast, RGnameFirst, COcity, IOofficeName, WRworkOfficeId, WRid, SRnameLast, SRnameFirst, WRbillToFileID, WRinjury, WRnotes
The error I get is the same:
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I can find no instance of where I am comparing or sorting the WRnotes column.
-
In your last query posting, you are grouping by WRNotes and you cannot.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
But if I take WRnotes out of the GROUP BY I get the infamous error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'workrequest.WRnotes' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
-
Why you need GROUP BY at all?
_____________
Code for TallyGenerator -
Sergiy, if I remove the entire GROUP BY, I still get this error:
Column 'requestgenerator.RGnameLast' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
So I need that clause in there to satisfy some requirement.
-
The 'some' requirements in your case, is that you have aggregates in your select. (SUM, MAX, MIN etc)
If you have aggregates, then all non-aggregated columns must be GROUPED BY
A workaround to include columns that don't need grouping, but can't be, or won't be in the GROUP BY clause, may be - depending on the data, to use MAX or MIN on that column, if either makes no difference to the result.
If this is a way to go in this case, I can't really tell.
Another workaround may be to split up the query in parts, do the aggregates first, enclose them in a virtual table, and then join out from that to include the nonaggregated columns...
It's late and I have to run, so I can't give any examples at this time, sorry.
/Kenneth
-
It means that one of the Rows is bigger than a SQL Server page, the text below is from the link below and it includes more restrictions. The reasons and the restrictions are valid based on the relational algebra. Hope this helps.
(There is no limit to the number of items in the ORDER BY clause. However, there is a limit of 8,060 bytes for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause.)
http://msdn2.microsoft.com/en-us/library/ms188385.aspx
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
You have MAX() and MIN() for some columns, and you are definetely not allowed (and probably don't intend) to group you text notes.
I could help you with this query if you build this query right way first.
a) assign aliases to each table you use;
b) use proper "INNER JOIN ... ON ..." syntax;
c) qualify every column name with table alias.
_____________
Code for TallyGenerator
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply