26 lines
710 B
SQL
26 lines
710 B
SQL
SELECT
|
|
s.bk7code plevel
|
|
,bk7des1
|
|
,bk7des2
|
|
,count(DISTINCT trim(CASE WHEN dba = '' THEN descr ELSE dba END)) FILTER (WHERE code IS NOT NULL) cnt
|
|
,string_agg(DISTINCT i.JBPLCD ,', ') listcodes
|
|
,jsonb_agg(DISTINCT trim(CASE WHEN dba = '' THEN descr ELSE dba END)) custs
|
|
FROM
|
|
lgdat.sach s
|
|
LEFT OUTER JOIN rlarp.cust c ON
|
|
s.BK7CODE = c.plevel
|
|
AND c.status = 'A'
|
|
LEFT OUTER JOIN "CMS.CUSLG".IPRCBHC i ON
|
|
i.JBPLVL = c.PLEVEL
|
|
WHERE
|
|
s.bk7code <> ''
|
|
GROUP BY
|
|
s.bk7code
|
|
,bk7des1
|
|
,bk7des2
|
|
HAVING
|
|
count(DISTINCT trim(CASE WHEN dba = '' THEN descr ELSE dba END)) FILTER (WHERE code IS NOT NULL) >= 0
|
|
ORDER BY
|
|
cnt DESC
|
|
, plevel ASC
|