• Welcome, Guest. Please login.
 
July 13, 2020, 04:07:16 am

News:

Welcome to the SQLitening support forums!


Division with zero

Started by Fim, April 13, 2018, 02:15:44 pm

Previous topic - Next topic

Fim

SELECT BES_KVANT * PRIS / PRISKVANT FROM IRA

Works as i should, but...
For some records PRISKVANT  is zero.
In that case I will use 1 instead.
But how to put an if-statment in that SELECT-command?
Like  SELECT BES_KVANT * PRIS / (if PRISKVANT = 0 then 1 else PRISKVANT) FROM IRA
Pls, don't say RTM, i have tried.
/Fim
Fim W


Jean-Pierre LEROY

Hi Fim,

Yes you should try the CASE expression in your SELECT statement:

SELECT CASE PRISKVANT WHEN 0 THEN 1 ELSE BES_KVANT * PRIS / PRISKVANT END FROM IRA

More info here https://www.sqlite.org/lang_expr.html

Hope that helps,
Jean-Pierre Leroy


Jean-Pierre LEROY

Hi cj,

It almost impossible to read your message in this forum.

The font you use is so small, impossible to read.

Regards,
Jean-Pierre

Bern Ertl

SELECT BES_KVANT * PRIS / (CASE WHEN PRISKVANT > 0 THEN PRISKVANT ELSE 1 END) FROM IRA

cj

April 16, 2018, 03:16:28 pm #5 Last Edit: April 17, 2018, 11:48:20 am by cj
Quote
SELECT BES_KVANT * PRIS / PRISKVANT FROM IRA
Works as i should, but..
For some records PRISKVANT  is zero.
In that case I will use 1 instead.
But how to put an if-statment in that SELECT-command?
Like  SELECT BES_KVANT * PRIS / (if PRISKVANT = 0 then 1 else PRISKVANT) FROM IRA
Pls, don't say RTM, i have tried.
/Fim   

Different results so FIM has to decide.

Bern appears to handle everything as problem was stated.
His routine will skip invalid input and handles NULL the same as 0.
If negative numbers are allowed his routine can be modified from  > 0  to <> 0.
Each result is correct in its own way of reading the question.

It was not stated in the question if negative,null or real numbers are possible or what to do with a string if it creeps into the mix.
Also shows how a UNION and CAST could be used.

I checked "Show WYSIWYG editor on post page" a few days ago.  It is now unchecked.
Getting rid of invalid font and size statements.


Posted the type of all input then followed it by the answer from each solution and combined into a MSGBOX at the end.

'Real added to create table to make it more interesting
'Allowed negative numbers, strings and NULL into input
'Shows logical results using case and cast
GLOBAL gs AS STRING
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG  'fim3.bas
LOCAL sql AS STRING
slopen "sample.db3","C"
slexe "drop table if exists ira"
slexe "create table if not exists ira(bes_kvant integer,pris integer,priskvant real)"
slexe "insert into ira values(1,10,0)"      '1,10
slexe "insert into ira values(2,20,'Z')"    '2,40
slexe "insert into ira values(3,30,3.1)"      '3,30
slexe "insert into ira values(4,40,-4)"     '4,-40
slexe "insert into ira values(5,50,null)"   '5,250
gs = CHR$(13,"logical results",13)
sql =  "select rowid, case "       +_
     "  when cast(priskvant as integer) > 0  then 'greater 0'"  +_
      " when priskvant isnull then 'null'" +_
      " when priskvant < 0    then 'less 0'"     +_
      " when priskvant = 0    then 'zero'"       +_
      " else  'string'"            + _
      " end" +_
      " from ira"
doit sql


gs+= CHR$(13,"case and cast",13)
sql =  "select rowid, case "       +_
     "  when cast(priskvant as integer) > 0  then bes_kvant * pris /priskvant"  +_
      " when priskvant < 0                   then bes_kvant * pris /priskvant"  +_                                  +_
      " else bes_kvant * pris"                   + _
      " end" +_
      " from ira"
doit sql


gs+=CHR$(13,"cast union",13)
sql =  " select rowid, bes_kvant * pris" +_
       " from ira" +_
       " where cast(priskvant as integer) = 0" +_
       " union" +_
       " select rowid, bes_kvant * pris / priskvant" +_
       " from ira" +_
       " where cast(priskvant as integer) > 0" +_
       " union" +_
       " select rowid, bes_kvant * pris / priskvant" +_
       " from ira" +_
       " where cast(priskvant as integer) < 0" +_
       " union" +_
       " select rowid, bes_kvant * pris" +_
       " from ira" +_
       " where priskvant isnull"
doit sql


gs+=CHR$(13,"skips null",13)
sql =  " select rowid, bes_kvant * pris" +_
       " from ira" +_
       " where cast(priskvant as integer) = 0" +_
       " union" +_
       " select rowid, bes_kvant * pris / priskvant" +_
       " from ira" +_
       " where cast(priskvant as integer) > 0 or priskvant < 0"
doit sql


bern:
gs+= CHR$(13,"bern",13)
sql = "SELECT rowid,BES_KVANT * PRIS / (CASE WHEN PRISKVANT > 0 THEN PRISKVANT ELSE 1 END) FROM IRA"
doit sql


paul:
gs+= CHR$(13,"paul squires",13)
sql ="SELECT rowid,BES_KVANT * PRIS / NULLIF(PRISKVANT,0) FROM IRA
doit sql


jean_paul:
gs+= CHR$(13,"jean-paul",13)
sql = "SELECT rowid,CASE PRISKVANT WHEN 0 THEN 1 ELSE BES_KVANT * PRIS / PRISKVANT END FROM IRA"
doit sql


? gs
END FUNCTION

SUB doit(sql AS STRING)
slsel sql
DO WHILE slGetRow
  gs+= slf(1) + "," + slf(2) + $CR
LOOP
END SUB'

Bern Ertl

Nice work cj.  Not sure why your posts are now adjusting the font size to size=2, but I was able to read your post in the message editor after hitting the quote button.

cj

April 17, 2018, 11:21:13 am #7 Last Edit: April 22, 2018, 11:15:06 am by cj
I had checked "Show WYSIWYG editor on post page" a few days ago.
It is now unchecked in Modify Profile, Look and Layout.

I have gone back and removed fonts and sizes from previous post.
When Jean-Pierre reported it, I looked, but didn't see anything strange here.   I'm now on another machine.

Thanks to both of you for reporting it.   

Fim

Thank you for all the tips and advice. Because it's a bit difficult with division with zero, I've decided to go another way. I will ensure that term PRISKVANT is always greater than zero.
/Fim
Fim W