you can mix and match DAL with SQL so just replace (a_wlp2.SUBSPC ???????? tt_main.SUBSPC)
with "a_wlp2.SUBSPC *IS NOT DISTINCT FROM* tt_main.SUBSPC" should work. On Tuesday, 14 January 2020 04:39:38 UTC-8, Константин Комков wrote: > > Hello! > I have that query: > Firebird SQL > SELECT > a_wlp2.id, > s_disc_names.FULL_NAME > FROM tt_main > JOIN a_groups ON (tt_main.GROUP_ID = a_groups.id) > JOIN a_wlp2 ON ((a_groups.WLP = a_wlp2.WLP) AND (a_wlp2.SUBSPC *IS NOT > DISTINCT FROM* tt_main.SUBSPC)) > JOIN a_wlp2_distrib ON ((a_wlp2.id = a_wlp2_distrib.WLP_ITEM) AND ( > a_wlp2_distrib.MODULE = tt_main.MODULE)) > JOIN s_disc_names ON (a_wlp2.DISC_NAME = s_disc_names.id) > WHERE (tt_main.id = 241) > ORDER BY s_disc_names.FULL_NAME > In that query I use *IS NOT DISTINCT FROM *becouse a_wlp2.SUBSPC and > tt_main.SUBSPC can be null. > > *Are there way to write that query using DAL in web2py?* > > rows = db(db.tt_main.id == request.vars.group_id).select( > db.a_wlp2.id, > db.s_disc_names.FULL_NAME, > orderby=db.s_disc_names.FULL_NAME, > join=[db.a_groups.on(db.tt_main.GROUP_ID == db.a_groups.id), > db.a_wlp2.on((db.a_groups.WLP == db.a_wlp2.WLP) & (a_wlp2.SUBSPC > ???????? tt_main.SUBSPC)), > db.a_wlp2_distrib.on((db.a_wlp2.id == db.a_wlp2_distrib.WLP_ITEM) & > (db.a_wlp2_distrib.MODULE == > db.tt_main.MODULE)), > db.s_disc_names.on(db.a_wlp2.DISC_NAME == db.s_disc_names.id)]) > > If I use a_wlp2.SUBSPC == tt_main.SUBSPC query don't work. > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/acd7e187-fa1b-4292-bc4f-827da26a9516%40googlegroups.com.
