You can translate the question and the replies:

can subquery be used in join condition?

I'm trying to get the row from table1 with the max effective_date less than or equal to table2.effective_date select blah from table1, table2 where table1.effective_date <= (select max(effective_date) from table1b where table1b.key = table1.key and table1b.effective_date < table2.effective_date) The parser makes a string out of the subquery, then gives aliases to table1 and table2, so the subquery then returns 0 rows. I've used the consistent aliases inside and outside the subquery. The parser overrides the aliases for the tables outside the query. My actual query: CREATE VIEW ps_residency_off_j_ps_stdnt_car_term FOLDER = '/developer folders/ken' AS SELECT ps_stdnt_car_term.emplid AS emplid, ps_stdnt_car_term.acad_career AS acad_career, ps_stdnt_car_term.institution AS institution, ps_stdnt_car_term.strm AS strm, ps_stdnt_car_term.academic_load AS academic_load, ps_stdnt_car_term.tot_cumulative AS tot_cumulative, ps_stdnt_car_term.cum_gpa AS cum_gpa, ps_residency_off.residency AS residency FROM ps_residency_off AS ps_residency_off INNER JOIN ps_stdnt_car_term AS ps_stdnt_car_term ON (ps_residency_off.emplid = ps_stdnt_car_term.emplid AND ps_residency_off.acad_career = ps_stdnt_car_term.acad_career AND ps_residency_off.institution = ps_stdnt_car_term.institution AND ps_residency_off.effective_term = '(select max(cc.effective_term) from ps_residency_off cc where cc.emplid = ps_residency_off.emplid and cc.acad_career = ps_residency_off.acad_career and cc.institution = ps_residency_off.institution and cc.effective_term <= ps_stdnt_car_term.strm)') ;
user
24-08-2015 23:51:52 -0400

1 Answer

Hi! Yes, you can use subqueries in join conditions. I suggest that you check out the "JOIN Operation" section of the Advanced VQL Guide which you can download here: https://community.denodo.com/docs/view/document/Denodo%20Express/Denodo%205.5/Advanced%20VQL%20Guide To debug your query I suggest you examine its execution trace. To learn how to examine an execution trace, check out the "Execution Trace of a Statement" section of the Virtual DataPort Administration Guide, which you can download here: https://community.denodo.com/docs/view/document/Denodo%20Express/Denodo%205.5/Virtual%20DataPort%20Administration%20Guide. Hope this helps!
Denodo Team
25-08-2015 04:41:05 -0400
You must sign in to add an answer. If you do not have an account, you can register here