Error on order by with union all

Hello,

I want to order by a field that is a code, like 1,2,3,11,12,13, and i get the following error

"Only text pointers are allowed in work tables, never text, ntext, or image columns sql server"

Why do i get this, what can i do to overcome this?

Thank you

Need more info. Like table structures, especially with data types. Is this code column a text column:

CREATE TABLE test
(
testId int PRIMARY key,
textVal text
)
INSERT INTO test
VALUES (1,'1,2,3,11,12,13')
INSERT INTO test
VALUES (2,'14,15')
go

--2000
Msg 420, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used in an ORDER BY clause.

--2005
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The textptr would be something like this:

SELECT *
FROM test
ORDER BY textptr(textVal)

But this would not be of any real value. Do you really need > 8000 characters for a code? Consider changing to a varchar(8000) or normalize this table to not include commas (a row per value in the code you have now.)

|||

Hi,

What i do is the following, i get two tables from progress and im joining them with a union all, the thing is that since progress is not a sql db (it is a progress DB) i use a driver somthing like

SELECT CAST(COD_TERC as varchar) COD, CAST(DSC_TERC as varchar) DSC from OPENQUERY(......)

UNION ALL

SELECT CAST(COD_TERC as varchar) COD, CAST(DSC_TERC as varchar) DSC from OPENQUERY(.....)

group by COD

Then i get the error.

I'm just doing the select by now, im not inserting it anywhere.

Thanks

|||

Oops, my repro is missing the thing that caused the errors:

SELECT *
FROM test
ORDER BY textVal

If COD is a text column, then you will get this error. However, you can do something like:

SELECT *
FROM test
ORDER BY cast(textVal AS varchar(100))

And it will work (might be slow... but no error)

|||

I have tryied it, but i still got an error.

I did the change that you mentioned, but i got the error stating that the element in order by must appear in the order list if the statement contains a union operator.

So i do the cast on the select part i then i get the same error again

Thank you

|||

Do you need a UNION operator with or without ALL clause? In other words, do you need to eliminate duplicates between the multiple SELECT statements? If each SELECT statement produces a distinct result then use UNION ALL since that is more efficient and will provide better performance. This should also eliminate the error.

Please read the Books Online topic on UNION operator also. It documents the various restrictions like when text/ntext/image data type columns can be used with the UNION operator and so on. If you are still not able to resolve the problem then please post a simple repro script otherwise we will just keep guessing on what the problem might be without knowing what you are trying or how you have coded your SELECT statements.

|||

I do use a union all because i have to.

The query is the following:

select COD_TERC, DSC_TERC, COD_END, DSC_MORADA, DSC_LOCAL, COD_POSTAL, DSC_PAIS from OpenQuery(MULTI, 'Select A.Terc1_Cod COD_TERC, A.Terc1_Nome1 DSC_TERC, '''' COD_END, A.terc1_morada DSC_MORADA, A.terc1_local DSC_LOCAL, A.cdpst_cod COD_POSTAL, P.pais_descr DSC_PAIS From pub.ct_terc1 A Inner Join pub.tg_pais P On A.pais_cod = P.Pais_Cod Where A.empr_cod = 1 ') union all select COD_TERC, DSC_TERC, COD_END, DSC_MORADA, DSC_LOCAL, COD_POSTAL, DSC_PAIS from OpenQuery(MULTI, 'Select B.terc1_Cod COD_TERC, B.ende_nome1 DSC_TERC, B.ende_descr COD_END, B.ende_morada DSC_MORADA, B.ende_local DSC_LOCAL, B.cdpst_cod COD_POSTAL, P.pais_descr DSC_PAIS From pub.ct_ende B Inner Join pub.tg_pais P On B.pais_cod = P.Pais_Cod Where B.empr_cod = 1') Order by COD_TERC

Where cod_terc normally has a value like 1204 or some other number.

Thank you