how to convert null to zero or other numeric value at data object level?

[Rony Santoso]

Can we convert null value into zero or other numeric values or even set its
value to other column value at data window object level?
(the column must be editable though -- not using computed column)

I mean can we done it without having to code like this:
lc_amt = dw_1.getitemdecimal(dw_1.getrow(), "amt")
if isnull(lc_amt) then dw_1.setitem(dw_1.getrow(), "amt", 5)

the conversion must take place at data object itself not after it is placed
in a window.

thanks,
rony

[Chris Pollach]

Ron;

You would NEVER do that as that would change the state of the column in
the DWO to be different that what is actually on your DB. Remember, a null
is NOT = Zero (actually stored though as a -0 in the DB as zero is
mathematically = +0 .. weird eh). I believe what you want is to show a value
of "0" or the word "Zero" when a Null is present. To do this have a look at
Display and Edit Style masks for your affected columns (Cool PB feature).

regards ... Chris

"Rony Santoso" <ron...@yahoo.de> wrote in message
news:420b97fc@forums-2-dub...

[philipsalgannik]

It would depend on the DBMS you are using.
In Oracle you can use NVL in the SQL of your datawindow.

[M. Searer]

Actually, use COALESCE ( col_name, 0 ) instead of NVL.
Since this is 'standard' ansi sql, it works in MS SQL, ASE, ASA (I think) as
well as Oracle.

<Philip Salgannik> wrote in message news:420b9a67.667...@sybase.com...

[Rony Santoso]

The example that you gave me is for converting null to zero/numeric value.

can we also use it to get other field value?
for example: if isnull(field_amtunused) then set the value field_amtunused =
field_amtToPay

this is actually what I need to accomplish..

thanks,
rony

"M. Searer" <nos...@nospam.com> wrote in message
news:420bae47$1@forums-2-dub...

[Rony Santoso]

To the contrary, this field is actually a computed field derived from:
SELECT DISTINCT "arrcv"."arrcvid",
"arrcv"."amtrcv",
"arrcv"."custid",
"arrcv"."dateplaced",
(arrcv.amtrcv - (select sum(arpmt.amtpaid) from arpmt where
arpmt.arrcvid = arrcv.arrcvid)) as amtUnused ////////-----> this is the
field !!! <-----------//////
FROM ArRcv LEFT OUTER JOIN ArPmt ON ArRcv.ArRcvID = ArPmt.ArRcvID
WHERE ArRcv.CustID = :ll_custID

what i want to achieve is to change its value to the value of
arrcv.amtrcv ---> if amtunused = null

is it possible to do this?

cheers,
rony

"Chris Pollach" <Poll...@SCC-CSC.gc.ca> wrote in message
news:420b9a8f$1@forums-2-dub...

[Hans Peter Oechslin]

Try with

(coalesce(arrcv.amtrcv,0) - (select coalesce(sum(arpmt.amtpaid),0) from

arpmt where arpmt.arrcvid = arrcv.arrcvid)) as amtUnused

depending on Database server you have to use a different function. this
should work with Oracle, ASE, ASA, MS SQLServer

Good luck

Hans Peter Oechslin

O