MS QUERY - Issues using multiple LIKES with DATE parameters - Makes no sense.

[chardy]

Good Afternoon,

I am currently experiencing some issues while attempting to pull back data into excel from our Oracle DB.

Basically, I am looking at MS Query and I input 2 LIKE parameters using OR. (I am using the criteria fields as opposed to SQL because I need to input questions in the prompts that I will describe below). These parameters are as follows:

LIKE '%CSC' OR 'COM%'

This works completely fine. It pulls back all groups that end with "CSC" and all groups that start with "COM." I have also tried by separating the Likes into 2 separate fields to cover the "OR" portion of the sql. It would look like this in the criteria fields:

LIKE '%CSC'
LIKE 'COM%'

With that being said, as soon as I input my date parameters it completely overrides the LIKEs that I have used to filter the groups in the first column. My date parameters are as follows:

>=[First day of the business week? YYYY-MM-DD 00:00:00] AND <=[Last day of the business week? YYYY-MM-DD 23:59:59]

With this being a date field, I receive a prompt to enter these date parameters as soon as I refresh the query (which is normal, and exactly what I wanted). However, when the data is returned they query has completely overwritten the LIKE parameters for the "groups" and it returns results from every kind of group in the DB.

I am 100% sure this structure of parameter works because I am using it successfully when I only have one "LIKE" criteria. However, the second that I input a second "LIKE" and try to use the date parameter, it completely looks past the "LIKE' criteria.

Any thoughts?

Please let me know if you require more information. Happy to supply because this is an extremely frustrating experience. I also might add, that I have checked the SQL that these parameters input and it seems to be correct as far as I can see. I have also confirmed it with one of our DBAs.... Completely stumped on this one.

Thank you for your time everyone, this is my first post on the forum.

Regards,
Chris

 

[ChrisM]

Check the physical placement of your criteria in MS Query. Are the two LIKE criteria listed in separate rows? If so, then your date criteria must also be in each row. Use the same date criteria in each row, you'll just have to type it twice.

When you have two criteria fields, they are joined by an AND.

When you have two rows of criteria, they are joined by an OR.

So to put all that together, you probably have:

(LIKE '%CSC' AND Date = xxx) OR LIKE 'COM%'

and you need it to be:

(LIKE '%CSC' AND Date = xxx) OR (LIKE 'COM%' AND Date = xxx)

 

[chardy]

Thanks for the Reply Chris....

Checking your suggestion right away. The DB is rather slow with the response time because of the amount of records. I will update ASAP as soon as I know if this was effective.

Appreciate the advice regardless though. Thanks for your time.

Cheers,
Chris

 

[chardy]

Hey Chris,

SQL came out exactly as you described. Unfortunately it is still returning many results from groups that do not match those described in the LIKE critieria.

This is a tricky one

Chris

 

[Richard Schollar]

Can you post the actual SQL you are using?

 

[ChrisM]

I just shorthand wrote the date part. Have you fully duplicated the date criteria in each row as well?

This should be listed twice, in each row:

>=[First day of the business week? YYYY-MM-DD 00:00:00] AND <=[Last day of the business week? YYYY-MM-DD 23:59:59]

 

[chardy]

SELECT "TableName"."Assigned Group"
FROM "TableName" "TableName"
WHERE ("TableName"."Assigned Group" Like '%CSC') AND ("TableName"."Last Resolved Date">=? And "TableName"."Last Resolved Date"<=?) OR ("TableName"."Assigned Group" Like 'COM%') AND ("TableName"."Last Resolved Date">=? And "TableName"."Last Resolved Date"<=?)

Thanks for the help everyone.

 

[ChrisM]

Hmm, looks fine to me. Can you try rewriting to this and see what happens:

(Like A or Like B) AND (>= Date AND <= Date)

 

[chardy]

Hey Chris,

Tried that as well with no such luck Still returning many results from other teams.

The weirdest thing is that this works perfectly well with only one "LIKE" criteria. Throw 2 criteria in there, and the problem comes to life.

Our DBA mentioned that he thought it might be a problem with MS QUERY itself, do you think this is possible?

Thanks,
Chris

 

[ChrisM]

Sorry, I have no idea. But certainly, using MS Query to hit an Oracle database is going to raise some edge cases where things don't work quite like you hope. Could be MS Query, or could be the ODBC driver itself.

There is an Oracle-supplied ODBC driver, and there is a Microsoft Oracle ODBC driver, maybe you could change to the other?

Can you run an extra query in Oracle to alias these group names into something easier to search? Then use MS Query to run that new query.

In Oracle: Select Right(Assigned Group,3) as XXX, Left(Assigned Group, 3) as YYY From.....

Now in MS Query set parameters on the XXX and YYY fields and you won't have to use LIKE.

Finally, you could bypass MS Query and use ADO and write the SQL by hand.