Board index » delphi » Need SQL Statement Help

Need SQL Statement Help

The following SQL Statement works fine:
SELECT S.Name, Sum(E."Score 1" + E."Score 2" + E."Score 3") as TotScore
FROM Schools S, Entries E
WHERE (SUBSTRING(E."Contestant ID" FROM 2 FOR 3) = S."School #") OR
E."Contestant ID" IS NULL
GROUP BY S.Name
ORDER BY TotScore DESC, S.Name

However, if I add E."Score 4" to the mix like this:

SELECT S.Name, Sum(E."Score 1" + E."Score 2" + E."Score 3" + E."Score 4") as
TotScore
FROM Schools S, Entries E
WHERE (SUBSTRING(E."Contestant ID" FROM 2 FOR 3) = S."School #") OR
E."Contestant ID" IS NULL
GROUP BY S.Name
ORDER BY TotScore DESC, S.Name

TotScore returns null for every field.  I'm assuming that this is because
there is no #."Score 4" field that has a value.  does this not having a
value cause the rest of the calculation to be Null as well?  If so, how do I
avoid this?  I want to be able to add all the fields in there, just in case.

Thanks,
Jesse
www.davinci-mims.com

 

Re:Need SQL Statement Help


Quote
"Anchorman" <Je...@davinci-mims.com> wrote in message

news:3cb1f311$1_2@dnews...

Quote

> TotScore returns null for every field.  I'm assuming that this is because
> there is no #."Score 4" field that has a value.  does this not having a
> value cause the rest of the calculation to be Null as well?

Correct. Anything plus null is still null, this applies to all types.

Quote
> If so, how do I
> avoid this?  I want to be able to add all the fields in there, just in

case.

Depends on the database you are using. Some provide a function to return the
actual value of a column or 0 of null. Others so not, in which case you need
to either default that field to 0 instead of null, or use a stored procedure
or possibly a view to return 0 instead of null.

--
Wayne Niddery (Logic Fundamentals, Inc.)
Those who disdain wealth as a worthy goal for an individual or a society
seem not to realize that wealth is the only thing that can prevent
poverty. - Thomas Sowell

Re:Need SQL Statement Help


You could try using the COALESCE operator:
SELECT S.Name, Sum(E."Score 1" + E."Score 2" + E."Score 3" + COALESCE
(E."Score 4", 0))
--
Finn Tolderlund

"Anchorman" <Je...@davinci-mims.com> skrev i en meddelelse
news:3cb1f311$1_2@dnews...

Quote
> However, if I add E."Score 4" to the mix like this:
> SELECT S.Name, Sum(E."Score 1" + E."Score 2" + E."Score 3" + E."Score 4")
as
> TotScore returns null for every field.  I'm assuming that this is because
> there is no #."Score 4" field that has a value.

Re:Need SQL Statement Help


Sorry, I just keep forgetting to post which database I am using.  I am using
a paradox database.  Is it possible to have it call it a 0 if it's a null
with a Paradox database?

Jesse

Re:Need SQL Statement Help


I'm using Paradox as my database, and I tried this.  Unfortunately, I get a
"cabability not supported" error.  Is there some other way to do this with a
Paradox database?

Jesse

Quote
"Finn Tolderlund" <XnospamYfi...@Ymail.tdcadsl.dkXnospamY> wrote in message

news:3cb20773$1_2@dnews...
Quote
> You could try using the COALESCE operator:
> SELECT S.Name, Sum(E."Score 1" + E."Score 2" + E."Score 3" + COALESCE
> (E."Score 4", 0))
> --
> Finn Tolderlund

Re:Need SQL Statement Help


Local SQL (the dialect used by Paradox tables) does not support COALESCE.
The best solution is to default Score 4 to zero so there will be no null
values.

--
Bill
(TeamB cannot answer questions received via email)

Other Threads