ADO and Where ((table1.colum1x <> table2.column2x) is Null)

[ak_ls]

I have to migrate DAO queries to ADO. I am having prolem in finding the equivalent of the following which is frequently used in WHERE clause.

Select .....

From table1 left inner join table2 on table1.colum1x = table2.column2x and ...

Where ((table1.colum1x <> table2.column2x) is Null) or ....

The syntex ((table1.colum1x <> table2.column2x) is Null) does not work in ADO. Though the following works

table1.colum1x is Null

Can somebody tell me what is the problem here and suggest me the equivalent of ((table1.colum1x <> table2.column2x) is Null) in ADO.

Ashok

 

[Premy]

Where ((table1.colum1x <> table2.column2x) is Null) or ....

Does this actually work in DAO ?

 

[LPurvis]

It would execute at least... ;-)
Should execute in ADO too looking at it.

I'm guessing you've abstracted your actual query into some example aircode for this question. (Soooo often that's a bad idea - you can make errors in doing so, or leave something out - or overlook an issue that's actually important/vital to the problem).

In this case - you've refered to a LEFT INNER join - which, of course, doesn't exist (hence your query wouldn't even parse in ADO, DAO or Access).
But setting that side (and assuming you mean LEFT OUTER) then your test is basically looking for instances where table2.column2x returns no match or table1.colum1x is Null).

