Character order of NLS_SORT WEST_EUROPEAN

Question

I have a template on a table T1

T1

ID | Name
---|----------
 1 | template1

Then I have some parameters that needs to be filled when someone use the template on a table T2

T2

ID | Template_ID | Params
---|-------------|---------
 1 |      1      | @param1
 2 |      1      | @param2
 3 |      1      | @param3
 4 |      1      | @param4

Table T3 link the template and the params with the specific values (if the user doesn't set a param, the table it will be filled with the default name, so the user can set it later)

T3

ID | Param_ID | Value
---|----------|---------
 1 |     1    |  xyz
 2 |     2    | @param2
 3 |     3    |   1
 4 |     4    | @param4

So, i need to list the templates that have, at least, 1 param without a specific value, in only 1 line, but with this query:

select T1.Name, T3.Value
from T1 inner join T2
    on T1.ID = T2.Template_ID
inner join T3
    on T2.ID = T3.Param_ID
where T3.Value like '@%';

I got 2 lines as a result. The problem is, I have a system over oracle DB and I can't do my own queries or change the DB parameters, only select the columns of the tables (or use only this 4 functions max, min, sum and count over the columns) and the system do all the query by itself, so, I can't use big tricks on 'where' or 'order by' clause (force NLS_SORT as Binary, or use ROWNUM = 1, for example). So, my only idea is use another character instead of @ to identify the default params, to use MAX or MIN function over the T3.Value, but the character needs to be, for sure, the first or the last one in the WEST_EUROPEAN sort and I can't find anywhere which is the sort order of this. Can anyone help me?

Answer 1

Does list of functions you are allowed to use include LISTAGG? If so, it will return the result in 1 line. Here's an example:

SQL> with
  2  t1 (id, name) as
  3    (select 1, 'template1' from dual),
  4  t2 (id, template_id, params) as
  5    (select 1, 1, '@param1' from dual union all
  6     select 2, 1, '@param2' from dual union all
  7     select 3, 1, '@param3' from dual union all
  8     select 4, 1, '@param4' from dual
  9    ),
 10  t3 (id, param_id, value) as
 11    (select 1, 1, 'xyz'     from dual union all
 12     select 2, 2, '@param2' from dual union all
 13     select 3, 3, '1'       from dual union all
 14     select 4, 4, '@param4' from dual
 15    )
 16  select t1.name, listagg(t3.value, ',') within group (order by t3.value) params
 17  from t1 join t2 on t1.id = t2.template_id
 18  join t3 on t3.param_id = t2.id
 19  where t3.value like '@%'
 20  group by t1.name;

NAME      PARAMS
--------- --------------------
template1 @param2,@param4

SQL>