SELECT
NVL(BTU.BTU_CODTAB, BTQ.BTQ_CODTAB) AS TabelaProcedimento,
NVL(BTU.BTU_CDTERM, BTQ.BTQ_CDTERM) AS Procedimento,
DenteRegiao,
Face
FROM (
SELECT
BYL_CODPAD TabelaProcedimento,
BYL_CODPSA Procedimento,
BYL_CODIGO DenteRegiao,
SUBSTR(BYL_FACE, numbers.n, 1) Face
FROM
(SELECT 1 n FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 3 FROM DUAL UNION ALL SELECT 4 FROM DUAL UNION ALL SELECT 5 FROM DUAL) numbers
INNER JOIN BYL010 BYL ON LENGTH(BYL.BYL_FACE)>numbers.n-1
WHERE
BYL_FACE <> ' '
AND SUBSTR(BYL_FACE, numbers.n, 1) <> ' '
AND D_E_L_E_T_ = ' '
AND D_E_L_E_T_ = ' '
UNION ALL
SELECT
B05_CODPAD TabelaProcedimento,
B05_CODPSA Procedimento,
B05_CODIGO DenteRegiao,
' ' Face
FROM B05010
WHERE
B05_CODPSA NOT IN (SELECT BYL_CODPSA FROM BYL010)
AND D_E_L_E_T_ = '
'
AND D_E_L_E_T_ = ' '
) temp
LEFT JOIN BTU010 BTU ON (BTU.BTU_VLRBUS = Procedimento AND BTU.D_E_L_E_T_ = ' ')
LEFT JOIN BTQ010 BTQ ON (BTQ.BTQ_CDTERM = Procedimento AND BTQ.D_E_L_E_T_ = ' ')
WHERE
(BTQ_CODTAB IS NULL OR BTQ_CODTAB IN ('18', '19', '20', '22', '90', '98'))
AND (BTU_CODTAB IS NULL OR BTU_CODTAB IN ('18', '19', '20', '22', '90', '98'))
AND ((BTU_CODTAB IS NOT NULL AND BTU_CDTERM IS NOT NULL) OR (BTQ_CODTAB IS NOT NULL AND BTQ_CDTERM IS NOT NULL))
GROUP BY NVL(BTU.BTU_CODTAB, BTQ.BTQ_CODTAB),NVL(BTU.BTU_CDTERM, BTQ.BTQ_CDTERM),DenteRegiao,Face
|