Hi, I'm having problems getting

[dkn]

Hi,

I'm having problems getting joins to work when retrieving data from an Oracle database.

The SQL statement I'm trying to use is:

Select WORKORDER.PMNUM, WORKORDER.STATUS, WORKORDER.EQNUM, WORKORDER.LEADCRAFT, WORKORDER.CHANGEBY, WORKORDER.CHANGEDATE, WORKORDER.WOPM4, WORKORDER.JPNUM, WORKORDER.ESTDUR, PM.FREQUENCY from MAXIMO.WORKORDER INNER JOIN MAXIMO.PM ON WORKORDER.PMNUM = PM.PMNUM where WORKORDER.WONUM = 0000242844

Which gives the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00933: SQL command not properly ended

/temreps/PMDetail.asp, line 29

Does anyone know what the correct syntax should be?

Thanks in advance

David )

 

[Targol]

If you add a semicolon ( at the end, do you still have the error ? Water is not bad as long as it stays out human body ;-)

 

[dkn]

I've tried adding a semi-colon, but the error message is still the same. The statement works fine with Access, so I guess this is just something specific to Oracle. I can get round it by just writing an extra query to pull the data I want out of the second table... just seems like a fudge that all...

David.

 

[Targol]

The "INNER JOIN" clause may be the source of your problem. Try it the old SQL way :

Select WORKORDER.PMNUM, WORKORDER.STATUS, WORKORDER.EQNUM, WORKORDER.LEADCRAFT, WORKORDER.CHANGEBY, WORKORDER.CHANGEDATE, WORKORDER.WOPM4, WORKORDER.JPNUM, WORKORDER.ESTDUR, PM.FREQUENCY from MAXIMO.WORKORDER, MAXIMO.PM 
WHERE WORKORDER.PMNUM = PM.PMNUM 
AND WORKORDER.WONUM = 0000242844

Water is not bad as long as it stays out human body ;-)

 

[GaryC123]

What version of Oracle ru using, Inner Join is only available from Oracle9i Saturday 12.00

20.00

3.00am

Sunday [img http

 

[dkn]

We're are running Oracle 7, so i guess thats why Inner Joins don't work. I've tried using the old SQL way, and it does work, but its woefully slow.. just by including the second table in my SQL statement the query goes from taking 5 seconds to run, to upwards of a minute.. I get better performance using two seperate queries.

Thanks for your input )

David.

 

[Targol]

What about creating a view in SGBD that'll make the join between WORKORDER an PM Tables ? Water is not bad as long as it stays out human body ;-)

 

[dkn]

Pardon my ignorance but what is SGDB?

David

 

[Targol]

Excuse me, I didnt realise that it was french abrev "Systeme de Gestion de Base de Donnees" => your database. Water is not bad as long as it stays out human body ;-)