Criar consulta dos dados de qualidade disponíveis no CSW

[MarconeRodrigues]

segue exemplo de uma consulta que tenho, caso não for neste formato que você queira fique a vontade para usar a tabela bcs-csw-core.core.inspct_rslt de resultados para criar sua tabela no formato desejado.

SELECT inspct_rslt.inspct_lot_nbr AS IL, MAX(CASE WHEN inspct_char.mstr_inspct_char_nbr = '50120008' AND inspct_rslt.mean_val > 0 AND inspct_char.mstr_inspct_char_nbr = '50120009' AND inspct_rslt.mean_val > 0 then NULL WHEN inspct_char.mstr_inspct_char_nbr = '50120008' AND inspct_rslt.mean_val > 0 THEN 'DEFINITIVA' WHEN inspct_char.mstr_inspct_char_nbr = '50120009' AND inspct_rslt.mean_val > 0 THEN 'PRÉVIA' ELSE NULL END) AS TIPO_AMOSTRA, MAX(CASE WHEN (inspct_char.mstr_inspct_char_nbr = '50120008' AND inspct_rslt.mean_val > 0) AND (inspct_char.mstr_inspct_char_nbr = '50120009' AND inspct_rslt.mean_val > 0) THEN NULL ELSE CONCAT(CAST(inspct_rslt.mean_val AS int), CASE WHEN inspct_char.mstr_inspct_char_nbr = '50120008' AND inspct_rslt.mean_val > 0 THEN '/AD/MG/' WHEN inspct_char.mstr_inspct_char_nbr = '50120009' AND inspct_rslt.mean_val > 0 THEN '/AP/MG/' END, EXTRACT(YEAR FROM inspct_rslt.inspct_start_dt)) END) AS NUMERO_AMOSTRA, CONCAT(COALESCE(MAX(CASE WHEN inspct_char.mstr_inspct_char_nbr = '50120008' AND inspct_rslt.mean_val > 0 THEN inspct_rslt.inspct_start_dt ELSE NULL END), MAX(CASE WHEN inspct_char.mstr_inspct_char_nbr = '50120009' AND inspct_rslt.mean_val > 0 THEN inspct_rslt.inspct_start_dt ELSE NULL END))) AS INSPCT_DT_NUMERO_AMOSTRA, LPAD(CAST(MAX(CASE WHEN inspct_char.mstr_inspct_char_nbr = '50120016' AND inspct_rslt.mean_val IS NOT NULL THEN CAST(inspct_rslt.mean_val AS int) ELSE NULL END) AS string), 8, '0') AS DATA_RECEBIMENTO_REAL,

