• Welcome, Guest. Please login.
 
December 10, 2019, 05:01:56 am

News:

Welcome to the SQLitening support forums!


Money no leading zero using CASE

Started by cj, April 18, 2019, 09:39:27 pm

Previous topic - Next topic

cj

April 18, 2019, 09:39:27 pm Last Edit: April 20, 2019, 06:03:14 pm by cj
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG

 LOCAL sql,sArray() AS STRING

 slopen "junk.db3","C"

 slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1 integer)"

 slexe "insert into t1 values(-1000),(-10),(-9),(-1),(0),(1),(9),(10),(100),(1000)"

 sql = "select rowid,"  +_
       " case" +_
       " when c1 > 0 then       ltrim(printf('%.2f',c1*.01),'0')"  +_
       " when c1 < 0 then '-'|| ltrim(printf('%.2f',c1*.01),'-0')" +_
       " else '.00'" +_
       " end  AS TheValues"+_
       " from t1"

 slSelAry sql,sArray(),"Q9"
 ? JOIN$(sArray(),$CR),,"No leading zero"

END FUNCTION