I need to put left join by using index join / extended join but don’t know how to use any constant or expression after “on key”. Example: what is the syntax of putting ‘Salary_’ as hardcoded in below query. Please suggest. Note: I can use expression very easily in normal join (without extended join).
Select * from Test a Left Join Test b on ‘Salary_’ || split(b.UserID, ‘_’)[1] for a
where
a.Type = 'User" and b.Type = ‘Salary’
My index is on field UserID and Document ID of salary is Salary_11, Salary_22…
CREATE INDEX ix2 ON Test('Salary_' || SPLIT(UserID, '_')[1] ) WHERE Type = 'Salary';
SELECT * FROM Test a LEFT JOIN Test b
ON KEY 'Salary_' || SPLIT(b.UserID, '_')[1] FOR a
WHERE a.Type = 'User" AND b.Type = 'Salary';
Thank you for your prompt reply. I got the concept that I need to maintain the complete key of LHS (which is a). The little extension in my query.
The above query gives result but giving wrong results where salary does not exist (b is showing user detail where salary does not exist). It doesn’t give the result if I need to fetch all the records from LHS and along b/salary null. Example: below query does not work.
SELECT * FROM Test a LEFT JOIN Test b
ON KEY ‘Salary_’ || SPLIT(b.UserID, ‘_’)[1] FOR a
WHERE a.Type = 'User" AND (b.Type = ‘Salary’ or b is missing)
it gives error: No index available for join term a
CREATE INDEX ix2 ON Test('Salary_' || SPLIT(UserID, '_')[1] );
SELECT a, CASE WHEN b.Type = 'Salary' TEHN b ELSE MISSING END AS b
FROM Test a LEFT JOIN Test b
ON KEY 'Salary_' || SPLIT(b.UserID, '_')[1] FOR a
WHERE a.Type = 'User" ;