Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
JOIN syntax using row values |
Thu, Aug 4 2016 5:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Aug 4 2016 6:59 AM | Permanent Link |
Fernando Dias Team Elevate | Roy
You are missing a right parenthesis before the '=' sign. -- Fernando Dias [Team Elevate] |
Thu, Aug 4 2016 8:02 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
Thu, Aug 4 2016 9:21 AM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Thu, Aug 4 2016 9:27 AM | Permanent Link |
Michael Riley ZilchWorks | <<
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 |
Thu, Aug 4 2016 10:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Aug 4 2016 10:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |