SQL on linked table vs SQL pass through (1 Viewer)

[darbid]

Hi everyone,

I have 2 SQLs which unless I am mistaken are identical. The difference is that from The Access Queries Objects 2003 I have one executing on the table LINKED, the second is a pass through directly to sql server.

What I do not understand is why I am getting different results. Could someone please help.

PASS_THROUGH

SELECT *
FROM tbl_questionnaire
WHERE q_decision_date Between '1/1/2011' And '3/31/2011' 
And (q_eval_reason Like '%xx%' Or q_eval_reason Like '%[?]%' Or q_eval_reason Like '% no %' Or q_eval_reason Like '%kein%' Or q_eval_reason Like '%nicht%' 
Or len(q_eval_reason)<11);

LINKED TABLE

SELECT *
FROM tbl_questionnaire
WHERE q_decision_date Between #1/1/2011# And #3/31/2011# 
And (q_eval_reason Like '*xx*' Or q_eval_reason Like '*[?]*' Or q_eval_reason Like '* no *' Or q_eval_reason Like '*kein*' Or q_eval_reason Like '*nicht*' 
Or len(q_eval_reason)<11);

 

[pbaldy]

My guess, and that's all it is, is that the ? is the problem. That's a wildcard in Access, but the equivalent in SQL Server is an underscore. If memory serves, they also handle the brackets differently. Is there a pattern to the different results?

 

[darbid]

Is there a pattern to the different results?

The pattern is that the linked table query produces much less. The only real pattern I can see is that it is not returning all the ones where len(q_eval_reason)<11.

Especially where q_eval_reason is empty and would have a len of 0.

 

[pbaldy]

A brief test shows that comparison doesn't work in SQL Server. I tested and this worked for me:

WHERE Len(IsNull(q_eval_reason, '')) < 11

IsNull() is the T-SQL equivalent of the Nz() function in Access, so that forces the Null into a ZLS, which the Len() function will work properly on.

 

[darbid]

A brief test shows that comparison doesn't work in SQL Server. I tested and this worked for me:

WHERE Len(IsNull(q_eval_reason, '')) < 11

IsNull() is the T-SQL equivalent of the Nz() function in Access, so that forces the Null into a ZLS, which the Len() function will work properly on.

Sorry maybe I explained that wrong. It appears that the linked table query or JET query is not working with the len. The passthrough seems to handle it ok as the passthrough returns rows where the field is empty.

I will however test what you have put above as I will eventually be using ADO for this and thus need to know the above. Thanks.

 

[Galaxiom]

Just a comment on the way you have stored q_eval_reason.

You seem to be expecting standard responses so these would be better represented by an integer with a lookup for display. It would also be much simpler and faster to query especially if you use ranges to group the types of responses you are using as criteria.

 

[darbid]

Just a comment on the way you have stored q_eval_reason.

q_eval_reason is a nchar type of size 690.

You seem to be expecting standard responses so these would be better represented by an integer with a lookup for display.

I might need a little help with this comment. I am sorry I am not very fit when it comes to SQL Server and SQL query language.

 

[Galaxiom]

It would seem that your field is allowing 690 text characters and you are searching for particular strings. This will be a relatively slow process.

Moreover spelling mistakes could easily result in some records being missed as well as the potential to match substrings and get false positives.

Entries that are to be searched are much better stored in a related table, one record per reason using integers to indicate the standard reasons. This table would have FKs to indicate the main record and the ReasonID. It is much, much faster to search this structure.

It could include another field for comments associated with each reason. You would write the non-standard reasons as a comment against a specific reason code allocated to custom reason if it was not standard enough to be allocated its own code.

 

[darbid]

It would seem that your field is allowing 690 text characters and you are searching for particular strings. This will be a relatively slow process.

Moreover spelling mistakes could easily result in some records being missed as well as the potential to match substrings and get false positives.

Entries that are to be searched are much better stored in a related table, one record per reason using integers to indicate the standard reasons. This table would have FKs to indicate the main record and the ReasonID. It is much, much faster to search this structure.

It could include another field for comments associated with each reason. You would write the non-standard reasons as a comment against a specific reason code allocated to custom reason if it was not standard enough to be allocated its own code.

Thank you for helping out.
Ok now I got it. Yes I would love to do all this. But in this case I am getting the data from a database I have zero control over. So the field is what it is. In fact what my search is attempting to do is to pick up on the problems of the other database, both problems of the actual coding of the database and from users that do not give good reasons. This is why I am search for "xx" or for very short reasons.