(Because the Join doesn't require that a value is in that field - but to then compare with not equals <> and compare to Null will only be true if one or both compared fields are Null.)
There are more explicit ways of requesting the same set of records.

So - what is your ultimate aim here.
Because executing the request via ADO doesn't seem relevant based upon what you've provided.

 

[jal]

Yes, if you can tell us what your query is trying to do, in plain English, instead of in weird queries, maybe we can tell you how to do it in ADO.

 

[ak_ls]

Purvis Thanks for correcting. It is infact LEFT JOIN.

I have to convert an Access 2003 mdb project to adp /SQL Server 2005. This project does not have any documentation, it is not clear to me what each such query is intended for.

There are many queries that have like ' Where (C1<>C2) Is Null or ...' type syntex. These queries could not be upsized or created afresh using same syntex to views of SQL Server 2005.

Access migration assistant could upsize the query but changed '(C1<>C2) Is Null' to 'Case When ... Else ..End' type and it does not produce current result.

I could not succeed creating these views using SQL Server 2005 Express Management Studio also.

To understand the error, I have also tried to execute the simple Select statements like the following but it does not work in ADO. The Error message displayed says 'Error near key word 'IS'.

Set RS = CurrentProject.Connection.Execute ("Select x1,y1,z1 where (x1<>Y1) is Null",,adCmdText)

The following is a real example that I am reproducing without any change.

SELECT ChangeStarteam.Programme, ChangeStarteam.[Report Date], ChangeStarteam.[XT Ref], ChangeStarteam.[CR Number], ChangeStarteam.[UHO CFH National Ref], ChangeStarteam.Status, ChangeStarteam.[Current Release Display], ChangeStarteam.Synopsis, ChangeStarteam.Responsibility, ChangeStarteam.[Modified Time], ChangeStarteam.[Entered By], ChangeStarteam.[Entered On], ChangeStarteam.[ROM Costs], ChangeStarteam.[Cost GBP], ChangeStarteam.[Price GBP], ChangeStarteam.[Review Status], ChangeStarteam.[CYA Complete Date], ChangeStarteam.[CYA Issued], ChangeStarteam.[CYA Response Date], ChangeStarteam.[CYA Response Date], ChangeStarteam.[CYA Response Due], ChangeStarteam.[Raised Date], ChangeStarteam.[Initiating Org (Display)], ChangeStarteam.[Action Owner]

FROM ChangeStarteam LEFT JOIN qChangeStarteamTrackerLatest ON ChangeStarteam.[XT Ref] = qChangeStarteamTrackerLatest.[XT Ref]

WHERE ((([ChangeStarteam].[CR Number]<>[qChangeStarteamTrackerLatest].[CR Number]) Is Null)) OR ((([ChangeStarteam].[Status]<>[qChangeStarteamTrackerLatest].[Status])=True)) OR ((([ChangeStarteam].[Responsibility]<>[qChangeStarteamTrackerLatest].[Responsibility])=True)) OR ((([ChangeStarteam].[ROM Costs]<>[qChangeStarteamTrackerLatest].[ROM Costs])=True)) OR ((([ChangeStarteam].[Cost GBP]<>[qChangeStarteamTrackerLatest].[Cost(GBP)])=True)) OR ((([ChangeStarteam].[Price GBP]<>[qChangeStarteamTrackerLatest].[Price(GBP)])=True)) OR ((([ChangeStarteam].[Review Status]<>[qChangeStarteamTrackerLatest].[Review Status])=True));

 

[jal]

I ran a little test where an ADO recordset seemed to be accepting that syntax. Therefore I think you might be migrating incorrectly.

Perchance, did you forget to include the ON clause? If you include the WHERE clause, but omit the ON clause, in a LEFT JOIN, you'll probably get a a syntax error.


Here's the details of what I did:

Imagine a table that has column1 (c1) and column2 (c2). Let's suppose c1 is fully populated whereas c2 is has nulls all the way down the column. If you query:

select count(*)
from table1
where c1 <> c2

If I paste this into Access SQL view, the result is a count of zero. I'm guessing this is because, in the case of a null value, the condition

c1 <> c2

returns neither false nor true but null. A WHERE clause is seeking a True or False but in this case it finds neither so it is never satsified. Hence the count is 0. In a sense, then, this type of query can serve as a backhanded method of detecting cases where either c1 or c2 is null, if we rewrite it like this:

select *
from table1
where (c1 <> c2) is null

I tried the above on both a DAO.Recordset and an ADODB.Recorset. I had 5 records in my table1 and so the query returned five records, in both cases.

Next, I separated c1 and c2. Let's call them R1 and L1 (right and left) and put them in separate tables R and L. Thus we have two columns (well, I added an ID column)

R.R1
L.L1

I then ran the following query in ADO and it returned five records:

Dim rs As New ADODB.Recordset
Call rs.Open("select * from L Left Join R on L.ID = R.ID where (L.L1 <> R.R1) is null", CurrentProject.Connection)


Don't know if any of this will help you. Just trying to get this discussion going.

 

[jal]

Just occurred to me - regarding:

where (x1<>Y1) is Null

By any chance, does it happen to be the case that x1 and y1, prior to the join, are columns with no nulls? If so, there's probably a simpler way to write this query.

 

[jal]

I put some more thought into this. As I surmised above, the WHERE clause in

Select *
from Table1
where (c1 <> c2) is null

returns true, and thus returns the record, if either column1 or column2 is null. So I think we could rewrite this as :

Select *
from Table1
where c1 is null or c2 is null

Let's consider again the 2-table query.

select * from L
Left Join R
on L.ID = R.ID
where (L.L1 <> R.R1) is null

It seems to me we could do the same thing here, that is, rewrite it as:

WHERE (L.L1 is null or R.R1 is null)

After all, the JOIN executes first, resulting in an initial table, and then the WHERE clause is applied to the one table. In other words we seem to have the same one-table scenario here, ultimately, as above.

Guys, am I right about this?

 

[LPurvis]

OK - very briefly... (well - probably as brief as I ever manage to be - which ain't too brief unfortunately).

Were the fields in the criteria the same as those in the join as had been initially speculated the comparison itself would be relatively redundant - it's then all about checking for Nulls as I mentioned in my earlier post.

However looking at your actual query as posted the join is distinct from the criteria fileds. Indeed - the Is Null comparison is only present on one clause and IMO it's protecting against Nulls - rather than looking for them.
Your query looks very much QBE generated. The abundance of redundant brackets implies this.

Let's consider
WHERE ((([ChangeStarteam].[CR Number]<>[qChangeStarteamTrackerLatest].[CR Number]) Is Null)) OR ((([ChangeStarteam].[Status]<>[qChangeStarteamTrackerLatest].[Status])=True))

Smacks of the QBE - when in fact you're just wanting to find rows where [CR Number] in the respective tables are different. (Or either is Null - preserving the Outer Join).
This would probably more normally be written as

WHERE ([ChangeStarteam].[CR Number]<>[qChangeStarteamTrackerLatest].[CR Number] OR [ChangeStarteam].[Status] Is Null OR [qChangeStarteamTrackerLatest].[Status] Is Null)

OK - using the comparison cheat works and is less typing.
FieldA <> FieldB Is Null
is less text than
FieldA Is Null OR FieldB Is Null
However in the former you're relying on an Expression to be evaluated, returning Null (which is will if either field value is Null) and comparing against the Is Null clause.

So?
I'd say that the problem you're experiencing has nothing to do with ADO.
As often mentioned - the OLEDB provider for Jet supports a majority of functionality exposed by both DAO and Access' exposure to Jet, but with substantial other available syntaxes.
But, vitally, you mentioned earlier than you're moving to an ADP.
The true difference in an ADP is not that of using ADO to expose your data (that's entirely possible and indeed very common in an MDB/ACCDB) - but that your BE is SQL Server.
T-SQL will not evaluate expressions implicitly for comparison as Jet does. (Jet has its expression service to perform that role).
In T-SQL you have to perform Case statements to return expression values for subsequent comparison.
Or - just use the explicit form of the SQL instead (OR clauses). That is pretty much standard and should work just fine under both Jet and SQL Server (using ADO or not).

Cheers.

 

[ak_ls]

Purvis / jal

Thanks for the helpful explanations.

I got it now. It's the T-SQL and not the ADO.

Today, I have also tried on similar lines and was able to make one query work. However, It has created additional work for me as now I have to examine each query and make modifications if required.

Thanks once more for helping.
Ashok