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:

The value of first_numbers=1112 is equal to :p4_number=1112 page item so the expected result which I want is:

CodePudding user response：

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