SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Division with zero  (Read 301 times)

Fim

  • Guru
  • ***
  • Posts: 145
    • View Profile
    • Fims sajt
Division with zero
« on: April 13, 2018, 11:45:44 AM »

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
Logged
Fim W

Jean-Pierre LEROY

  • Master Geek
  • ****
  • Posts: 261
  • expect nothing, be prepared for everything ...
    • View Profile
Re: Division with zero
« Reply #2 on: April 14, 2018, 03:47:46 AM »

Hi Fim,

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

Code: [Select]
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

Logged

Jean-Pierre LEROY

  • Master Geek
  • ****
  • Posts: 261
  • expect nothing, be prepared for everything ...
    • View Profile
Re: Division with zero
« Reply #3 on: April 14, 2018, 08:50:59 AM »

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
Logged

Bern Ertl

  • Master Geek
  • ****
  • Posts: 491
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Division with zero
« Reply #4 on: April 16, 2018, 09:08:50 AM »

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

cj

  • Master Geek
  • ****
  • Posts: 664
    • View Profile
Re: Division with zero
« Reply #5 on: April 16, 2018, 12:46:28 PM »

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.
Code: [Select]
'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'
« Last Edit: April 17, 2018, 09:18:20 AM by cj »
Logged
CJ

Bern Ertl

  • Master Geek
  • ****
  • Posts: 491
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Division with zero
« Reply #6 on: April 17, 2018, 07:24:40 AM »

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

  • Master Geek
  • ****
  • Posts: 664
    • View Profile
Re: Division with zero 4/17/18
« Reply #7 on: April 17, 2018, 08:51:13 AM »

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.   
« Last Edit: April 22, 2018, 08:45:06 AM by cj »
Logged
CJ

Fim

  • Guru
  • ***
  • Posts: 145
    • View Profile
    • Fims sajt
Re: Division with zero
« Reply #8 on: April 22, 2018, 02:04:05 AM »

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
Logged
Fim W