Untitled
unknown
sql
2 years ago
12 kB
8
Indexable
WITH
ibmMCPSarrangementGrade AS (
SELECT workkey as workkeyIBM , array_sort(array_distinct(array_agg(upper(wrkatr1)))) AS ibmArrangementGrade, array_sort(array_distinct(array_agg(upper(wrkatr1)))) AS ibmArrangementGradeOrig
FROM "current_all_ice_source".iacwlc
WHERE soccode = '044' AND wrkatr1 IN ('NOP', 'PAY', 'pay')
AND workkey NOT IN (select workkey from "current_all_ice_source".iacwrk where wrkisref = 'Y')
GROUP BY workkey
),
ibmMCPSdramaticoMusical AS (
SELECT workkey as workkeyIBM, array_sort(array_distinct(array_agg(upper(wrkatr2)))) AS ibmDramaticoMusical
FROM "current_all_ice_source".iacwlc
WHERE soccode = '044' AND wrkatr2 IN ('N', 'Y')
AND workkey NOT IN (select workkey from "current_all_ice_source".iacwrk where wrkisref = 'Y')
GROUP BY workkey
),
ibmMCPSlastDistRefMech AS (
SELECT workkey as workkeyIBM, array_sort(array_distinct(array_agg(upper(wrkatr3)))) AS ibmLastDistRefMech
FROM "current_all_ice_source".iacwlc
WHERE soccode = '044'
AND wrkatr3 IN
('0001A', '0001B', '0002A', '0002B', '0003A', '0003B', '0004A', '0004B', '0005B', '0006A', '0006B', '0007A', '0007B', '0008A', '0008B', '0009B', '0010A', '0010B', '0011A', '0011B', '0012A', '0012B', '0101A', '0101B', '0102A', '0102B', '0103A', '0103B', '0104A', '0104B', '0105B', '0106A', '0106B', '0107A', '0107B', '0108A', '0108B', '0109A', '0109B', '0110A', '0110B', '0111A', '0111B', '0112A', '0201A', '0201B', '0202B', '0203A', '0203B', '0204B', '0205A', '0205B', '0206A', '0206B', '0207A', '0207B', '0208A', '0208B', '0209A', '0209B', '0210A', '0210B', '0211A', '0211B', '0212A', '0212B', '0301A', '0301B', '0302A', '0302B', '0303A', '0304A', '0304B', '0305A', '0305B', '0306A', '0306B', '0307A', '0307B', '0308A', '0308B', '0309A', '0309B', '0310A', '0310B', '0311A', '0311B', '0311C', '0312A', '0312B', '0401A', '0401B', '0402A', '0402B', '0403A', '0403B', '0404B', '0405A', '0405B', '0406A', '0406B', '0407A', '0407B', '0408A', '0408B', '0409A', '0409B', '0410A', '0410B', '0411A', '0411B', '0412A', '0412B', '0412C', '0501A', '0501B', '0502A', '0502B', '0503A', '0503B', '0504A', '0504B', '0505A', '0505B', '0506A', '0506B', '0507A', '0507B', '0507C', '0508A', '0508B', '0509A', '0509B', '0510A', '0510B', '0510C', '0511A', '0511B', '0512A', '0512B', '0601A', '0601B', '0602A', '0602B', '0602C', '0603A', '0603B', '0604A', '0604B', '0605A', '0605B', '0606A', '0606B', '0607A', '0607B', '0608A', '0608B', '0609A', '0609B', '0610A', '0610B', '0611A', '0611B', '0612A', '0612B', '0701A', '0701B', '0702A', '0702B', '0703A', '0703B', '0704A', '0704B', '0705A', '0705B', '0706A', '0706B', '0707A', '0707B', '0708A', '0708B', '0709A', '0709B', '0710A', '0710B', '0711A', '0711B', '0712A', '0712B', '0801A', '0801B', '0802A', '0802B', '0803A', '0803B', '0804A', '0804B', '0805A', '0805B', '0806A', '0806B', '0807A', '0807B', '0808A', '0808B', '0809A', '0809B', '0810A', '0810B', '0811A', '0811B', '0812A', '0812B', '0901A', '0901B', '0902A', '0902B', '0902C', '0903A', '0903B', '0904A', '0904B', '0905A', '0905B', '0906A', '0906B', '0907A', '0907B', '0908A', '0908B', '0909A', '0909B', '0910A', '0910B', '0911A', '0911B', '0912A', '0912B', '1001A', '1001B', '1002A', '1002B', '1003A', '1003B', '1004A', '1004B', '1005A', '1005B', '1006A', '1006B', '1007A', '1007B', '1008A', '1008B', '1009A', '1009B', '1010A', '1010B', '1011A', '1011B', '1012A', '1012B', '1101A', '1101B', '1102A', '1102B', '1103A', '1103B', '1104A', '1104B', '1105A', '1105B', '1106A', '1106B', '1107A', '1107B', '1108A', '1108B', '1109A', '1109B', '1110A', '1110B', '1111A', '1111B', '1112A', '1112B', '1201A', '1201B', '1202A', '1202B', '1203A', '1203B', '1204A', '1204B', '1205A', '1205B', '1206A', '1206B', '1207A', '1207B', '1208A', '1208B', '1209A', '1209B', '1210A', '1210B', '1211A', '1211B', '1212A', '1212B', '1301A', '1301B', '1302A', '1302B', '1303A', '1303B', '1304A', '1304B', '1305A', '1305B', '1306A', '1306B', '1307A', '1307B', '1308A', '1308B', '1309A', '1309B', '1310A', '1310B', '1311A', '1311B', '1312A', '1312B', '1401A', '1401B', '1402A', '1402B', '1403A', '1403B', '1404A', '1404B', '1405A', '1405B', '1406A', '1406B', '1407A', '1407B', '1408A', '1408B', '1409A', '1409B', '1410A', '1410B', '1411A', '1411B', '1412A', '1412B', '1501A', '1501B', '1502A', '1502B', '1503A', '1503B', '1504A', '1504B', '1505A', '1505B', '1506A', '1507A', '1507B', '1508A', '1508B', '1509A', '1509B', '1510A', '1510B', '1511A', '1511B', '1512A', '1512B', '1601A', '1601B', '1602A', '1602B', '1603A', '1603B', '1604A', '1604B', '1605A', '1605B', '1606A', '1606B', '1607A', '1607B', '1610B', '1611B', '1612B', '1701B', '1702B', '1703B', '1703C', '1704B', '1705B', '1706B', '1707B', '1708B', '1709B', '1710B', '1711B', '1712B', '1801B', '1802B', '1803B', '1804B', '1805B', '1806B', '1807B', '1808B', '1809B', '1810B', '1811B', '1812B', '1901B', '1902B', '1903B', '1904B', '1905B', '1906B', '1907B', '1908B', '1909B', '1910B', '1911B', '1912B', '2001B', '2002B', '2003B', '2004B', '2005B', '2006B', '2007B', '2008B', '201608C', '201609B', '201609M', '2016101', '201611D', '201611M', '2016121', '2016122', '201612B', '201701M', '201702A', '201702M', '201703M', '2017041', '201704A', '201705M', '201706A', '201706M', '2017071', '2017072', '201708M', '201709M', '2017101', '2017102', '201711M', '2017121', '2017122', '201801M', '201802M', '201803B', '201803M', '2018041', '201804A', '201805M', '201806M', '2018071', '201808M', '201809M', '2018101', '201811M', '2018121', '2018122', '201901M', '201902M', '201903M', '2019041', '201905M', '201906M', '2019071', '201908M', '201909M', '2019101', '201911M', '2019121', '2019122', '202001M', '202002M', '202003M', '2020041', '202005M', '202006M', '2020071', '202008M', '8105A', '8107A', '8108A', '8109A', '8110A', '8111A', '8112A', '8201A', '8202A', '8203A', '8204A', '8205A', '8206A', '8207A', '8208A', '8212A', '8301A', '8302A', '8303A', '8303B', '8304A', '8305A', '8306A', '8307A', '8308A', '8309A', '8310A', '8311A', '8312A', '8402A', '8403A', '8404A', '8405A', '8406A', '8407A', '8408A', '8409A', '8410A', '8411A', '8412A', '8501A', '8502A', '8503A', '8505A', '8506A', '8507A', '8508A', '8509A', '8510A', '8511A', '8512A', '8601A', '8602A', '8603A', '8604A', '8605A', '8606A', '8607A', '8608A', '8609A', '8610A', '8611A', '8612A', '8701A', '8702A', '8703A', '8704A', '8705A', '8706A', '8707A', '8708A', '8709A', '8710A', '8711A', '8712A', '8801A', '8802A', '8803A', '8805A', '8806A', '8807A', '8808A', '8809A', '8810A', '8811A', '8812A', '8901A', '8902A', '8903A', '8904A', '8905A', '8906A', '8907A', '8908A', '8909A', '8910A', '8911A', '8912A', '9001A', '9002A', '9003A', '9004A', '9005A', '9006A', '9007A', '9008A', '9009A', '9010A', '9011A', '9011B', '9012C', '9101A', '9102B', '9103C', '9104C', '9105B', '9106B', '9107B', '9108A', '9108B', '9108C', '9109B', '9109C', '9110B', '9111A', '9111B', '9112C', '9112D', '9201A', '9201B', '9202A', '9202C', '9203D', '9205B', '9206D', '9207B', '9207C', '9208A', '9209B', '9209C', '9209E', '9210A', '9211B', '9211E', '9211F', '9212A', '9212B', '9301A', '9301B', '9302A', '9302B', '9303B', '9305A', '9305B', '9306B', '9306C', '9307A', '9308A', '9309A', '9310A', '9311A', '9311B', '9312B', '9401B', '9402B', '9403A', '9403B', '9405B', '9406A', '9407A', '9408A', '9408B', '9409A', '9410B', '9412A', '9501A', '9501B', '9502B', '9503A', '9504A', '9504B', '9505A', '9506B', '9507A', '9507B', '9508A', '9508B', '9509A', '9511A', '9511B', '9512A', '9512B', '9601A', '9601C', '9602A', '9602B', '9603A', '9603B', '9604A', '9604B', '9605B', '9606A', '9606B', '9607A', '9607B', '9608A', '9608B', '9609A', '9609B', '9610A', '9610B', '9611A', '9611B', '9612A', '9612B', '9701A', '9702A', '9702B', '9703A', '9704A', '9704B', '9705A', '9705B', '9705C', '9706A', '9707A', '9707B', '9708A', '9709A', '9710B', '9711A', '9711B', '9712B', '9802A', '9802B', '9803A', '9803B', '9804A', '9804B', '9805A', '9805B', '9806A', '9806B', '9807A', '9807B', '9808A', '9808B', '9809A', '9809B', '9810A', '9810B', '9811A', '9811B', '9901A', '9901B', '9902B', '9903A', '9903B', '9904A', '9904B', '9905A', '9905B', '9906A', '9906B', '9907A', '9907B', '9908B', '9909A', '9909B', '9910A', '9910B', '9911A', '9911B', '9912A', '9912B')
AND workkey NOT IN (select workkey from "current_all_ice_source".iacwrk where wrkisref = 'Y')
GROUP BY workkey
),
ibmMCPSlastDistRefPerf AS (
SELECT workkey as workkeyIBM, array_sort(array_distinct(array_agg(upper(wrkatr4)))) AS ibmLastDistRefPerf
FROM "current_all_ice_source".iacwlc
WHERE soccode = '044'
AND wrkatr4 IN
('2004071', '2004101', '2004121', '2005041', '2005071', '2005101', '2005121', '2005122', '2006041', '2006071', '2006101', '2006121', '2007041', '2007071', '2007101', '2007121', '2008041', '2008071', '2008101', '2008121', '2009041', '2009071', '2009101', '2009121', '2010041', '2010071', '2010101', '2010121', '2011041', '2011071', '2011101', '2011121', '2012041', '2012071', '2012101', '2012121', '2012122', '2013041', '2013071', '2013101', '2013121', '2013122', '2014041', '2014071', '2014101', '2014121', '2015041', '2015071', '2015101', '2015121', '2016041', '2016071', '2016101', '2016121', '2016122', '2017041', '2017071', '2017072', '2017101', '2017102', '2017121', '2017122', '2018041', '2018071', '2018101', '2018121', '2018122', '2019041', '2019071', '2019101', '2019121', '2019122', '2020041', '2020071')
AND workkey NOT IN (select workkey from "current_all_ice_source".iacwrk where wrkisref = 'Y')
GROUP BY workkey
),
ibmMCPSBroadcastInfo AS (
SELECT workkey as workkeyIBM, array_sort(array_distinct(array_agg(wrkatr5))) AS ibmBroadcastInfo
FROM "current_all_ice_source".iacwlc
WHERE soccode = '044'
AND workkey NOT IN (select workkey from "current_all_ice_source".iacwrk where wrkisref = 'Y') AND wrkatr5 <> ' ' AND wrkatr5 IS NOT NULL AND wrkatr5 <> ''
GROUP BY workkey
),
ibmMCPSAll AS (
SELECT * from ibmMCPSarrangementGrade
FULL JOIN ibmMCPSdramaticoMusical USING(workkeyIBM)
FULL JOIN ibmMCPSlastDistRefMech USING(workkeyIBM)
FULL JOIN ibmMCPSlastDistRefPerf USING(workkeyIBM)
FULL JOIN ibmMCPSBroadcastInfo USING(workkeyIBM)
WHERE workkeyIBM NOT IN (select workkey from "current_all_ice_source".iacwrk where wrkisref = 'Y')
),
cubeMCPSarrangementGrade as (
SELECT split_part(workid, ':', 2) AS workkeyCube, array_sort(attributes['arrangementGrade']) AS cubeArrangementGrade
FROM "andriusg_mid-20".worksociety
WHERE societyid = 'CISAC:44' AND ns = 'ICE' AND cardinality(array_sort(attributes['arrangementGrade'])) IS NOT NULL
),
cubeMCPSdramaticoMusical AS (
SELECT split_part(workid, ':', 2) AS workkeyCube, array_sort(attributes['dramaticoMusical']) AS cubeDramaticoMusical
FROM "andriusg_mid-20".worksociety
WHERE societyid = 'CISAC:44' AND ns = 'ICE' AND cardinality(array_sort(attributes['dramaticoMusical'])) IS NOT NULL
),
cubeMCPSlastDistRefMech AS (
SELECT split_part(workid, ':', 2) AS workkeyCube, array_sort(attributes['lastDistRefMech']) AS cubeLastDistRefMech
FROM "andriusg_mid-20".worksociety
WHERE societyid = 'CISAC:44' AND ns = 'ICE' AND cardinality(array_sort(attributes['lastDistRefMech'])) IS NOT NULL
),
cubeMCPSLastDistRefPerf AS (
SELECT split_part(workid, ':', 2) AS workkeyCube, array_sort(attributes['lastDistRefPerf']) AS cubeLastDistRefPerf
FROM "andriusg_mid-20".worksociety
WHERE societyid = 'CISAC:44' AND ns = 'ICE' AND cardinality(array_sort(attributes['lastDistRefPerf'])) IS NOT NULL
),
cubeMCPSBroadcastInfo AS (
SELECT split_part(workid, ':', 2) AS workkeyCube, k.*, array_sort(attributes['broadcastInfo']) AS cubeBroadcastInfo
FROM "andriusg_mid-20".worksociety k
WHERE societyid = 'CISAC:44' AND ns = 'ICE' AND cardinality(array_sort(attributes['broadcastInfo'])) IS NOT NULL
),
cubeMCPSAll AS (
SELECT * FROM cubeMCPSarrangementGrade
FULL JOIN cubeMCPSdramaticoMusical USING(workkeyCube)
FULL JOIN cubeMCPSlastDistRefMech USING(workkeyCube)
FULL JOIN cubeMCPSLastDistRefPerf USING(workkeyCube)
FULL JOIN cubeMCPSBroadcastInfo USING(workkeyCube)
)
SELECT * FROM cubeMCPSAll
FULL JOIN ibmMCPSAll ON workkeyIBM = workkeyCube
WHERE cubeArrangementGrade <> ibmArrangementGrade OR cubeDramaticoMusical <> ibmDramaticoMusical OR cubeLastDistRefMech <> ibmLastDistRefMech
OR cubeLastDistRefPerf <> ibmLastDistRefPerf OR cubeBroadcastInfo <> ibmBroadcastInfo OR workkeyIBM IS NULL OR workkeyCube IS NULL
LIMIT 100;Editor is loading...