sql placeholder rows

Question

I have an apex item P_USERS which can have a value higher than the amount of rows returning from the query below.

I have a classic report which has the following query:

  select 
       first_name,
       last_name
  from  accounts
  where account_role = 'Author'
  order by account_nr;

I want placeholder rows to be added to the query (first_name = null, last_name = null etc.), if the total rows from the query is lesser than the value in the apex_item P_USERS.

Any tips on how to achieve this? Maybe with a LEFT join?

score 1 · Answer 1 · answered Aug 29 '18 at 09:15

You may try to use a LEFT JOIN.

First, create a list of number until the limit you want like suggested here:

-- let's say you want 300 records
Select Rownum r From dual Connect By Rownum <= 300

Then you can use this to left join and have empty records:

SELECT C, R FROM  
  ( select rownum i, c from (select  'a' c from dual union all select 'b' from dual) )
, ( Select Rownum r From dual Connect By Rownum <= 300)
where i(+)= r order by r

The above gives you an ordered list starting with 'a', 'b', then null until the end.

So you could adapt it to your case so:

SELECT F,L FROM  
  ( select rownum i, f, l from (
             select first_name f, last_name l 
               from  accounts where account_role = 'Author'
            order by account_nr) )
, ( Select Rownum r From dual Connect By Rownum <= 300)
where i(+)= r

Somehow it doesn't show all records from account, if there are more accounts then rownums defined in the second select statement. — Elvira, Aug 29 '18 at 13:21
Sorry, no time to help you right now... maybe a new question could help. Ask wisely. — J. Chomel, Aug 29 '18 at 13:36

score 1 · Accepted Answer · answered Aug 29 '18 at 13:52

If you have more result than the minima you defined, you must add the rest with union.

Here is what you could try to adapt to your case:

  SELECT i,c FROM  (
select rownum i, c from (
select  'a' c from dual union all select 'b' from dual union all select 'd' from dual union all select 'be' from dual
)), (Select Rownum r From dual Connect By Rownum <= 3)
where (i(+)= r)
union select i,c from (select rownum i, c from (
select  'a' c from dual union all select 'b' from dual union all select 'd' from dual union all select 'be' from dual
)) where i>3

sql placeholder rows

2 Answers2