Tablename prefix not used

Question

Post reply

Tablename prefix not used

Mithrandir

SSCrazy

Points: 2628

February 3, 2006 at 11:19 am

#112695

Hi, I have this query:

SELECT TOP 10
S.id_solicitud, 
CONVERT(NCHAR(10), S.fecha_creacion, 120) AS fecha_creacion, 
S.nombre, 
S.clave_material,
IsNull(CONVERT(NCHAR(10), S.fecha_ini_pruebas, 120), 'N/A') AS fecha_ini_pruebas,
IsNull(CONVERT(NCHAR(10), S.fecha_fin_pruebas, 120), 'N/A') AS fecha_fin_pruebas,
IsNull(CONVERT(NCHAR(10), S.fecha_emision_reporte, 120), 'N/A') AS fecha_emision_reporte
FROM
dbo.tblSolsInstrum_Solicitudes S
WHERE
id_usuario = 'car6527'
ORDER BY
S.fecha_creacion DESC

The problem is that the sortig is not resolving the S prefix to reference the full-lenght field, so, the ordering is not being performed well.

I know that simply renaming the resulting date convert field the problem is fixed, but TSQL should be working as is supposed to be 🙁

Any ideas?

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 1

try ..

SELECT TOP 10

S.id_solicitud,

CONVERT(NCHAR(10), S.fecha_creacion, 120) AS fecha_creacion,

S.nombre,

S.clave_material,

IsNull(CONVERT(NCHAR(10), S.fecha_ini_pruebas, 120), 'N/A') AS fecha_ini_pruebas,

IsNull(CONVERT(NCHAR(10), S.fecha_fin_pruebas, 120), 'N/A') AS fecha_fin_pruebas,

IsNull(CONVERT(NCHAR(10), S.fecha_emision_reporte, 120), 'N/A') AS fecha_emision_reporte

FROM

dbo.tblSolsInstrum_Solicitudes S

WHERE

id_usuario = 'car6527'

ORDER BY

2 DESC

Mathew J Kulangara
sqladventures.blogspot.com

Mithrandir SSCrazy Points: 2628 More actions · Answer 2

Hi Mathew.

That did'nt work either. The "2" references the column after processing, this equivals the "fecha_creacion" without the S prefix.

In other words, that is what is happening with the original query. 🙁

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

Interesting issue

SELECT TOP 10
S.id_solicitud, 
CONVERT(NCHAR(10), S.fecha_creacion, 120) AS fecha_creacion, 
S.nombre, 
S.clave_material,
IsNull(CONVERT(NCHAR(10), S.fecha_ini_pruebas, 120), 'N/A') AS fecha_ini_pruebas,
IsNull(CONVERT(NCHAR(10), S.fecha_fin_pruebas, 120), 'N/A') AS fecha_fin_pruebas,
IsNull(CONVERT(NCHAR(10), S.fecha_emision_reporte, 120), 'N/A') AS fecha_emision_reporte
FROM
dbo.tblSolsInstrum_Solicitudes S
WHERE
id_usuario = 'car6527'
ORDER BY
fecha_creacion DESC

probably gives what you want

but I have no clue what will happen when you add more tables.

Mithrandir SSCrazy Points: 2628 More actions · Answer 4

Hi Jo

Unfortunately that gives the same result, this way the result has sense for me, since the ORDER BY is referencing the resulting column instead the source one.

Another shot?

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 5

I've gotten different sortorders with

SELECT TOP 10

CONVERT(NCHAR(10), S.fecha_creacion, 120) AS fecha_creacion

,S.fecha_creacion AS B

,ID

FROM dbo.test S

ORDER BY B DESC

ID 1 on top

SELECT TOP 10

CONVERT(NCHAR(10), S.fecha_creacion, 120) AS fecha_creacion

,S.fecha_creacion AS B

,ID

FROM dbo.test S

ORDER BY fecha_creacion DESC

ID 2 on top

CREATE TABLE [TEST] (

[fecha_creacion] [datetime] NULL ,

[ID] [char] (10) NULL

) ON [PRIMARY]

GO

INSERT INTO dbo].[TEST]([fecha_creacion], [ID])

VALUES ('2005/10/01 12:00:00',1)

INSERT INTO dbo].[TEST]([fecha_creacion], [ID])

VALUES ('2005/10/01 10:00:00',2)

INSERT INTO dbo].[TEST]([fecha_creacion], [ID])

VALUES (NULL,3)

INSERT INTO dbo].[TEST]([fecha_creacion], [ID])

VALUES ('2005/01/01',4)

PW-201837 SSC-Insane Points: 20805 More actions · Answer 6

Select from a derived table, where the derived table contains BOTH columns. In the derived table resultset, name the columns differently.

Then when you select out of the derived table, select the convert()'ed column and orderby the original column.

Select top 10 {columns}

From

(

Select

CONVERT(NCHAR(10), S.fecha_creacion, 120) AS fecha_creacion,

S.fecha_creacion As RawData,

{other columns}

From .. bla blah

) dt

Order By RawData

Mithrandir SSCrazy Points: 2628 More actions · Answer 7

The best solution I got is like Jo's one. Re-select the field to sort by that field.

A boogie-man tsql story. I hope that to be solved in sql2005.

Thanks!

SELECT TOP 10
S.id_solicitud, 
CONVERT(NCHAR(10), S.fecha_creacion, 120) AS fecha_creacion, 
S.nombre, 
S.clave_material,
IsNull(CONVERT(NCHAR(10), S.fecha_ini_pruebas, 120), 'N/A') AS fecha_ini_pruebas,
IsNull(CONVERT(NCHAR(10), S.fecha_fin_pruebas, 120), 'N/A') AS fecha_fin_pruebas,
IsNull(CONVERT(NCHAR(10), S.fecha_emision_reporte, 120), 'N/A') AS fecha_emision_reporte,
S.fecha_creacion AS order_field
FROM
dbo.tblSolsInstrum_Solicitudes S
WHERE
id_usuario = 'car6527'
ORDER BY
order_field DESC