query that will return null values to 0

[enrico via DotNetMonster.com]

i'm using mySQL as my backend to my program. i know this is not VB.NET
anymore but i don't know how to do it. i have a query that will calculate if
it satisfies the condition. it goes like this:

SELECT
(tblseedsubsidy .NoOfBags)
FROM
agri.tbltrcclie nt
INNER JOIN agri.tblseedsub sidy
ON (tbltrcclient.C lientID = tblseedsubsidy. ClientID)
INNER JOIN agri.tblinvento ry
ON (tblseedsubsidy .SeedID = tblinventory.Se edID)
WHERE (tblseedsubsidy .CoOwner = '1'
AND tbltrcclient.Ty peofSeed = 'Corn')
HAVING (sum(tblseedsub sidy.NoOfBags) /2);

but every time it doesn't satisfy the condition where CoOwner must be equal
to '1' it returns a null value. how can i convert the null value into 0?

if anyone has any idea on how to do it i would really appreciate it. thanks.

--
Message posted via DotNetMonster.c om

http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-vb-net/200805/1

[Cor Ligthert[MVP]]

Re: query that will return null values to 0

Enrico as you said, nothing to do with VB, however in SQL script it is

(.............. Or tblseedsubsidy. CoOwner Is Null)

Cor

"enrico via DotNetMonster.c om" <u41845@uweschr eef in bericht
news:83ae12e7b0 23d@uwe...

i'm using mySQL as my backend to my program. i know this is not VB.NET
anymore but i don't know how to do it. i have a query that will calculate
if
it satisfies the condition. it goes like this:
>
SELECT
(tblseedsubsidy .NoOfBags)
FROM
agri.tbltrcclie nt
INNER JOIN agri.tblseedsub sidy
ON (tbltrcclient.C lientID = tblseedsubsidy. ClientID)
INNER JOIN agri.tblinvento ry
ON (tblseedsubsidy .SeedID = tblinventory.Se edID)
WHERE (tblseedsubsidy .CoOwner = '1'
AND tbltrcclient.Ty peofSeed = 'Corn')
HAVING (sum(tblseedsub sidy.NoOfBags) /2);
>
but every time it doesn't satisfy the condition where CoOwner must be
equal
to '1' it returns a null value. how can i convert the null value into 0?
>
if anyone has any idea on how to do it i would really appreciate it.
thanks.
>
--
Message posted via DotNetMonster.c om

http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-vb-net/200805/1

>

[Michel Posseth [MCP]]

Re: query that will return null values to 0

Hello Enrico ,

this should do the trick

SELECT
ISNULL(tblseeds ubsidy.NoOfBags ,0) AS NoOfBags
FROM
agri.tbltrcclie nt
INNER JOIN agri.tblseedsub sidy
ON (tbltrcclient.C lientID = tblseedsubsidy. ClientID)
INNER JOIN agri.tblinvento ry
ON (tblseedsubsidy .SeedID = tblinventory.Se edID)
WHERE (tblseedsubsidy .CoOwner = '1'
AND tbltrcclient.Ty peofSeed = 'Corn')
HAVING (sum(tblseedsub sidy.NoOfBags) /2);


As you did not provide the data structure i asume that
tblseedsubsidy. NoOfBags is a numericdatatype field otherwise change above
to
SELECT
ISNULL(tblseeds ubsidy.NoOfBags ,'0') AS NoOfBags
............... ............... ......

HTH

Michel Posseth



"enrico via DotNetMonster.c om" <u41845@uweschr eef in bericht
news:83ae12e7b0 23d@uwe...

i'm using mySQL as my backend to my program. i know this is not VB.NET
anymore but i don't know how to do it. i have a query that will calculate
if
it satisfies the condition. it goes like this:
>
SELECT
(tblseedsubsidy .NoOfBags)
FROM
agri.tbltrcclie nt
INNER JOIN agri.tblseedsub sidy
ON (tbltrcclient.C lientID = tblseedsubsidy. ClientID)
INNER JOIN agri.tblinvento ry
ON (tblseedsubsidy .SeedID = tblinventory.Se edID)
WHERE (tblseedsubsidy .CoOwner = '1'
AND tbltrcclient.Ty peofSeed = 'Corn')
HAVING (sum(tblseedsub sidy.NoOfBags) /2);
>
but every time it doesn't satisfy the condition where CoOwner must be
equal
to '1' it returns a null value. how can i convert the null value into 0?
>
if anyone has any idea on how to do it i would really appreciate it.
thanks.
>
--
Message posted via DotNetMonster.c om

http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-vb-net/200805/1

>

[enrico via DotNetMonster.com]

Re: query that will return null values to 0

it still doesn't work. it prompts an error something like "Incorrect
parameter count...". is it possible that it doesn't recognize the isnull
statement?

--
Message posted via DotNetMonster.c om

http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-vb-net/200805/1

[Steve Gerrard]

Re: query that will return null values to 0

enrico via DotNetMonster.c om wrote:

it still doesn't work. it prompts an error something like "Incorrect
parameter count...". is it possible that it doesn't recognize the
isnull statement?

What does

HAVING (sum(tblseedsub sidy.NoOfBags) /2)

mean?

[enrico via DotNetMonster.com]

Re: query that will return null values to 0

i use sqlyog to test my query. i just drop and drag my fields and put some
criteria on what i want my query is to be, and it gave a "having" statement.
i didn't care at all because my query worked.

--
Message posted via http://www.dotnetmonster.com

[Steve Gerrard]

Re: query that will return null values to 0

enrico via DotNetMonster.c om wrote:

i use sqlyog to test my query. i just drop and drag my fields and put
some criteria on what i want my query is to be, and it gave a
"having" statement. i didn't care at all because my query worked.

I just thought it was unusual to say
HAVING (sum(tblseedsub sidy.NoOfBags) /2)

Normally criteria include some sort of comparision, such as
sum(tblseedsubs idy.NoOfBags) /2 0

[=?Utf-8?B?TWljaGVsIFBvc3NldGggW01DUF0=?=]

Re: query that will return null values to 0

1000 times sorry :-) i missed this
"i'm using mySQL as..........."

The SQL i Showed that would have solved the problem is in MSSQL dialect

MYSQL however uses a slightly different dialect

the solution for MYSQL would be


SELECT
IFNULL(tblseeds ubsidy.NoOfBags ,0) AS NoOfBags
FROM
agri.tbltrcclie nt
INNER JOIN agri.tblseedsub sidy
ON (tbltrcclient.C lientID = tblseedsubsidy. ClientID)
INNER JOIN agri.tblinvento ry
ON (tblseedsubsidy .SeedID = tblinventory.Se edID)
WHERE (tblseedsubsidy .CoOwner = '1'
AND tbltrcclient.Ty peofSeed = 'Corn')
HAVING (sum(tblseedsub sidy.NoOfBags) /2);

HTH

And again sorry for missing the MYSQL part

Regards

Michel Posseth [MCP]







"enrico via DotNetMonster.c om" wrote:

it still doesn't work. it prompts an error something like "Incorrect
parameter count...". is it possible that it doesn't recognize the isnull
statement?
>
--
Message posted via DotNetMonster.c om

http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-vb-net/200805/1

>
>