Oracle Error Question

[former_member2987]

This is not your typical Oracle Error question (I think!)

I'm getting a strange error when I run a query using 7.2 and an Oracle 10 database:

Error putNextEntry failed storing john doe

Exception from Add operation:java.sql.SQLException: ORA-00917: missing comma

Exception from Modify operation:java.sql.SQLException: ORA-00933: SQL command not properly ended

Here's the query right out of my log:

select SearchValue from mxmc_rt.idmv_value_ext_active where attrname = 'MSKEYVALUE' and mskey in (select mskey from mxmc_rt.idmv_value_ext_active where ((attrname='MX_FIRSTNAME' and searchvalue='John') and mskey in (select mskey from idmv_value_ext_active where (attrname='MX_LASTNAME' and searchvalue='Doe'))))

Any ideas? Basically, I need to retrieve the MSKEYVALUE for a given firstname/last name combination. (The mskeyvalue maps back to a separate employeeID)

Interesting thing, is that this query works just fine through Toad.

I'd appreciate any help! 

Thanks,

Matt

[mvoros · Accepted solution]

Hi,

I am not going to be helpful because I don't know what's wrong about that query. But I know that it's quite ugly and it shows how relational DB is not the best choice for IdM. Anyway, I would try to use INTERSECT to reduce level of nesting to 2. Something like:

select SearchValue from mxmc_rt.idmv_value_ext_active where attrname = 'MSKEYVALUE' and mskey in (

select mskey from mxmc_rt.idmv_value_ext_active where attrname='MX_FIRSTNAME' and searchvalue='John'

INTERSECT

select mskey from mxmc_rt.idmv_value_ext_active where attrname='MX_LASTNAME' and searchvalue='Doe'

)

Cheers

[former_member2987 · Accepted solution]

Well Martin, thanks for the advice.  I will look into the use of INTERSECT.  The strange thing is that the query works well enough when running through the Toad interface.

I don't see the development team rewriting the back end anytime soon, so I guess we'll need to muddle through

[former_member2987 · Accepted solution]

Still getting the error message.  I think Oracle just doesn't like this query.  Going to have to find another way to get this info.