join of two table fails in postrgess (fully qualified names in AS

[cvkem]

;; this report contains two variants of the same (intended) query. and the corresponding
;; manual correction I needed to make to the sql statements.

;; I want to make a query that joins a table with itself.
;; the left side of the join contains an additional where clause
;; the output should be a list containing
;; GP-id #(patients with complete profile) #patients
;; given these rows you can subsequently compute the
;; ratio by dividing the second by the third column.
;; (However, the computation of the ratio is not part of the current
;; queries, although that should be possible.)

;; the next two tables are copies of one another.
;; I introduced the duplicate table because I thought that
;; the issue was due to the fact that the original query was
;; performing a self-join on a table.
;;
(def cvrmTable (table db "rv.vinzi_cvrm_xtab"))
(def tmpTable (table db "rv.aa_xtab"))

(def pRatioSQL (-> cvrmTable
(select (where (not (= :profcomplete nil))))
(aggregate [[:count/* :as :cnt]] [:huisarts])
(join (-> tmpTable
(aggregate [[:count/* :as :cnt2]] [:huisarts])
)
:huisarts)
))

;;; The query produced by clojureql is
;;
;; SELECT rv.vinzi_cvrm_xtab.huisarts,count()
;; AS cnt,rv.aa_xtab_subselect.aa_xtab,rv.aa_xtab_subselect.cnt2
;; FROM rv.vinzi_cvrm_xtab JOIN
;; (SELECT rv.aa_xtab.huisarts,count() AS cnt2
;; FROM rv.aa_xtab GROUP BY rv.aa_xtab.huisarts)
;; AS rv.aa_xtab_subselect USING(huisarts)
;; WHERE NOT((profcomplete IS NULL))
;; GROUP BY rv.vinzi_cvrm_xtab.huisarts
;;
;;; A correct output (with minimal changes) could be:
;;
;; SELECT rv.vinzi_cvrm_xtab.huisarts,count() AS cnt, cnt2
;; FROM rv.vinzi_cvrm_xtab JOIN
;; (SELECT rv.aa_xtab.huisarts,count() AS cnt2
;; FROM rv.aa_xtab GROUP BY rv.aa_xtab.huisarts)
;; AS aa_xtab_subselect USING(huisarts)
;; WHERE NOT((profcomplete IS NULL))
;; GROUP BY rv.vinzi_cvrm_xtab.huisarts, cnt2
;;
;; Corrections:
;; - no fully qualified names for alias of subselect (rv. prefixes)
;; - The initial select line omits the ouput fields that
;; are added by the joined subselect.

;; the next statement is almost similar. However, I project out
;; the fields I want to have more explicitly.

(def pRatioSQL (-> cvrmTable
(select (where (not (= :profcomplete nil))))
(aggregate [[:count/* :as :cnt]] [:huisarts])
(join (-> tmpTable
(aggregate [[:count/* :as :cnt2]] [:huisarts])
)
:huisarts)
(project [:huisarts :rv.vinzi_cvrm_xtab.cnt :rv.aa_xtab.cnt2])
))

;; output of clojureql (sql)

;; SELECT rv.vinzi_cvrm_xtab.huisarts,rv.vinzi_cvrm_xtab.cnt,
;; rv.aa_xtab.cnt2,rv.aa_xtab_subselect.cnt2
;; FROM rv.vinzi_cvrm_xtab JOIN
;; (SELECT rv.aa_xtab.huisarts,count(*) AS cnt2
;; FROM rv.aa_xtab GROUP BY rv.aa_xtab.huisarts)
;; AS rv.aa_xtab_subselect USING(huisarts)
;; WHERE NOT((profcomplete IS NULL)) GROUP BY rv.vinzi_cvrm_xtab.huisarts

;; ;; Corrected SQL statement
;; SELECT rv.vinzi_cvrm_xtab.huisarts, count() as cnt,aa_xtab_subselect.cnt2
;; FROM rv.vinzi_cvrm_xtab JOIN
;; (SELECT rv.aa_xtab.huisarts,count() AS cnt2
;; FROM rv.aa_xtab GROUP BY rv.aa_xtab.huisarts)
;; AS aa_xtab_subselect USING(huisarts)
;; WHERE NOT((profcomplete IS NULL))
;; GROUP BY rv.vinzi_cvrm_xtab.huisarts, aa_xtab_subselect.cnt2
;;
;; The applied corrections are:
;; 1. replaced "rv.vinzi_cvrm_xtab.cnt" by "count(*) AS cnt
;; 2. removed fiedl "rv.aa_xtab.cnt2" as this does not exist
:: 3. changed "rv.aa_xtab_subselect" to "aa_xtab_subselect" (2x)
;; 4. added "aa_xtab_subselect.cnt2" to the GROUP BY clause