-- Modificar aqui as MICs desejadas MAX(CASE WHEN inspct_rslt.mean_val is not null AND inspct_char.mstr_inspct_char_nbr = '50125148' THEN inspct_rslt.mean_val ELSE NULL END) AS GERMINACAO_FINAL, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50125148' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_GERMINACAO_FINAL, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50125150' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_VIGOR_FINAL, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50125149' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_HERBICIDA_FINAL, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50120041' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_SEMENTE_PURA_SP_PERCENTIL, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr IN ('50125384', '50120038', '50140197', '50125406', '50125386') AND inspct_rslt.inspct_end_dt NOT IN ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_TOTAL_DEFEITOS_PERCENTIL_LAB, MAX(CASE WHEN inspct_rslt.cd_nm_1 <>'' AND inspct_char.mstr_inspct_char_nbr = '50120361' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.cd_nm_1 ELSE NULL END) AS JUSTIFICATIVAS_LASP, MAX(CASE WHEN inspct_rslt.cd_nm_1 <>'' AND inspct_char.mstr_inspct_char_nbr = '50120361' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_JUSTIFICATIVAS_LASP, MAX(CASE WHEN inspct_rslt.cd_nm_1 <>'' AND inspct_char.mstr_inspct_char_nbr = '50120003' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_HOMOGENEIZACAO, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50125215' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_EA_MEDIA_FINAL, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50120235' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_HPLC_THIAMETOXAM, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50121063' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_HPLC_CLOTHIANIDIN, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50121063' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_HPLC_PONCHO, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50121296' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_HPLC_CLORANTRANILIPROLE, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50125203' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_HPLC_THIRAM, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50125205' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_HPLC_THIABENDAZOLE, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50125403' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_HPLC_PROTHIOCONAZOLE, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50125409' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_HPLC_IMIDACLOPRID, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50120012' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_UMIDADE_ESTUFA, MAX(CASE WHEN inspct_rslt.mean_val IS NOT NULL AND inspct_char.mstr_inspct_char_nbr = '50120017' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_INFESTACAO_PERCENTIL, MAX(CASE WHEN inspct_rslt.cd_nm_1 <>'' AND inspct_char.mstr_inspct_char_nbr = '50120065' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_DOSN_AVALIADO_POR, MAX(CASE WHEN inspct_rslt.cd_nm_1 <>'' AND inspct_char.mstr_inspct_char_nbr = '50121206' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_AVALIACAO_CMS, MAX(CASE WHEN inspct_rslt.cd_nm_1 <>'' AND inspct_char.mstr_inspct_char_nbr = '50120181' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_AVALIACAO_PUREZA_DO_EVENTO, MAX(CASE WHEN inspct_rslt.cd_nm_1 <>'' AND inspct_char.mstr_inspct_char_nbr = '50121439' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_AVALIACAO_MIR162, MAX(CASE WHEN inspct_rslt.cd_nm_1 <>'' AND inspct_char.mstr_inspct_char_nbr = '50120182' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_AVALIACAO_SNP, MAX(CASE WHEN inspct_rslt.cd_nm_1 <>'' AND inspct_char.mstr_inspct_char_nbr = '50059979' AND inspct_rslt.inspct_end_dt not in ('1900-01-01') THEN inspct_rslt.inspct_end_dt ELSE NULL END) AS DATA_ZIGOSITY_CORN_STATUS,

FROM bcs-csw-core.core.inspct_rslt inspct_rslt INNER JOIN bcs-csw-core.core.inspct_char inspct_char ON inspct_char.inspct_lot_nbr = inspct_rslt.inspct_lot_nbr AND inspct_rslt.inspct_char_nbr = inspct_char.inspct_char_nbr AND inspct_char.prcs_ord_oper_line_nbr = inspct_rslt.prcs_ord_oper_line_nbr AND inspct_char.sap_src_sys_cd = inspct_rslt.sap_src_sys_cd AND inspct_rslt.rslt_rec_sts_ind IN ('3','4', '5','6','7','8') --Status de registro do resultado(0 = Pode ser processado; 1 = Tem de ser processado; 2 = Procesado; 3 = Avaliado;...) AND inspct_rslt.sap_src_sys_cd = 'x08' --x08=P08 xbc=PBC AND (inspct_rslt.cd_nm_1 <> '' OR inspct_rslt.mean_val IS NOT NULL) AND inspct_rslt.inspct_start_dt BETWEEN DATE('2023-01-01') AND DATE('2025-12-31')

-- AND inspct_rslt.inspct_lot_nbr IN( -- '090003098768', -- '040006738098', -- '090003098124', -- '090003145520', -- '080002925904', -- '050001692567' -- )

AND inspct_char.mstr_inspct_char_nbr IN ( '50120008', '50120009', '50120016',

'50125148', '50125150', '50125149', '50120041', '50125384', '50120361', '50120003', '50125215', '50121266', '50120235', '50121063', '50121296', '50125203', '50125205', '50125403', '50125409', '50120012', '50120017', '50120065', '50120068', '50120106', '50120032', '50120078', '50120221', '50120087', '50120020', '50121446', '50140196', '50140198', '50140202', '50125207', '50125208', '50120224', '50121072', '50120182', '50120181', '50121439', '50121206', '50140197', '50120038', '50125401', '50121497', '50125410', '50125402', '50121496', '50121205', '50059998', '50059979', '50140214', '50125357', '50125228', '50120367', '50059785', '50125386', '50140125', '50120346', '50125406' ) group by inspct_rslt.inspct_lot_nbr

[MarconeRodrigues]

note que quando for valores quantitativos é usado mean_val quando for qualitativo é usado cd_nm_1

[MarconeRodrigues]

parte de DU vc encontra nessa tabela bcs-csw-core.core.inspct_lot_usg_decsn_log

CASE WHEN inspct_lot_usg_decsn_log.chng_dt <> '1900-01-01' AND inspct_lot_usg_decsn_log.chng_dt IS NOT NULL THEN inspct_lot_usg_decsn_log.chng_dt WHEN inspct_lot_usg_decsn_log.crtd_dt IS NULL OR inspct_lot_usg_decsn_log.crtd_dt = '1900-01-01' THEN NULL ELSE inspct_lot_usg_decsn_log.crtd_dt END AS DATA_DU,