Board index » delphi » Null value as float in SQL

Null value as float in SQL

Hi,

I've got a problem with my sql:

SELECT code, sum(amountinc - amountpaid)
FROM "z:\cdata\jkm\wbridge\invoices.db"
WHERE
(AmountPaid < AmountInc) or (amountpaid is null)
group by code

This only sums up records where Amountpaid has a figure in it. Any records
were amountpaid contains a null value, gives me a null value in "Sum of
amountinc - amountpaid", even though amountinc > 0!

How can I get it to pick up null values in the sum calculation so it treats
them as a zero?

I've even tried "SELECT code, sum(amountinc - cast(amountpaid as float))"
but had no success.

Using Paradox tables on Win2K box in Delphi 6.

Thanks & Regards

Adam Hair.

 

Re:Null value as float in SQL


Hi,

Off the top of my head, try:

/* select all records with amountpaid not null */
SELECT CODE, SUM(amountinc - amountpaid) AS amount
FROM "z:\cdata\jkm\wbridge\invoices.db"
WHERE (NOT amountpaid is null) and (AmountPaid < AmountInc)

UNION ALL

/* now add all records where amountpaid is null (ie zero in this context */
SELECT CODE, SUM(amountinc) AS amount
FROM "z:\cdata\jkm\wbridge\invoices.db"
WHERE (amountpaid is null) and (0 < AmountInc)

Alternatively, update your tables not to contain NULL values, like so:
UPDATE "z:\cdata\jkm\wbridge\invoices.db"
SET amountpaid = 0 WHERE amountpaid is NULL

Quote
"Adam H." <ah...@netconnect.com.au> wrote:
>Hi,

>I've got a problem with my sql:

>SELECT code, sum(amountinc - amountpaid)
>FROM "z:\cdata\jkm\wbridge\invoices.db"
>WHERE
>(AmountPaid < AmountInc) or (amountpaid is null)
>group by code

>This only sums up records where Amountpaid has a figure in it. Any records
>were amountpaid contains a null value, gives me a null value in "Sum of
>amountinc - amountpaid", even though amountinc > 0!

>How can I get it to pick up null values in the sum calculation so it treats
>them as a zero?

>I've even tried "SELECT code, sum(amountinc - cast(amountpaid as float))"
>but had no success.

>Using Paradox tables on Win2K box in Delphi 6.

>Thanks & Regards

>Adam Hair.

Re:Null value as float in SQL


Actually, I just realized, my initial suggestion won't work...

You need to summarize the result from my query again by code for it to work properly...

Apologies

Walter

Quote
"Walter Prins" <pr...@nospam.ananzi.co.za> wrote:

>Hi,

>Off the top of my head, try:

>/* select all records with amountpaid not null */
>SELECT CODE, SUM(amountinc - amountpaid) AS amount
>FROM "z:\cdata\jkm\wbridge\invoices.db"
>WHERE (NOT amountpaid is null) and (AmountPaid < AmountInc)

>UNION ALL

>/* now add all records where amountpaid is null (ie zero in this context */
>SELECT CODE, SUM(amountinc) AS amount
>FROM "z:\cdata\jkm\wbridge\invoices.db"
>WHERE (amountpaid is null) and (0 < AmountInc)

>Alternatively, update your tables not to contain NULL values, like so:
>UPDATE "z:\cdata\jkm\wbridge\invoices.db"
>SET amountpaid = 0 WHERE amountpaid is NULL

>"Adam H." <ah...@netconnect.com.au> wrote:
>>Hi,

>>I've got a problem with my sql:

>>SELECT code, sum(amountinc - amountpaid)
>>FROM "z:\cdata\jkm\wbridge\invoices.db"
>>WHERE
>>(AmountPaid < AmountInc) or (amountpaid is null)
>>group by code

>>This only sums up records where Amountpaid has a figure in it. Any records
>>were amountpaid contains a null value, gives me a null value in "Sum of
>>amountinc - amountpaid", even though amountinc > 0!

>>How can I get it to pick up null values in the sum calculation so it treats
>>them as a zero?

>>I've even tried "SELECT code, sum(amountinc - cast(amountpaid as float))"
>>but had no success.

>>Using Paradox tables on Win2K box in Delphi 6.

>>Thanks & Regards

>>Adam Hair.

Re:Null value as float in SQL


Hi again, I noticed I also left out the GROUP BY clause in my original solution (doh!) so, I thought I'd provide a complete corrected solution:

Put this in a file called "z:\cdata\jkm\wbridge\temp.sql":

SELECT CODE, SUM(amountinc - amountpaid) AS amount
FROM "z:\cdata\jkm\wbridge\invoices.db"
WHERE (NOT amountpaid is null) and (AmountPaid < AmountInc)
GROUP BY CODE

UNION ALL

SELECT CODE, SUM(amountinc) AS amount
FROM "z:\cdata\jkm\wbridge\invoices.db"
WHERE (amountpaid is null) and (0 < AmountInc)
GROUP BY CODE

-----------------------

Then the solution to the query is:
SELECT CODE, SUM(amount)
FROM "z:\cdata\jkm\wbridge\temp.sql"
GROUP BY CODE

HTH

Walter Prins

Quote
"Walter Prins" <pr...@nospam.ananzi.co.za> wrote:

>Actually, I just realized, my initial suggestion won't work...

>You need to summarize the result from my query again by code for it to work properly...

>Apologies

>Walter

>"Walter Prins" <pr...@nospam.ananzi.co.za> wrote:

>>Hi,

>>Off the top of my head, try:

>>/* select all records with amountpaid not null */
>>SELECT CODE, SUM(amountinc - amountpaid) AS amount
>>FROM "z:\cdata\jkm\wbridge\invoices.db"
>>WHERE (NOT amountpaid is null) and (AmountPaid < AmountInc)

>>UNION ALL

>>/* now add all records where amountpaid is null (ie zero in this context */
>>SELECT CODE, SUM(amountinc) AS amount
>>FROM "z:\cdata\jkm\wbridge\invoices.db"
>>WHERE (amountpaid is null) and (0 < AmountInc)

>>Alternatively, update your tables not to contain NULL values, like so:
>>UPDATE "z:\cdata\jkm\wbridge\invoices.db"
>>SET amountpaid = 0 WHERE amountpaid is NULL

>>"Adam H." <ah...@netconnect.com.au> wrote:
>>>Hi,

>>>I've got a problem with my sql:

>>>SELECT code, sum(amountinc - amountpaid)
>>>FROM "z:\cdata\jkm\wbridge\invoices.db"
>>>WHERE
>>>(AmountPaid < AmountInc) or (amountpaid is null)
>>>group by code

>>>This only sums up records where Amountpaid has a figure in it. Any records
>>>were amountpaid contains a null value, gives me a null value in "Sum of
>>>amountinc - amountpaid", even though amountinc > 0!

>>>How can I get it to pick up null values in the sum calculation so it treats
>>>them as a zero?

>>>I've even tried "SELECT code, sum(amountinc - cast(amountpaid as float))"
>>>but had no success.

>>>Using Paradox tables on Win2K box in Delphi 6.

>>>Thanks & Regards

>>>Adam Hair.

Re:Null value as float in SQL


Hi Walter,

Thanks for the example code. The union command will work, and appreciate it
greatly!

Best Regards

Adam.

Other Threads