Invalid Relational Operator Error

[KelleyM]

I think this is the right subforum for this but let me know if it is not.



I have a select querry that works fine

SELECT DCN
FROM DW_MY_TB
WHERE (((RPT_EXCLUSION)=’N’) AND ((CONVERTED_CONTRACT)=999999999) AND 
((MEMID)=99) AND ((PAIDDT)<20121031) AND ((FROMDT)<20101114) AND 
((THRUDT)>20101114)) OR 
(((RPT_EXCLUSION)=’N’) AND ((CONVERTED_CONTRACT)=999999999) AND 
((MEMID)=99) AND ((PAIDDT)<20121031) AND ((FROMDT)>20101114) AND 
((THRUDT)>20101231));

For efficiency reasons I want to run it as a pass through query. I've done this successfully in the past.

So I changed the type on the ribbon and set up my connection string as I've done before.

I changed the SQL to

SELECT DCN
FROM DW.MY_TB
WHERE (((RPT_EXCLUSION)=’N’) AND ((CONVERTED_CONTRACT)=999999999) AND 
((MEMID)=99) AND ((PAIDDT)<20121031) AND ((FROMDT)<20101114) AND 
((THRUDT)>20101114)) OR 
(((RPT_EXCLUSION)=’N’) AND ((CONVERTED_CONTRACT)=999999999) AND 
((MEMID)=99) AND ((PAIDDT)<20121031) AND ((FROMDT)>20101114) AND 
((THRUDT)>20101231));

When I try to run it I get an error message saying "ORA-00920: Invalid Relational Operator"

Any hints what the trouble is?

Thanks in advance for your help.

[JoeM]

Without knowing the format of each of your fields, and seeing what your data looks like, it is hard to say (things like blanks or nulls values can sometimes be problematic).

I would recommend starting the pass-through query with no criteria, i.e.

SELECT DCN
FROM DW.MY_TB

If that runs fine, then add in the first criteria. If that works, add in the second criteria. Keep adding them in, one at a time, until you get the error. Then you will know that the last one that you added in is your problem, and that is where you will want to focus your attention.

[orange]

I agree with JoeM that you should start with something simple and build on what works.

Also, with a pass through you must use the syntax of the SQL of the Database on which the pass through will be working. In your case it will be Oracle, and it's been over 4 years since I worked with Oracle, so you may have to review Oracle syntax and see if there is something obvious (maybe Dates??).

I went through the SQL for the logic and adjusted it (but I haven't checked it for Oracle syntax)
Try

SELECT DCN
FROM DW.MY_TB
WHERE 

(
((RPT_EXCLUSION)=’N’) AND 
((CONVERTED_CONTRACT)=999999999) AND 
((MEMID)=99) AND 
((PAIDDT)<20121031) AND 
(
(((FROMDT)<20101114) AND 
((THRUDT)>20101114))

OR 

(((FROMDT)>20101114) AND 
((THRUDT)>20101231))
);

[KelleyM]

It's always the little things.

The problem turned out to be in this line (RPT_EXCLUSION)=’N’. I changed it to (RPT_EXCLUSION)='N'. Guess somehow I ended up with characters that looked like single quotes but weren't actually.

Also, thank you, orange, for correcting the duplicitous criteria.

Everything works great now.

[JoeM]

Yes, I find those often don't translate well when copied between different programs.
I noticed it in your original post, but just assumed that it just didn't copy well when your posted your question, especially when you said that the first query worked fine (and the text qualifiers there look the same).

[orange]

Good eye! Yes Access wants ' or ", but often you see posts (and I have received incoming text files
with ` and ’ which are not acceptable to Access.
Glad you got your stuff working.