query that will return null values to 0
in [VB.NET]
|
Prev: PrintDialog returns always cancel
Next: The application requires that assembly office Version 12.0.0.0 be installed in the Global Assembly Cache (GAC) first.
From: enrico via DotNetMonster.com on 5 May 2008 00:17 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.tbltrcclient INNER JOIN agri.tblseedsubsidy ON (tbltrcclient.ClientID = tblseedsubsidy.ClientID) INNER JOIN agri.tblinventory ON (tblseedsubsidy.SeedID = tblinventory.SeedID) WHERE (tblseedsubsidy.CoOwner = '1' AND tbltrcclient.TypeofSeed = 'Corn') HAVING (sum(tblseedsubsidy.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.com http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-vb-net/200805/1
From: Cor Ligthert[MVP] on 5 May 2008 01:13 Enrico as you said, nothing to do with VB, however in SQL script it is (..............Or tblseedsubsidy.CoOwner Is Null) Cor "enrico via DotNetMonster.com" <u41845(a)uwe> schreef in bericht news:83ae12e7b023d(a)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.tbltrcclient > INNER JOIN agri.tblseedsubsidy > ON (tbltrcclient.ClientID = tblseedsubsidy.ClientID) > INNER JOIN agri.tblinventory > ON (tblseedsubsidy.SeedID = tblinventory.SeedID) > WHERE (tblseedsubsidy.CoOwner = '1' > AND tbltrcclient.TypeofSeed = 'Corn') > HAVING (sum(tblseedsubsidy.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.com > http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-vb-net/200805/1 >
From: Michel Posseth [MCP] on 5 May 2008 01:30 Hello Enrico , this should do the trick SELECT ISNULL(tblseedsubsidy.NoOfBags,0) AS NoOfBags FROM agri.tbltrcclient INNER JOIN agri.tblseedsubsidy ON (tbltrcclient.ClientID = tblseedsubsidy.ClientID) INNER JOIN agri.tblinventory ON (tblseedsubsidy.SeedID = tblinventory.SeedID) WHERE (tblseedsubsidy.CoOwner = '1' AND tbltrcclient.TypeofSeed = 'Corn') HAVING (sum(tblseedsubsidy.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(tblseedsubsidy.NoOfBags,'0') AS NoOfBags .................................... HTH Michel Posseth "enrico via DotNetMonster.com" <u41845(a)uwe> schreef in bericht news:83ae12e7b023d(a)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.tbltrcclient > INNER JOIN agri.tblseedsubsidy > ON (tbltrcclient.ClientID = tblseedsubsidy.ClientID) > INNER JOIN agri.tblinventory > ON (tblseedsubsidy.SeedID = tblinventory.SeedID) > WHERE (tblseedsubsidy.CoOwner = '1' > AND tbltrcclient.TypeofSeed = 'Corn') > HAVING (sum(tblseedsubsidy.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.com > http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-vb-net/200805/1 >
From: enrico via DotNetMonster.com on 6 May 2008 20:17 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.com http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-vb-net/200805/1
From: Steve Gerrard on 6 May 2008 22:57
enrico via DotNetMonster.com 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(tblseedsubsidy.NoOfBags) /2) mean? |