0

I have code that connects from MS Excel to an Oracle Database, queries the DB and returns the results.

Everything barring this one issue is working correctly. There is one column called Service_ID. There are 3 drop down boxes in excel for this, as you may want to query multiple services. E.g. Service1 AND Service2 AND Service3.

I have defined Service_ID as 

C.SERVICE_ID  || '-' || G.TYPE_ID AS SERVICE_ID

And when I query 

C.SERVICE_ID LIKE :cmbSelectServiceType

It finds the service and works as expected. However, trying to look up Service2 and 3 is where I have the issue.

I have tried: 

C.SERVICE_ID  || '-' || G.TYPE_ID AS SERVICE_ID2
C.SERVICE_ID  || '-' || G.TYPE_ID AS SERVICE_ID3

Which doesn't work.

I have tried also in the WHERE (with and without the lines above) cmbSelectService is the name of the dropdown in the Excel GUI: 

C.SERVICE_ID LIKE :cmbSelectServiceType
AND C.SERVICE_ID LIKE :cmbSelectServiceType2
AND C.SERVICE_ID LIKE :cmbSelectServiceType3

Obviously, I don't normally work in this field, I'm just helping out and could do with some input.

Improve this question

1 Answer 1

Reset to default
1

Try this

C.SERVICE_ID LIKE :cmbSelectServiceType
OR C.SERVICE_ID LIKE :cmbSelectServiceType2
OR C.SERVICE_ID LIKE :cmbSelectServiceType3

With your AND you where querying for a row where C.SERVICE_ID = :cmbSelectServiceType = :cmbSelectServiceType2 = :cmbSelectServiceType3 and I do suspect that in your three dropdown you select three different values.

Improve this answer
7
  • Hi many thanks. All three dropdowns will always have different values. I want to find someone who has one, two or all three. Not 1 or 2 or 3, but 1 and 2, or 1 and 2 and 3, so I'm not sure about the 'OR' statement here. I am getting an error "SQL Command not properly formed" in excel, but the SQL program (Golden) is showing it as fine. There is only one column for Service_ID by the way, but multiple rows for each person in it, and their services. If I remove the two lines for service 2 and 3 it works fine...
    – RossC
    Commented Feb 11, 2015 at 13:31
  • how do you decide if you want to check only 1 and 2 or all three drop down values?
    – mucio
    Commented Feb 11, 2015 at 13:37
  • The user will select 1, or 1 and 2, or 1 and 2 and 3 basically. There wont' be any variance beyond that. For any value not selected I pass a '%' character and look up all of those. If only Service1 is selected anything with 1 is to be returned, if 1 and 2, then any 1 and 2 will be returned.
    – RossC
    Commented Feb 11, 2015 at 13:39
  • 1
    then I will leave it with OR and I will add at the end something like HAVING COUNT(DISTINCT C.SERVICE_ID) = 3 -(COALESCE(:cmbSelectServiceType, 1) + COALESCE(:cmbSelectServiceType2, 1) + COALESCE(:cmbSelectServiceType3 ,1)) this will check that the number of different SERVICE_ID is exactly what you where expecting: if you pass values in 1 and 2, then the formula will return 2 and the query has to return 2 different SERVICE_ID
    – mucio
    Commented Feb 11, 2015 at 13:50
  • I'm sorry about this, I get "Inconsistent Data Type expected CHAR got NUMBER" in Golden, highlighting the 1 in COALESCE(:cbmSelectServiceType, 1) and a "Missing Expression" error via the VBA. I have added the lines from the answer, and added the HAVING to the end.
    – RossC
    Commented Feb 11, 2015 at 15:11

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.