[RESOLVED] Equivalent of Nz() Function

[Shaggy Hiker]

I have query that includes this in the SELECT statement (as one of many fields):

IIF(STRM_Survey.flgNoSurvey = 0,Nz((SELECT SUM(CountNew) FROM STRM_Redds WHERE STRM_Redds.SurveyID = STRM_Survey.SurveyID),0),Null)  AS NewRedds

That works in Access, but not in ADO.NET, because Nz is not recognized in ADO.NET. In SQL Server, I would use IsNull rather than Nz, but IsNull is a recognized function in Access, and it doesn't do the same thing as it does in SQL Server.

I realize that I could write this as an utterly ugly IIF within IIF, but it seems like I would have to write it like this:

IIF(STRM_Survey.flgNoSurvey = 0,IIF(ISNull(SELECT SUM(CountNew) FROM STRM_Redds WHERE STRM_Redds.SurveyID = STRM_Survey.SurveyID),0),SELECT SUM(CountNew) FROM STRM_Redds WHERE STRM_Redds.SurveyID = STRM_Survey.SurveyID)  AS NewRedds

which seems like a massive waste of time, as it runs a sub-query twice. Therefore, I'm looking for a replacement for Nz that will work in ADO.NET. Is there such a thing?

I do have an alternative, in this case, but it is probably worse than the double sub-query.

[techgnome]

Would COALESCE work?

-tg

[Shaggy Hiker]

Nope, Coalesce isn't a recognized function.

What's annoying is that Nz is recognized in Access, but apparently not in OLEDB ADO.Net, while IsNull is used in the fashion that it works in Access, but not in the fashion it is used in SQL Server. Meanwhile, Coalesce isn't there at all.

[Zvoni]

Eliminate the Nz...

IIF(STRM_Survey.flgNoSurvey = 0,
  (SELECT SUM(
                     IIF(CountNew IS NULL, 0, CountNew)
                    ) 
   FROM STRM_Redds WHERE STRM_Redds.SurveyID = STRM_Survey.SurveyID)
,Null)  AS NewRedds

That way, the most inner IIF (3rd line) will never return a NULL, meaning the SUM will always return at least a 0

[Shaggy Hiker]

Yeah, that was it. It sure helps if you can type.

At first, it wasn't working, but it was because I wrote IFF rather than IIF. All that did was give a syntax error without anything more helpful, such as noting an unrecognized function. It took a while to track that down.

[Shaggy Hiker]

I guess I need to unresolve this.

The query runs, it just doesn't work, which kind of has me baffled. What Zvoni wrote, runs without issue (though it did reveal that the first IIF is actually wrong, but that's an internal process issue), but Nulls are still Null.

The theory is sound: SUM the result of the IIF, which will either produce 0, or some other integer, so the SUM of those integers should always be an integer. But in practice, if there is a Null in that inner IIF, the result of the SUM is still Null.

Since I've utterly botched copy and pastes before, here's the actual line that is still returning Null when CountNew is Null:

 IIF(STRM_Survey.flgNoSurvey = 0, (SELECT SUM(IIf (CountNew IS NULL, 0, CountNew)) FROM STRM_Redds WHERE STRM_Redds.SurveyID = STRM_Survey.SurveyID),Null) AS NewRedds,

[grindmore]

Instead of nested IIFs, try using COALESCE() - it returns the first non-null value. Or handle the null check in your C# code. Either should work in ADO.NET.

[techgnome]

Instead of nested IIFs, try using COALESCE() - it returns the first non-null value. Or handle the null check in your C# code. Either should work in ADO.NET.

That's what I thought...

but...

Nope, Coalesce isn't a recognized function.

What's annoying is that Nz is recognized in Access, but apparently not in OLEDB ADO.Net, while IsNull is used in the fashion that it works in Access, but not in the fashion it is used in SQL Server. Meanwhile, Coalesce isn't there at all.

Which I don't get because it shoul;d be a SQL function...

[Zvoni]

That's what I thought...

but...

Which I don't get because it shoul;d be a SQL function...

but not in Access.
REPLACE is another which Access only knows from „inside“ Access
has driven me barmy in the past

[Shaggy Hiker]

Yeah, it's doing that. That inner IIF SHOULD work.
I tried a variant on that, which looked like this:

IIf (CountNew IS NULL,0, 2)

Since CountNew is almost always going to be 1, this should have essentially doubled CountNew, which is what happened, but I still got Nulls where CountNew was Null. It appears that the Access SQL engine has decided, "it's Null, I don't care what you do with it, the result is Null." It feels like a shortcut. Stop processing on Nulls, or something like that.

