Variable : path_start_date=14-MAY-21,17-MAY-21,06-APR-12
Query :
select greatest(''||REPLACE(''''||&path_start_date||'''',',',''',''')||'')
from dual;
Expected Output = 17-MAY-21
Can anyone suggest what's wrong with the above query.
Asked
Active
Viewed 64 times
0
Littlefoot
- 131,892
- 15
- 35
- 57
Gaurav Thuckral
- 13
- 3
1 Answers
0
Split input string into rows, and fetch the MAX value:
SQL> with temp (val) as
2 (select '14-MAY-21,17-MAY-21,06-APR-12' from dual)
3 select
4 max(to_date(regexp_substr(val, '[^,]+', 1, level), 'dd-mon-yy',
5 'nls_date_language = english'
6 )
7 ) max_datum
8 from temp
9 connect by level <= regexp_count(val, ',') + 1
10 /
MAX_DATUM
---------
17-MAY-21
SQL>
Littlefoot
- 131,892
- 15
- 35
- 57
-
Could you please provide solution in dynamic query or help me found the error in my query?? – Gaurav Thuckral Aug 27 '21 at 13:11
-
Dynamic what? Instead of a string in line #2, use e.g. `select :par_string from dual` instead. – Littlefoot Aug 27 '21 at 13:29