B4J Question SQL Query - add dummy rows if number of rows is under value x

[h725]

Hello B4X community,

I have a question concerning an sql query.

I always need to get a minimum number of returned rows in an sql query. If the number
of the returned rows is under a defined number I have to add dummy rows.
If the sql query returns the defined number of rows or more, then there is no need to add extra rows.
The defined number is a variable and can change.

Example (in this case the number of minimum returned rows is 5):

Table rooms

id	room
1	Room 1
2	Room 2
3	Room 3

"select id, room from rooms" would return the following:

1 room1
2 room2
3 room3

but I need something like that:

1 room1
2 room2
3 room3
0 empty
0 empty

Table rooms

id	room
1	Room1
2	Room2
3	Room3
4	Room4
5	Room5
6	Room6

In this case there is no need to add extra rows because the number of result rows is > 5.

1 room1
2 room2
3 room3
4 room4
5 room5
6 room6

Any idea?

Kind regards
h725

 

[roerGarcia]

It is not easier to do it with the result set size versus the desired number of rows?

 

[EnriqueGonzalez]

SELECT *
FROM (
select id, room
FROM rooms
union all
Select 0 id, 'empty room' room
union all
Select 0 id, 'empty room' room
union all
Select 0 id, 'empty room' room
union all
Select 0 id, 'empty room' room
union all
Select 0 id, 'empty room' room
) LIMIT 5

if there are less than 5 it, on of the dummies will appear

 

[h725]

Thanks. I edited the first post a little bit. The defined number is a variable and can change.
It can be 5, 10 or 20 etc.

I found something which goes into the direction, but the number is fixed:

Add extra rows in result set of query with dummy data in sql server

I have this table let's say Students. If there are 2 students in table and result set gives 2 rows than I want to add 5 more rows with dummy data in it no matter what it says i.e 'Dummy Record' or

stackoverflow.com

 

[aeric]

It’s easier to use B4X to add the dummy with loop after getting the query results.

 

[roerGarcia]

Sorry, isn't it an abuse as programmers to do something like that? Is it not easier to manipulate the size of the result and do what is necessary to present what you want?

 

[h725]

Hello,
thank you for your answers. I know that the task is a little bit tricky. I will explain why I need it this way and explain the background:
The used IDE is not B4J but VS .net. The reason why I am asking the question in this forum is because I got very good feedback
and solutions from the community in the past. If it is not wanted please move the thread to a appropiate theme or delete it. I would also pay
for the solution so maybe the business forum might fit ;-)

I have a control which generates a ganttchart from a database. I do not have the sourcecode of the control. The control has several advantages,
that is the reason why I would like to use this one. It has one disadvantage: It streches the height of the row depending of the quantity
of the entries. The following illustration is done with openoffice, but the principles shout be clear

Picture 1,2,3 current behavior, Picture 4 is the wanted behaviour.

I have no chance to loop through the resultset. I can only insert an sql query due to internal addressing the control.

Kind regards
h725

 

[roerGarcia]

in that case the proposals submitted are valid.
Try them.

 

[EnriqueGonzalez]

you could mix my advice with a loop

MySQL :: MySQL 5.7 Reference Manual :: 13.6.5.5 LOOP Statement

dev.mysql.com

you could pass the numer of rows you need and inside the dummy row

 

[h725]

Ok Thank you very much. I will try.