;; 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"))
;;; The query produced by clojureql is
;;
;; SELECT rv.vinzi_cvrmxtab.huisarts,count()
;; AS cnt,rv.aa_xtab_subselect.aa_xtab,rv.aa_xtab_subselect.cnt2
;; FROM rv.vinzi_cvrm_xtab JOIN
;; (SELECT rv.aaxtab.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_cvrmxtab.huisarts,count() AS cnt, cnt2
;; FROM rv.vinzi_cvrm_xtab JOIN
;; (SELECT rv.aaxtab.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.
;; 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_cvrmxtab.huisarts, count() as cnt,aa_xtab_subselect.cnt2
;; FROM rv.vinzi_cvrm_xtab JOIN
;; (SELECT rv.aaxtab.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
;; 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_cvrmxtab.huisarts,count() ;; AS cnt,rv.aa_xtab_subselect.aa_xtab,rv.aa_xtab_subselect.cnt2 ;; FROM rv.vinzi_cvrm_xtab JOIN ;; (SELECT rv.aaxtab.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_cvrmxtab.huisarts,count() AS cnt, cnt2 ;; FROM rv.vinzi_cvrm_xtab JOIN ;; (SELECT rv.aaxtab.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_cvrmxtab.huisarts, count() as cnt,aa_xtab_subselect.cnt2 ;; FROM rv.vinzi_cvrm_xtab JOIN ;; (SELECT rv.aaxtab.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