Grade Banding, NLS-Dependent Date Filters and Sort Order

grade_code is VARCHAR2 and the literal '2' is a string, so Oracle performs a character comparison, not a numeric one. Character by character, '1' is less than '2', so '10' sorts before '2' and the predicate grade_code >= '2' is FALSE for grade '10'. The top grade is therefore labelled JUNIOR - exactly the 40 missing SENIOR rows (570 returned instead of 610).

A subtler point in the same expression: grade_code is nullable for some legacy staff, and NULL >= '2' is UNKNOWN, so those rows fall to ELSE and are silently reported as JUNIOR. That is a business decision, not a default; make it explicit with a third branch.

SELECT emp_name,
       CASE WHEN grade_code IS NULL THEN 'UNGRADED'
            WHEN TO_NUMBER(grade_code) >= 2 THEN 'SENIOR'
            ELSE 'JUNIOR'
       END AS band
FROM employees;

Two cautions. Converting the column to a number fixes the ordering, but the reverse form - comparing the VARCHAR2 column to a numeric literal like grade_code >= 2 - also forces an implicit conversion of the column and raises ORA-01722 if any row holds a non-numeric grade such as 'A1'. Both directions disable any plain index on grade_code. The structural fix is a lookup table with a numeric grade and a grade_band column, joined to EMPLOYEES, so comparisons never depend on how a code was typed.

'01-JUL-2023' is a string, not a date. When it is compared with a DATE column, Oracle converts the string using the session's NLS_DATE_FORMAT. SQL*Plus has 'DD-MON-RR', which matches the text, so the implicit conversion succeeds. The BI tool has 'YYYY-MM-DD', so Oracle tries to read '01' as a four-digit year and hits the letters 'JUL', producing ORA-01858 ('a non-numeric character was found where a numeric was expected').

This is not a BI tool defect; it is a query that depends on session state and will break for any user with different NLS settings, including an ad hoc session where someone ran ALTER SESSION.

Preferred - ANSI date literal, immune to NLS:

WHERE hire_date >= DATE '2023-07-01'

Explicit and language-independent when parsing application strings:

WHERE hire_date >= TO_DATE('01-JUL-2023','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')

The NLS_DATE_LANGUAGE argument matters for month names; without it, a session with NLS_DATE_LANGUAGE=GERMAN would fail to recognise 'JUL'. Both forms compare the bare column against a constant, so any index on hire_date remains usable - unlike TO_CHAR(hire_date,'YYYY-MM-DD') = '2023-07-01', which wraps the column and forces a full scan.

Both symptoms come from the same fact: the column is a string, and sorting follows the session collation. With NLS_SORT=BINARY, Oracle compares the internal character encoding. Unaccented Latin letters occupy lower code points than accented ones, so 'Zimmer' sorts before 'Ähren' (Ä = U+00C4), pushing all accented names to the end. For a purely ASCII code such as '10' versus '2', binary comparison compares '1' with '2' and stops: '10' is smaller, so the order is '10','100','2' - correct as text, wrong as numbers.

Linguistic ordering:

ORDER BY NLSSORT(emp_name, 'NLS_SORT=GERMAN') ;
-- or, for the whole session:
ALTER SESSION SET NLS_SORT=GERMAN;

Numeric ordering of a code stored as text:

ORDER BY TO_NUMBER(code) NULLS LAST;
-- 19c-safe when some codes are alphanumeric:
ORDER BY TO_NUMBER(code DEFAULT NULL ON CONVERSION ERROR) NULLS LAST, code;

VALIDATE_CONVERSION(code AS NUMBER) = 1 in a CASE gives the same protection. LPAD to a fixed width works only if the maximum length is known and enforced.

Performance note: NLSSORT(emp_name, ...) and TO_NUMBER(code) are functions on the column, so the optimizer cannot use a plain index on that column for the sort. If these orderings run frequently on large sets, create function-based indexes matching the exact expressions (for example on NLSSORT(emp_name,'NLS_SORT=GERMAN')) and query with the identical expression.