[Zvoni]

What about a default value of 0 for CountNew?

EDIT: i did try out above most inner Iif in Access, sooooo: are those „real“ NULLs?

[Shaggy Hiker]

No, actually they are not...sort of, and that might offer up a different solution. The CountNew field is non-nullable, and often is 0. However, the record may not exist. CountNew is in a child table left outer joined to a parent for the sake of this query. The bulk of the fields in the query come from the parent, with a sum of CountNew from the child. There may be no children, though, so when CountNew is Null, it's because there are no children to sum up. Is there a different approach that would work in that case?


There will be no children if flgNoSurvey is True and there MIGHT be no children if flgNoSurvey is false. If flgNoSurvey is True, then we want Null in that field. If flgNoSurvey is false, but there STILL are no children, then we want 0.

[Zvoni]

Replace the inner Iif with „0+CountNew“?
just brainstorming here….

EDIT: Forget it. SomeThing plus NULL still returns NULL

[Zvoni]

btw: If everything fails, and you don't want to "fork" your Client-Code (Checking if the backend is Access or MSSQL),
an alternative might be to store the specific SQL-Statement within the Database itself in a Simpletext-Column in a separate Table.

From your client, you just pull that Statement from the Backend, assign it as the "real" statement, and off you go (setting Parameters not withstanding)
(It's my "Go-To"-Technique if i have to support different SQL-Syntaxes)

In your case, in Access you would store the Query with the NZ-Function, in MSSQL the "version" with COALESCE (or whatever function you use) in its respective Table.
From the client, the SQL to pull it out is the same for both backends:
SELECT Statement FROM tbl_SQL_Statements WHERE StatementID=3
(and StatementID is a basically a constant, which value must be the same in both backends, so a Primary Key but not AutoInc)

[Zvoni]

AHA!
Had the same phenomenon like you, and your "LEFT JOIN" was the hint i was searching for

This works in Access:

SELECT T1.SomeTextField, T1.SomeIntField, IIF(T2.CN IS NULL, 0, T2.CN) AS CN2 FROM table1 AS T1
LEFT JOIN (
SELECT FK, SUM(CountNew) AS CN FROM table2 GROUP BY FK) AS T2 
ON T1.ID=T2.FK

FK in T2 is the Foreign Key to PK in T1.

Since you said, that CountNew is NON NULL, the second/inner SELECT within the LEFT JOIN will never return a NULL for the SUM.
Meaning: We have to catch the NULL AFTER LEFT JOIN-ing

EDIT: That said, i think i have to rework my first attempt above to

IIF(STRM_Survey.flgNoSurvey = 0,
  (SELECT IIF(
            SUM(CountNew) IS NULL, 0, SUM(CountNew)
                    ) 
   FROM STRM_Redds WHERE STRM_Redds.SurveyID = STRM_Survey.SurveyID)
,Null)  AS NewRedds

which looks "redundant", but apparanetly works at least in Access
(I just tried it with your "version" of a LEFT JOIN)

[Shaggy Hiker]

That got it. Very interesting. I would not have thought of checking whether the Sum of CountNew Is Null. The more I think about it, the more I find it almost arbitrary.

[Zvoni]

The more I think about it, the more I find it almost arbitrary.

Not really if you think about it.

Remember: in a Query a Filter is applied first, THEN any functions on Fields.
Meaning in your case: All records get filtered FIRST according to the inner WHERE-Clause, which you pass the outer ID to.

Since you stated that CountNew is NON NULL, any records where you do have a match, WILL return an integer for CountNew
The opposite is also true: where there is no match, you get NOTHING (as in: RecordCount=0), and NOTHING is not NULL.

So there is NOTHING you could actually compare if it's NULL.
That's why my first approach failed.
I also expected CountNew itself to be NULL, which is wrong. It was NOTHING (as in: RecordCount=0)
BUT: The SUM of NOTHING is NULL

btw: It's also exactly the reason, why i don't like the "version" of your LEFT JOIN.
I'd probably go with my "true" LEFT JOIN (see above). Then it's clear what happens where.

Nevermind Performance-Reasons.
For an unfathomable reason, i always think your inner SELECT gets executed as many times there are ID's "injected" from outside.
So 1000 ID's from outside = inner SELECT gets executed 1000 times.

But i might be completely wrong here (i never look at execution-plans, i always look on my clock)

[Shaggy Hiker]

Ah, I get it. I was thinking that the Nothing WAS the same as Null, but Nothing only resolves to Null. If I query for just the field, I get Nothing, but that is displayed as Null. The SUM() forced the Nothing to resolve for the sake of the operation.