I wanted to replace

LEFT OUTER JOIN AssessmentDetails Y ON (Y._fkAssessments = :xAssessments) AND (Y._fkQSC = M._fkQSC) AND (Y._fkAssessor = :xAssessor)

with

LEFT OUTER JOIN AssessmentDetails Y ON (Y._fkAssessments,Y._fkQSC,Y._fkAssessor = (:xAssessments, M._fkQSC,   :xAssessor)

to see if it worked any better but I'm not allowed

ElevateDB Error #700 An error was found in the statement at line 22 and column 85 (Invalid expression , found, row values not allowed)

The idea was that I have a compound index on AssessmentDetails with the columns above and I was hoping to get an single index scan rather than an index scan on the first term and filters on the next two.

Any ideas?


Roy Lambert

Roy

You are missing a right parenthesis before the '=' sign.

--
Fernando Dias
[Team Elevate]

Fernando


Your eyeballs must be younger than mine

It works now but is no better - same logic in the execution plan

Roy Lambert

Roy,

For that to work you have to have indexes starting with the same 3 columns on both tables, is it the case ?
And the eyeballs... They had better days - I have to take off my spectacles to read the screen... odd eh

--
Fernando Dias
[Team Elevate]

<<
Fernando Dias wrote:
I have to take off my spectacles to read the screen... odd eh
>>

Yes, that is odd.
Michael Riley
GySgt USMC (Retired)
www.zilchworks.com

Fernando

>For that to work you have to have indexes starting with the same 3 columns on both tables, is it the case ?

Yup - the primary key is a compound index with that structure - it may just be that the index scan / filter combination is the best way to do it.

>And the eyeballs... They had better days - I have to take off my spectacles to read the screen... odd eh

In browsers I have discovered the magnificence of ctrl +

Roy

Michael

>I have to take off my spectacles to read the screen... odd eh
>>>
>
>Yes, that is odd.

Not if you're shortsighted and the specs need replacing.

I sometimes do the same for anything close up

Roy