ORA-00933: SQL command not properly ended

[justTryingThis]

Hi I'm new to oracle using 8.1 I getting the 933 error when excuting the following code what am I missing ?

select
sdidataitem.paramid,
sdidataitem.displayvalue,
sdidataitem.DISPLAYUNITS,
sdidataitem.PARAMLISTID,
sdidataitem.dataset,
sdidataitem.u_quant_element,
SDIDATAITEM.U_NONREPORTABLE
from
submission
inner join sample on submission.submissionid= sample.u_submissionid
inner join sdidataitem on sample.sampleid = sdidataitem.keyid1
where
sample.u_submissionid = '619390'
and
upper(SDIDATAITEM.VARIANTID) NOT IN('ACPREP','WETPREP','MACHINE',
'ACLAB','COLORIMETRICAC','COLORIMETRICW C','GRAVIMETRICAC',
'GRAVIMETRICWC','LOI','MASSSPEC','PELAB','PRICING','TITRATIONAC',
'TITRATIONWC','TURBIDITY','VARIOUS SI','ELECTROPLATEDWC')

 

[Dagon]

I don't think Oracle 8 supports the "inner join" syntax. You will need to upgrade to at least 9.

 

[SantaMufasa]

I don't think Oracle 8 supports the "inner join" syntax. You will need to upgrade to at least 9.

...Or, you can use Oracle's pre-Oracle9 join syntax which relies upon WHERE clauses and, for outer joins, the "(+)" operator on the WHERE clauses.

Let us know if you need help reconstructing your SELECT for Oracle8i-compatible syntax.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[justTryingThis]

Mufasa

I tried the query with just a join instead of inner joins
I get the same error

I would appreciate any help you could give me on this query

 

[Turkbear]

Hi,
Can you post your revised Sql ( does it look something like this)?

select
sdidataitem.paramid,
sdidataitem.displayvalue,
sdidataitem.DISPLAYUNITS,
sdidataitem.PARAMLISTID,
sdidataitem.dataset,
sdidataitem.u_quant_element,
SDIDATAITEM.U_NONREPORTABLE
from
submission,sample,sdidataitem
where
submission.submissionid= sample.u_submissionid
and 
sample.sampleid = sdidataitem.keyid1
and
sample.u_submissionid = '619390'
and
upper(SDIDATAITEM.VARIANTID) NOT IN('ACPREP','WETPREP','MACHINE',
'ACLAB','COLORIMETRICAC','COLORIMETRICW C','GRAVIMETRICAC',
'GRAVIMETRICWC','LOI','MASSSPEC','PELAB','PRICING','TITRATIONAC',
'TITRATIONWC','TURBIDITY','VARIOUS SI','ELECTROPLATEDWC')

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[justTryingThis]

Turkbear

The original query is below I tried to revise it so it would run faster

select
sdidataitem.paramid,
sdidataitem.displayvalue,
sdidataitem.DISPLAYUNITS,
sdidataitem.PARAMLISTID,
sdidataitem.dataset,
sdidataitem.u_quant_element,
SDIDATAITEM.U_NONREPORTABLE
from
sdidataitem, sample, submission
where
sdidataitem.keyid1 = sample.sampleid
and sample.u_submissionid = submission.submissionid
and upper(SDIDATAITEM.VARIANTID) NOT
IN('ACPREP','WETPREP','MACHINE','ACLAB','COLORIMETRICAC','COLORIMETR ICW C', GRAVIMETRICAC','GRAVIMETRICWC','LOI','MASSSPEC','PELAB','PRICING','TITRATIONAC','TITRATIONWC',
'TURBIDITY','VARIOUS SI', 'ElectroplatedWC')
and sample.u_submissionid = @variable('SubmissionID'

 

[Turkbear]

Hi,
A NOT IN statement can cause a slow response even with an indexed field, since every record needs to be checked ( an explain plan probably shows a full table scan for sdidataitem), as an experiment try this method:

select
sdidataitem.paramid,
sdidataitem.displayvalue,
sdidataitem.DISPLAYUNITS,
sdidataitem.PARAMLISTID,
sdidataitem.dataset,
sdidataitem.u_quant_element,
SDIDATAITEM.U_NONREPORTABLE
from
submission,sample,sdidataitem
where
submission.submissionid= sample.u_submissionid
and 
sample.sampleid = sdidataitem.keyid1
and
sample.u_submissionid = '619390'
MINUS
select
sdidataitem.paramid,
sdidataitem.displayvalue,
sdidataitem.DISPLAYUNITS,
sdidataitem.PARAMLISTID,
sdidataitem.dataset,
sdidataitem.u_quant_element,
SDIDATAITEM.U_NONREPORTABLE
from
submission,sample,sdidataitem
where
submission.submissionid= sample.u_submissionid
and 
sample.sampleid = sdidataitem.keyid1
and
sample.u_submissionid = '619390'
and
upper(SDIDATAITEM.VARIANTID) IN('ACPREP','WETPREP','MACHINE',
'ACLAB','COLORIMETRICAC','COLORIMETRICW C','GRAVIMETRICAC',
'GRAVIMETRICWC','LOI','MASSSPEC','PELAB','PRICING','TITRATIONAC',
'TITRATIONWC','TURBIDITY','VARIOUS SI','ELECTROPLATEDWC');

Add, if not already there, a function-based index on Upper(SDIDATAITEM.VARIANTID) to further help .






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dagon]

I don't know if function-based indexes were available in 8. Sample.u_submissionid and submission.submissionid may be more important fields to index, if it they aren't already.

 

[justTryingThis]

Turkbear

I tried your suggestion the query ran two mins longer

Tables are indixed as follows

Sample

PRIMARY KEY ( SAMPLEID )
INDEX U_SUBMISSIONID ON
SAMPLE(U_SUBMISSIONID)

Submission
PRIMARY KEY ( SUBMISSIONID )

SDIDATAITEM
PRIMARY KEY ( SDCID, KEYID1, KEYID2, KEYID3, PARAMLISTID, PARAMLISTVERSIONID, VARIANTID, DATASET, PARAMID, PARAMTYPE, REPLICATEID

 

[Turkbear]

Hi,
Just to coonfirm, Function-Based Indexes were introduced in 8i.

http://www.oracle-base.com/articles/8i/FunctionBasedIndexes.php

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dagon]

The problem is likely to be with the join to sdidataitem. You are joining on KEYID1 only but this is not the leading column of the concatenated index. Try putting an index on KEYID1 on its own.