52 lines
1.4 KiB
SQL
52 lines
1.4 KiB
SQL
DROP VIEW rlarp.plm;
|
|
|
|
CREATE VIEW rlarp.plm AS
|
|
WITH
|
|
------------------parse description lines into columns-------------------
|
|
plm AS (
|
|
SELECT
|
|
japlcd plcode
|
|
,substr(japld1,1,1) func
|
|
,substr(japld1,10,1) basis
|
|
,substr(japld1,3,2) tier
|
|
,substr(japld2,1,3) currency
|
|
,substr(japld1,6,3) country
|
|
,substr(japld1,12,5) parent
|
|
,min(jbfdat) fdat
|
|
,max(jbtdat) tdat
|
|
--,listagg(distinct bvcurr,',') curr
|
|
--,listagg(distinct bvctry,',') ctry
|
|
,p.JAPLDS D1
|
|
,p.JAPLD1 D2
|
|
,p.JAPLD2 D3
|
|
FROM
|
|
lgdat.iprca p
|
|
LEFT OUTER JOIN "CMS.CUSLG".iprcbhc i ON
|
|
japlcd = i.jbplcd
|
|
--INNER JOIN lgdat.cust c ON
|
|
-- bvfut14 = i.jbplvl
|
|
WHERE
|
|
1=1
|
|
--only include regional, catalog, and programs
|
|
--substr(japld1,10,1) IN ('R','C','P')
|
|
--or any other applicable price list, but they shoudl have already been defined so this is somewhat redundant
|
|
--OR (
|
|
-- DATERANGE('2020-05-31','2021-06-01') && DATERANGE(jbfdat,jbtdat)
|
|
-- AND bvstat = 'A'
|
|
--)
|
|
GROUP BY
|
|
japlcd
|
|
,substr(japld1,1,1)
|
|
,substr(japld1,3,2)
|
|
,substr(japld2,5,3)
|
|
,substr(japld1,6,3)
|
|
,substr(japld1,10,1)
|
|
,substr(japld1,12,5)
|
|
,p.JAPLDS
|
|
,p.JAPLD1
|
|
,p.JAPLD2
|
|
)
|
|
SELECT * FROM plm
|
|
|
|
GRANT SELECT ON rlarp.plm to public;
|