Ndtyjky

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?

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>