3

I have written some basic SQL in Oracle, which runs as expected. It selects a client, their ID and what 'services' they can provide.

However, when I add variables that are passed from VBA code the error message ORA-00907: Missing Right Parenthesis occurs. It is due to an OR in the code. 

AND C.CURRENCY LIKE :cmbSelectAccountCcy
AND (S.SERVICEID LIKE :cmbSelectServiceType OR S.SERVICEID LIKE :cmbSelectServiceType2)
AND .... etc

If I remove the OR it runs as expected. The OR is part of a check that includes (at the end)

HAVING COUNT(S.SERVICEID) > 2

Allowing the 2 values to be checked, and only if the 2 (or more) are present display the client.

The combo boxes in Excel will pass the service ID as either a set value from the drop down box, or a % character. I assume the right parenthesis is never reached in some cases.

What is the reason that I can run the SQL query with Hard Coded values, but not passing the very same variables through VBA? I have a Debug.Print() statement in the VBA which is showing all the values I would expect to see passed over.

UPDATE:

Changing the name cmbSelectServiceType2 to cmbServiceTypeTwo appears to have fixed the issue but, as of yet, I cannot figure out why. Any answers appreciated still! The only factor I can see is that the first is 21 chars, the second is < 20 chars. The column data type is VARCHAR2 in the table.

Tangent: The OR statement is now returning Service1 or Service1 and Service2 or Service2, i.e. a fully OR statement. The HAVING clause above was an attempt to impose that only BOTH are allowed.

UPDATE 2

Changing the '2' in cmbSelectServiceType2 to letters 'two' does not work. It seems like 20 chars is some arbitrary limit.

Community
  • 1
  • 1
RossC
  • 1,200
  • 2
  • 11
  • 24
  • 1
    Sounds like a driver problem; maybe it's confused about the bind variable data type? – Alex Poole Feb 24 '15 at 11:00
  • Thanks @alexpoole so does this mean I need to (if possible) cast the variables to their correct type, for example: `CAST(:Variable As Number)` or such? They are all of type `VARCHAR2` I'm not a dev by trade, I'm trying to get something automated in a new job, so this is me coming at it pretty blind. – RossC Feb 24 '15 at 11:05
  • You can't cast the `%` as a number. If the driver is breaking it then it's hard to guess how to work around it. Checking/updating/replacing the driver is maybe preferable but might not be possible for you? I can think of one possible approach but it's a bit hit-and-miss without knowing the underlying problem; `(S.SERVICEID = :cmbSelectServiceType OR S.SERVICEID =:cmbSelectServiceType2 OR :cmbSelectServiceType = '%' OR :cmbSelectServiceType2 = '%')` – Alex Poole Feb 24 '15 at 11:14
  • @AlexPoole Thanks again. I tried that approach just now and still 'missing right parenthesis' for any combo of variables I have passed. If I take out the 'OR' and just have the one Service ID variable it works fine, strange (to me) that it breaks with 2. Hard coding 2 with actual values works fine, as does the approach above with hard coding. If it's driver related as you say, then this is not going to be fun at all!! – RossC Feb 24 '15 at 11:22
  • "The OR is part of a check that includes (at the end) `HAVING COUNT(S.SERVICEID) > 2`" - What does that mean? – David Faber Feb 24 '15 at 12:49
  • @DavidFaber that was added with help from someone else, I'm afraid. I wanted to check if the person has one, two or more of the services assigned. That should be >1 in the code, but I'm not sure how it works to be perfectly blunt. I was told if I'm passing more than one variable to group it, and to limit the search that way. A client could have service 1, service 2 and so on. The user will search for Service 1, Or service 2, or service 1 AND service 2. It doesn't matter if the person searches only for Service 1, but the client has 1 and 2. As long as the variable is there at all. – RossC Feb 24 '15 at 13:15
  • daft suggestion, but what happens if you put a space between the 2 and the ) in `S.SERVICEID LIKE :cmbSelectServiceType2)`? (I'm just wondering if the bracket is being taken to be part of the bind variable name) – Boneist Feb 24 '15 at 13:19
  • @Boneist I never though of that (and vba being as awful as it is, it wouldn't suprise me), but sadly it throws the same error message about missing right parenthesis. – RossC Feb 24 '15 at 13:21
  • Hmm. And [this post](http://stackoverflow.com/questions/24747684/execution-of-oracle-ad-hoc-query-with-bind-parameters-erroring-out-ora-00907-m) doesn't help? – Boneist Feb 24 '15 at 13:34
  • @Boneist I'm reading it now, thanks a million. Something VERY ODD has happened (odd to me anyway). I renamed `:cmbSelectServiceType2` to `:combServiceTypeTwo` and it seems to be working now...? That makes no sense. Could this be a length of variable issue? The first one is 21 chars (excluding the `:`) and the second is < 20. – RossC Feb 24 '15 at 13:42
  • 2
    I would be more inclined to think it was the number causing the issue, rather than the length (well, as long as the length is <= 30), but I've never used VBA *{:-) Oracle wouldn't have a problem with the bind variable name containing a number, providing it was not at the start of the identifier, but perhaps VBA or the data provider doesn't like it? – Boneist Feb 24 '15 at 13:55
  • Still sounds like a driver problem, but could be VBA... I don't understand your tangent though; do you mean you need `HAVING COUNT(DISTINCT S.SERVICEID) > 2`? – Alex Poole Feb 24 '15 at 18:13
  • @AlexPoole I was under the (wrong) impression that this `HAVING COUNT(S.SERVICE_ID) >= 2 would only return clients that have multiple service IDs, i.e. the one's the user is searching for. I now see that I'm completely miles off the mark. I'm offloading that onto the VBA as it's as handy there as anywhere else. – RossC Feb 25 '15 at 07:57

1 Answers1

0

according to:

Getting around the Max String size in a vba function?

A vba function cannot handle a string with more than 255 chars, is it possible that this may be causing the problem, especially as when you cut the string length it works.

Community
  • 1
  • 1
HardLeeWorking
  • 195
  • 2
  • 13
  • Hi, but it's the length of the variable name, once it is under 20 chars it appears to work ok. I don't know who the size of the String would factor in to be honest. The String itself is only 3 chars long. – RossC Feb 27 '15 at 15:19