SQL request how to return all the date before one date - FROM keyword not found where expected-CodePudding

I have made the following code :

(SELECT MAX("tn".DATE) 
FROM A.Test_Name "tn" 
WHERE "tn".Test_NAME = 1
AND "tn".Date <= '18Dec2022')

Which does take the max date, how can I do to select all the "tn" dates below 18Dec2022 ?

I did try to do :

(SELECT "tn".DATE
FROM A.Test_Name "tn" 
WHERE "tn".Test_NAME = 1
AND "tn".Date <= '18Dec2022')

but this does throw an error :

SQL Error: ORA-00923: FROM keyword not found where expected

I use this query to do a JOIN

JOIN A.Test_Name tn ON tn.Test_Name = 1 AND tn.DATE = 
(SELECT MAX("tn".DATE) 
FROM A.Test_Name "tn" 
WHERE "tn".Test_NAME = 1
AND "tn".Date <= '18Dec2022')

CodePudding user response：

It looks like perhaps DATE is a keyword. Try quoting it like other identifiers:

SELECT "tn"."date"
FROM A.Test_Name "tn" 
WHERE "tn".Test_NAME = 1
AND "tn"."date" <= '18Dec2022'

CodePudding user response：

You can use the analytic RANK function to find the maximum values (without having to query the table twice):

SELECT *
FROM   (
  SELECT tn.*,
         RANK() OVER (ORDER BY tn."DATE") AS rnk
  FROM   A.Test_Name tn
  WHERE  tn.Test_Name =  1
  AND    tn."DATE"    <= DATE '2022-12-18'
)
WHERE  rnk = 1;

Note 1: Using quoted identifiers is generally considered bad practice as you have to use exactly the same case for the quoted identifiers; whereas if you use unquoted identifiers then you can use any case.

Note 2: However, DATE is a reserved word and if you are going to use it as an identifier then you must use a quoted identifier.