33 lines
960 B
SQL
33 lines
960 B
SQL
SELECT
|
|
m."header"
|
|
,m.header_sort::int header_sort
|
|
,m.detail_sort::int detail_sort
|
|
,m.stlc
|
|
,f.flag
|
|
,COALESCE(rstt.rn,0) comp_sort
|
|
,COALESCE(rstt.c,m.stlc) stlcc
|
|
,mj.descr
|
|
--,mj."header"
|
|
FROM
|
|
rlarp.molds m
|
|
CROSS JOIN ( VALUES
|
|
('base'),
|
|
('compatible')
|
|
) AS f(flag)
|
|
LEFT JOIN LATERAL regexp_split_to_table(m.compatible,', ') WITH ORDINALITY rstt(c, rn) ON
|
|
f.flag = 'compatible'
|
|
LEFT OUTER JOIN rlarp.molds mj ON
|
|
mj.stlc = COALESCE(rstt.c,m.stlc)
|
|
WHERE
|
|
true
|
|
AND NOT (f.flag = 'compatible' AND COALESCE(rstt.rn,0) = 0)
|
|
AND NOT (f.flag = 'compatible' AND COALESCE(m.header,'') = '')
|
|
AND m.status <> 'I'
|
|
--only list active compatible items
|
|
AND mj.status <> 'I'
|
|
--exclude compatible items that have their own price list header
|
|
AND NOT (f.flag = 'compatible' AND mj."header" IS NOT NULL)
|
|
ORDER BY
|
|
m.header_sort
|
|
,m.detail_sort
|
|
,comp_sort |