TO_CHAR is failing in SQL query-CodePudding

Why TO_CHAR(rln.FECHA_CONSULTA,'dd-MM-yyyy')AS'FECHA_CONSULTA' is failing when i'm executing this script?

SELECT 
rln.CIF_NIF,
rln.MOTIVO,
TO_CHAR(rln.FECHA_CONSULTA,'dd-MM-yyyy') AS 'FECHA_CONSULTA' --fail here
FROM RASTRO_LISTA_NEGRA rln 
WHERE rln.FECHA_CONSULTA >= TO_DATE (CONCAT(TO_CHAR(sysdate-1,'dd/MM/yyyy'),'08:00:00'), 'DD/MM/YYYY HH24:MI:SS')

I'm using Oracle

CodePudding user response：

Single quotes are used for string literals. Not for alias names.

So change this

AS 'FECHA_CONSULTA' --fail here

To this

AS "FECHA_CONSULTA" --alright

Or this

AS FECHA_CONSULTA   --okidoki

Also, the WHERE clause could be simplified

WHERE rln.FECHA_CONSULTA >= (TRUNC(SYSDATE-1)   INTERVAL '08:00' HOUR TO MINUTE)

CodePudding user response：

This is mostly essentially a typo: single quotes are used for literal values, double quotes are used for identifiers.

From the documentation:

... you represent the name of an object with a quoted identifier or a nonquoted identifier.

A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

A nonquoted identifier is not surrounded by any punctuation.

So AS 'FECHA_CONSULTA' should be AS "FECHA_CONSULTA", or just AS FECHA_CONSULTA since the alias identifier doesn't need to be quoted in this case.

But you can also simplify this:

TO_DATE (CONCAT(TO_CHAR(sysdate-1,'dd/MM/yyyy'),'08:00:00'), 'DD/MM/YYYY HH24:MI:SS')

to:

TRUNC(sysdate-1)   INTERVAL '8' HOUR

TRUNC(sysdate-1)   (8/24)