• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Money no leading zero using CASE

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

Previous topic - Next topic

cj

#INCLUDE "sqlitening.inc"
'added these 2-macros 2/15/22
MACRO money0(colname)= CHR$("printf('%.2f',",colname,"*.01)") 'with leading 0
MACRO money(colname)= CHR$("ltrim(printf('%.2f',",colname,"*.01),'0')")

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
 


"No email alerts being received"  Please often check back.