I want to select a column value which is equal to a specific page item value and select the other values as null. The main table data is the following:
| id | first_numbers | second_numbers |
|---|---|---|
| 1 | 1112 | 2222 |
| 1 | 3434 | 1112 |
The value of first_numbers=1112 is equal to :p4_number=1112 page item so the expected result which I want is:
| id | first_numbers | second_numbers |
|---|---|---|
| 1 | 1112 | null |
| 1 | null | 1112 |
Use CASE expression:
Sample data:
SQL> with test (id, first_numbers, second_numbers) as
2 (select 1, 1112, 2222 from dual union all
3 select 2, 3434, 1112 from dual
4 )
Query itself:
5 select id,
6 case when first_numbers <> &&P4_NUMBER then null else first_numbers end first_numbers,
7 case when second_numbers <> &&P4_NUMBER then null else second_numbers end second_numbers
8 from test;
Enter value for p4_number: 1112
ID FIRST_NUMBERS SECOND_NUMBERS
---------- ------------- --------------
1 1112
2 1112
SQL>
As you use Apex, you'd use bind (instead of substitution) variable, i.e.
... case when first_numbers <> :P4_NUMBER then
^
|
this
