Using distinct with null

[Константин Комков]

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.

[Massimo Di Pierro]

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.

[Константин Комков]

Massimo Di Pierro, thank you it work!