sql query returns 0 if parameter is null (Open Office base)

Question

I have a table "pupils" consisting of the following coloumns:

int      IDpupil
varchar  name
int      class

where class is a foreign key and not nullable, i.e. every pupil has to be in a class.

I'd like to see all pupils in a class depending on a class number I want to specify each time new.

The following statement does the intended if I specify a class:

SELECT "IDpupil", 
       "name", 
       "class" 
  FROM "pupils" 
 WHERE (("class" = :qClass) 
        OR (:qClass is null))

But if I want to see all pupils in that table and therefore do not specify a number it returns only the pupils of class "0". If I do this with names it works. It seems to me a null parameter gets cast to 0, what I don't want. How can I fix this?

I tried to use a case statement, which should test wether the input is Null or "", but I didn't get it to work. Maybe also for the complexity of a case statement. Then I tried this link from here. Didn't help either.

is `class` nullable? I.e. is it `int class` or `int class null` in the definition? — Sergey Kalinichenko, Aug 05 '14 at 13:45
See if it work: `((0=:qClass and :qClass is null) or ("class" = :qClass))` — Jorge Campos, Aug 05 '14 at 14:09
It looks like the trick would be to convince the system that `:qClass` is nullable, i.e. that it remains `null` when you do not specify any number. In other words, it sounds like the code path that leads to the query interprets an empty `:qClass` as if it were zero, not a `null`. — Sergey Kalinichenko, Aug 05 '14 at 14:14
@dasblinkenlight my thougths exactly, anyone any ideas how to solve this? — Sebastian, Aug 05 '14 at 16:51

score 0 · Accepted Answer · answered Aug 05 '14 at 14:11

0

i cant see where exactely is your probleme but if you want to desplay all puils just a big select wihtout any WHERE close

SELECT "IDpupil", 
       "name", 
       "class" 
  FROM "pupils"

answered Aug 05 '14 at 14:11

mugiwaradz

393
1
3
15

I got this far. If I walk this way I have to implement two queries, where one is a special case of the other. I don't want to do this. – Sebastian Aug 05 '14 at 16:42
I coud not find any other solution. So I (have to) do it this way. – Sebastian Aug 17 '14 at 21:31

sql query returns 0 if parameter is null (Open Office base)

1 Answers1