Returning NULL Values

[Sparky Arbuckle]

I am working on a web application for a company that uses an existing
company database. I just found out before the long weekend that a
couple hundred of the items in the table aren't completely related.

I am mostly using 2 tables for the project: ItemVersion, ItemVersionRev

ItemVersion has fields: ItemVersion, ItemNumber, RecordID
ItemVersionRev has fields: RecordID, RevNumber

All of the items in the database have ItemNumbers (obviously) and most
have revision numbers (RevNumber). One of the functions that I created
for my application finds the MaxOfRevNumber. I ran a query on the
database and saw that dreaded error screen that read "There is no row
at position 0"

The query that I have now simply returns MaxOfRevNumber. Is there a way
to program a query to check and see if an ItemNumber has a Revision and
depending on the result, return a certain value. For example, if there
is not a corresponding RevNumber, then return 0 as the RevNumber?

I'm sorry for the long post and if needed can clarify more.

 

[Brendan Reynolds]

Something like this should do it (substitute your table and field names,
obviously) ...

SELECT Max(IIf([testnum] Is Null,0,[testnum])) AS Expr1
FROM tblTest;

 

[Sparky Arbuckle]

Thanks Brendan. What if the query is more complex?

SELECT TOP 1 Sheet.SheetType, IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN]) as
Expr1 " & _
"FROM ADCN INNER JOIN Sheet ON ADCN.RecordID = Sheet.RecordID " & _
"WHERE (Sheet.Drawing = '" & x & "') AND (Sheet.SheetNumber = 0) " & _
"ORDER BY ADCN.ReleaseDate DESC;"

I still don't get anything returned.

 

[Sparky Arbuckle]

Is there a way to alter the above query if that field doesn't even
exist? Re-designing the database isn't an option now because of the
31,000+ records.

 

[Sparky Arbuckle]

Thanks Brendan. How would I go about trying to add that to a more
complex query such as:

SELECT TOP 1 Sheet.SheetType, ADCN.ADCN FROM " & _
"ADCN INNER JOIN Sheet ON ADCN.RecordID = Sheet.RecordID " & _
"WHERE (Sheet.Drawing = '" & x & "') AND (Sheet.SheetNumber = 0) " & _
"ORDER BY ADCN.ReleaseDate DESC;

ADCN is the field name in question.

 

[Brendan Reynolds]

There are a few reasons why this query might return no records. As far as I
can see, whether ADCN is Null or not is not one of them. Examine the inner
join - do you need an outer join instead? What about the criteria - are you
sure that there are records that match both criteria?

 

[Brendan Reynolds]

If a field doesn't exist, then I would have thought that the way to fix the
query was to take the reference to the non-existent field out of the query -
or am I missing something?

 

[Sparky Arbuckle]

Brendan, thanks for all your help today. I have learned a lot stemming
from researching the IIf() function.

My problem in a nutshell is this. Some Items have RevNumbers and some
do not. Instead of trying to figure out which ones do and don't have
revnumbers is not an option because there are 31,000+ records. I want
to use one query for this function and if there are no revnumbers for a
particular item I want to assign a value of 0 to it.

The reason why I need one SQL statement is because I am using an
ASP.NET datagrid to display 4-5 items at a time. If one of these items
doesn't have a RevNumber then the whole page errors out.

I hope this helps.