I have a select list to show a list of Nationalities which is
SELECT DESCRIPTION d, NATIONALITY_ID r FROM NATIONALITY ORDER BY DESCRIPTION;
This brings back my list in Alphabetical order which is fine. I now want the default value to be ‘British’ as this will be the most common one used in this scenario. However even with ‘Display Null Value’ set to ‘No’ and after trying to set Default to Static and setting that to ‘British’ it won’t work, I’ve also tried setting Default to ‘SQL Query’ and that query being
SELECT DESCRIPTION FROM NATIONALITY WHERE DESCRIPTION = ‘British’
Everything I try doesn’t work and it’ll continue to show the first selection in the list.
CodePudding user response:
Modify ORDER BY
clause:
SQL> with nationality (description, nationality_id) as
2 (select 'Austrian', 1 from dual union all
3 select 'Croatian', 2 from dual union all
4 select 'British' , 3 from dual union all
5 select 'French' , 4 from dual
6 )
7 select description d, nationality_id r
8 from nationality
9 order by case when description = 'British' then 1
10 end,
11 description;
D R
-------- ----------
British 3
Austrian 1
Croatian 2
French 4
SQL>
CodePudding user response:
The value of the select list item is the return value, not the display value.
Suppose you have a form on a users table. One of the page items will be "Nationality". In the users table you'd then have a column nationality_id with a foreign key to a nationality table. The page item P1_NATIONALITY will contain the value that is stored in the users table - which is nationality_id.
To set a default, you could use "SQL Query" like this:
SELECT NATIONALITY_ID FROM NATIONALITY WHERE DESCRIPTION = ‘British’