Returning Null Values

I am working on queries for a SQL database. I have much experience with
Access and am trying to ween myself off of it. The query I am working
on now is supposed to return a set of data like the following:
LatestVersion(Ver)/VersionUpdate(Update)/Location(SheetNumber)
A/aa1/3
This works fine except for some of the products in the database don't
have these values attributed to them. I am using the following SQL
query:
SELECT Max(COALESCE(Sheet.Ver, 'NONE')) AS MaxOfVer,
Max(COALESCE(Update.Update, 0)) AS MaxOfUpdate, Sheet.SheetNumber FROM
Drawings INNER JOIN (Update INNER JOIN
Sheet ON Update.RecordID = Sheet.RecordID) ON Drawings.Drawing =
Sheet.Drawing WHERE (Sheet.Drawing = 'XXXXX') AND
(Sheet.SheetType = 'SH') GROUP BY Sheet.Drawing, Sheet.SheetType,
Sheet.SheetNumber
How can I edit this string to return either all 3 of the fields I need,
or return all of 3 of the fields with value = NONE if there are no
results. Is it possible to wrap a COALESCE around the whole query
string? How do I check to see if query results actually exist?
*** Sent via Developersdex http://www.examnotes.net ***Put the MAX() inside the COALESCE(). iow, perform the coalesce after
determining the max.
Will Chamberlain wrote:
> I am working on queries for a SQL database. I have much experience with
> Access and am trying to ween myself off of it. The query I am working
> on now is supposed to return a set of data like the following:
> LatestVersion(Ver)/VersionUpdate(Update)/Location(SheetNumber)
> A/aa1/3
> This works fine except for some of the products in the database don't
> have these values attributed to them. I am using the following SQL
> query:
> SELECT Max(COALESCE(Sheet.Ver, 'NONE')) AS MaxOfVer,
> Max(COALESCE(Update.Update, 0)) AS MaxOfUpdate, Sheet.SheetNumber FROM
> Drawings INNER JOIN (Update INNER JOIN
> Sheet ON Update.RecordID = Sheet.RecordID) ON Drawings.Drawing =
> Sheet.Drawing WHERE (Sheet.Drawing = 'XXXXX') AND
> (Sheet.SheetType = 'SH') GROUP BY Sheet.Drawing, Sheet.SheetType,
> Sheet.SheetNumber
>
> How can I edit this string to return either all 3 of the fields I need,
> or return all of 3 of the fields with value = NONE if there are no
> results. Is it possible to wrap a COALESCE around the whole query
> string? How do I check to see if query results actually exist?
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Trey. I got the first part to work but am having trouble with the
next query. Sometimes there are values and other times I am getting
NULL. This is extremely frustrating because I am getting 'Cannot Cast
from DBNull to Type String' errors in my .NET application. With the
query below; what would be my best avenue for fixing this situation from
the SQL standpoint? I tried to SELECT MAX then do a COALESCE but I am
still getting errors.
SELECT MAX(COALESCE(a.ADCN, 0)) AS ADCN FROM ADCN a INNER JOIN Sheet s
ON a.RecordID = s.RecordID WHERE s.Drawing = 'XXXX' AND s.SheetNumber =
0
Programmatically I have tried something along the lines of:
If rowCount = 0 Then
..
and
If Convert.IsDBNull(ds.tables...) = True Then
..
Both of these avenues have worked with no such luck.
*** Sent via Developersdex http://www.examnotes.net ***|||MAX inside COALESCE -- e.g.
SELECT COALESCE(MAX(a.ADCN), 0) AS ADCN, etc.
Will Chamberlain wrote:
> Thanks Trey. I got the first part to work but am having trouble with the
> next query. Sometimes there are values and other times I am getting
> NULL. This is extremely frustrating because I am getting 'Cannot Cast
> from DBNull to Type String' errors in my .NET application. With the
> query below; what would be my best avenue for fixing this situation from
> the SQL standpoint? I tried to SELECT MAX then do a COALESCE but I am
> still getting errors.
> SELECT MAX(COALESCE(a.ADCN, 0)) AS ADCN FROM ADCN a INNER JOIN Sheet s
> ON a.RecordID = s.RecordID WHERE s.Drawing = 'XXXX' AND s.SheetNumber =
> 0
> Programmatically I have tried something along the lines of:
> If rowCount = 0 Then
> ..
> and
> If Convert.IsDBNull(ds.tables...) = True Then
> ..
> Both of these avenues have worked with no such luck.
> *** Sent via Developersdex http://www.examnotes.net ***