Ajuda em uma query
Boa tarde, pessoal,
Estou com duvida em um relatório que estou fazendo.
um relatório de recebimentos onde para um determinado cnv ele não traga 3 classes (CTF.CTF_COD NOT IN (''100'', ''2000'', ''300'')) e para todos os demais traga todas as classes. (Devo ter feito errado mas tentei case e não deu ai tentei union com uma select trazendo todas menos esse determinado cnv e a segunda so essa cnv mas ele da um erro perto da union).
SELECT STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
MIN ( SMM.SMM_VLR ),
CV.CNV_COD CNV_COD_CV,
CV.CNV_NOME CNV_NOME_CV,
OSM.OSM_SERIE,
OSM.OSM_NUM,
SUM ( EXT.EXT_VALOR_RECEB ),
SUM ( EXT.EXT_VALOR_GLOSA ),
PSV.PSV_NOME
FROM PAC, SMM, STR, OSM, CTF, SMK, CNV CV, EXT, PSV, MNS
WHERE ( SMM.SMM_SFAT <> ''C'' ) AND
( OSM.OSM_SERIE = SMM.SMM_OSM_SERIE ) AND
( OSM.OSM_NUM = SMM.SMM_OSM ) AND
( ( ( OSM.OSM_CNV = CV.CNV_COD AND SMM.SMM_CTH_NUM IS NULL ) OR ( SMM.SMM_CNV_COD = CV.CNV_COD AND SMM.SMM_CTH_NUM IS NOT NULL ) ) ) AND
( STR.STR_COD = SMM.SMM_STR ) AND
( SMK.SMK_TIPO = SMM.SMM_TPCOD ) AND
( SMK.SMK_COD = SMM.SMM_COD ) AND
( SMK.SMK_TIPO = CTF.CTF_TIPO ) AND
( SMK.SMK_CTF = CTF.CTF_COD ) AND
( OSM.OSM_PAC = PAC.PAC_REG ) AND
( PSV.PSV_COD = SMM.SMM_MED ) AND
( EXT.EXT_OSM_SERIE = SMM.SMM_OSM_SERIE ) AND
( EXT.EXT_OSM_NUM = SMM.SMM_OSM ) AND
( EXT.EXT_SMM_NUM = SMM.SMM_NUM ) AND
( EXT.EXT_MNS_NUM = MNS.MNS_NUM ) AND
( EXT.EXT_MNS_SERIE = MNS.MNS_SERIE ) AND
( MNS.MNS_IND_LIBERADO = ''S'' ) AND
( SMM.SMM_COD not in ( ''00731121'',''00230107'',''00731135'',''00731136'',''00731133'',''AJUSTE'',''00731137'',''00731138'',''00731139'',''00731140'',''00731141 '' ) ) AND
( CV.CNV_CAIXA_FATURA <> ''C'' ) AND
( CV.CNV_COD <> ''107'' ) AND
( ( EXT.EXT_DT_MOV >= ''2020-01-01 00:00:00'' AND
EXT.EXT_DT_MOV < ''2020-01-31 23:59:59'' ) ) AND
( SMM.SMM_STR = ''085'' ) AND
( SMM.SMM_COD <> ''COPARTIC'' ) AND
( MNS.MNS_TIPO <> ''NR'' )
GROUP BY STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
CV.CNV_COD,
CV.CNV_NOME,
OSM.OSM_SERIE,
OSM.OSM_NUM,
PSV.PSV_NOME
ORDER BY OSM.OSM_NUM asc, STR.STR_NOME ASC,
CV.CNV_NOME ASC,
CTF.CTF_NOME ASC
UNION
SELECT STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
MIN ( SMM.SMM_VLR ),
CV.CNV_COD CNV_COD_CV,
CV.CNV_NOME CNV_NOME_CV,
OSM.OSM_SERIE,
OSM.OSM_NUM,
SUM ( EXT.EXT_VALOR_RECEB ),
SUM ( EXT.EXT_VALOR_GLOSA ),
PSV.PSV_NOME
FROM PAC, SMM, STR, OSM, CTF, SMK, CNV CV, EXT, PSV, MNS
WHERE ( SMM.SMM_SFAT <> ''C'' ) AND
( OSM.OSM_SERIE = SMM.SMM_OSM_SERIE ) AND
( OSM.OSM_NUM = SMM.SMM_OSM ) AND
( ( ( OSM.OSM_CNV = CV.CNV_COD AND SMM.SMM_CTH_NUM IS NULL ) OR ( SMM.SMM_CNV_COD = CV.CNV_COD AND SMM.SMM_CTH_NUM IS NOT NULL ) ) ) AND
( STR.STR_COD = SMM.SMM_STR ) AND
( SMK.SMK_TIPO = SMM.SMM_TPCOD ) AND
( SMK.SMK_COD = SMM.SMM_COD ) AND
( SMK.SMK_TIPO = CTF.CTF_TIPO ) AND
( SMK.SMK_CTF = CTF.CTF_COD ) AND
( OSM.OSM_PAC = PAC.PAC_REG ) AND
( PSV.PSV_COD = SMM.SMM_MED ) AND
( EXT.EXT_OSM_SERIE = SMM.SMM_OSM_SERIE ) AND
( EXT.EXT_OSM_NUM = SMM.SMM_OSM ) AND
( EXT.EXT_SMM_NUM = SMM.SMM_NUM ) AND
( EXT.EXT_MNS_NUM = MNS.MNS_NUM ) AND
( EXT.EXT_MNS_SERIE = MNS.MNS_SERIE ) AND
( MNS.MNS_IND_LIBERADO = ''S'' ) AND
( SMM.SMM_COD not in ( ''00731121'',''00230107'',''00731135'',''00731136'',''00731133'',''AJUSTE'',''00731137'',''00731138'',''00731139'',''00731140'',''00731141 '' ) ) AND
( CV.CNV_CAIXA_FATURA <> ''C'' ) AND
( CV.CNV_COD = ''107'' ) AND
( ( EXT.EXT_DT_MOV >= ''2020-01-01 00:00:00'' AND
EXT.EXT_DT_MOV < ''2020-01-31 23:59:59'' ) ) AND
( SMM.SMM_STR = ''085'' ) AND
( SMM.SMM_COD <> ''COPARTIC'' ) AND
( MNS.MNS_TIPO <> ''NR'' ) AND
( CTF.CTF_COD NOT IN (''100'', ''2000'', ''300''))
GROUP BY STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
CV.CNV_COD,
CV.CNV_NOME,
OSM.OSM_SERIE,
OSM.OSM_NUM,
PSV.PSV_NOME
ORDER BY OSM.OSM_NUM asc, STR.STR_NOME ASC,
CV.CNV_NOME ASC,
CTF.CTF_NOME ASC
Estou com duvida em um relatório que estou fazendo.
um relatório de recebimentos onde para um determinado cnv ele não traga 3 classes (CTF.CTF_COD NOT IN (''100'', ''2000'', ''300'')) e para todos os demais traga todas as classes. (Devo ter feito errado mas tentei case e não deu ai tentei union com uma select trazendo todas menos esse determinado cnv e a segunda so essa cnv mas ele da um erro perto da union).
SELECT STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
MIN ( SMM.SMM_VLR ),
CV.CNV_COD CNV_COD_CV,
CV.CNV_NOME CNV_NOME_CV,
OSM.OSM_SERIE,
OSM.OSM_NUM,
SUM ( EXT.EXT_VALOR_RECEB ),
SUM ( EXT.EXT_VALOR_GLOSA ),
PSV.PSV_NOME
FROM PAC, SMM, STR, OSM, CTF, SMK, CNV CV, EXT, PSV, MNS
WHERE ( SMM.SMM_SFAT <> ''C'' ) AND
( OSM.OSM_SERIE = SMM.SMM_OSM_SERIE ) AND
( OSM.OSM_NUM = SMM.SMM_OSM ) AND
( ( ( OSM.OSM_CNV = CV.CNV_COD AND SMM.SMM_CTH_NUM IS NULL ) OR ( SMM.SMM_CNV_COD = CV.CNV_COD AND SMM.SMM_CTH_NUM IS NOT NULL ) ) ) AND
( STR.STR_COD = SMM.SMM_STR ) AND
( SMK.SMK_TIPO = SMM.SMM_TPCOD ) AND
( SMK.SMK_COD = SMM.SMM_COD ) AND
( SMK.SMK_TIPO = CTF.CTF_TIPO ) AND
( SMK.SMK_CTF = CTF.CTF_COD ) AND
( OSM.OSM_PAC = PAC.PAC_REG ) AND
( PSV.PSV_COD = SMM.SMM_MED ) AND
( EXT.EXT_OSM_SERIE = SMM.SMM_OSM_SERIE ) AND
( EXT.EXT_OSM_NUM = SMM.SMM_OSM ) AND
( EXT.EXT_SMM_NUM = SMM.SMM_NUM ) AND
( EXT.EXT_MNS_NUM = MNS.MNS_NUM ) AND
( EXT.EXT_MNS_SERIE = MNS.MNS_SERIE ) AND
( MNS.MNS_IND_LIBERADO = ''S'' ) AND
( SMM.SMM_COD not in ( ''00731121'',''00230107'',''00731135'',''00731136'',''00731133'',''AJUSTE'',''00731137'',''00731138'',''00731139'',''00731140'',''00731141 '' ) ) AND
( CV.CNV_CAIXA_FATURA <> ''C'' ) AND
( CV.CNV_COD <> ''107'' ) AND
( ( EXT.EXT_DT_MOV >= ''2020-01-01 00:00:00'' AND
EXT.EXT_DT_MOV < ''2020-01-31 23:59:59'' ) ) AND
( SMM.SMM_STR = ''085'' ) AND
( SMM.SMM_COD <> ''COPARTIC'' ) AND
( MNS.MNS_TIPO <> ''NR'' )
GROUP BY STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
CV.CNV_COD,
CV.CNV_NOME,
OSM.OSM_SERIE,
OSM.OSM_NUM,
PSV.PSV_NOME
ORDER BY OSM.OSM_NUM asc, STR.STR_NOME ASC,
CV.CNV_NOME ASC,
CTF.CTF_NOME ASC
UNION
SELECT STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
MIN ( SMM.SMM_VLR ),
CV.CNV_COD CNV_COD_CV,
CV.CNV_NOME CNV_NOME_CV,
OSM.OSM_SERIE,
OSM.OSM_NUM,
SUM ( EXT.EXT_VALOR_RECEB ),
SUM ( EXT.EXT_VALOR_GLOSA ),
PSV.PSV_NOME
FROM PAC, SMM, STR, OSM, CTF, SMK, CNV CV, EXT, PSV, MNS
WHERE ( SMM.SMM_SFAT <> ''C'' ) AND
( OSM.OSM_SERIE = SMM.SMM_OSM_SERIE ) AND
( OSM.OSM_NUM = SMM.SMM_OSM ) AND
( ( ( OSM.OSM_CNV = CV.CNV_COD AND SMM.SMM_CTH_NUM IS NULL ) OR ( SMM.SMM_CNV_COD = CV.CNV_COD AND SMM.SMM_CTH_NUM IS NOT NULL ) ) ) AND
( STR.STR_COD = SMM.SMM_STR ) AND
( SMK.SMK_TIPO = SMM.SMM_TPCOD ) AND
( SMK.SMK_COD = SMM.SMM_COD ) AND
( SMK.SMK_TIPO = CTF.CTF_TIPO ) AND
( SMK.SMK_CTF = CTF.CTF_COD ) AND
( OSM.OSM_PAC = PAC.PAC_REG ) AND
( PSV.PSV_COD = SMM.SMM_MED ) AND
( EXT.EXT_OSM_SERIE = SMM.SMM_OSM_SERIE ) AND
( EXT.EXT_OSM_NUM = SMM.SMM_OSM ) AND
( EXT.EXT_SMM_NUM = SMM.SMM_NUM ) AND
( EXT.EXT_MNS_NUM = MNS.MNS_NUM ) AND
( EXT.EXT_MNS_SERIE = MNS.MNS_SERIE ) AND
( MNS.MNS_IND_LIBERADO = ''S'' ) AND
( SMM.SMM_COD not in ( ''00731121'',''00230107'',''00731135'',''00731136'',''00731133'',''AJUSTE'',''00731137'',''00731138'',''00731139'',''00731140'',''00731141 '' ) ) AND
( CV.CNV_CAIXA_FATURA <> ''C'' ) AND
( CV.CNV_COD = ''107'' ) AND
( ( EXT.EXT_DT_MOV >= ''2020-01-01 00:00:00'' AND
EXT.EXT_DT_MOV < ''2020-01-31 23:59:59'' ) ) AND
( SMM.SMM_STR = ''085'' ) AND
( SMM.SMM_COD <> ''COPARTIC'' ) AND
( MNS.MNS_TIPO <> ''NR'' ) AND
( CTF.CTF_COD NOT IN (''100'', ''2000'', ''300''))
GROUP BY STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
CV.CNV_COD,
CV.CNV_NOME,
OSM.OSM_SERIE,
OSM.OSM_NUM,
PSV.PSV_NOME
ORDER BY OSM.OSM_NUM asc, STR.STR_NOME ASC,
CV.CNV_NOME ASC,
CTF.CTF_NOME ASC
Isnar Filho
Curtidas 0
Respostas
Emerson Nascimento
17/02/2020
tente algo assim:
SELECT
STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
MIN( SMM.SMM_VLR ),
CV.CNV_COD CNV_COD_CV,
CV.CNV_NOME CNV_NOME_CV,
OSM.OSM_SERIE,
OSM.OSM_NUM,
SUM( EXT.EXT_VALOR_RECEB ),
SUM( EXT.EXT_VALOR_GLOSA ),
PSV.PSV_NOME
FROM PAC
INNER JOIN OSM ON ( OSM.OSM_PAC = PAC.PAC_REG )
INNER JOIN SMM ON ( SMM.SMM_OSM_SERIE = OSM.OSM_SERIE )
AND ( SMM.SMM_OSM = OSM.OSM_NUM )
INNER JOIN STR ON ( STR.STR_COD = SMM.SMM_STR )
INNER JOIN SMK ON ( SMK.SMK_TIPO = SMM.SMM_TPCOD )
AND ( SMK.SMK_COD = SMM.SMM_COD )
INNER JOIN CTF ON ( CTF.CTF_TIPO = SMK.SMK_TIPO )
AND ( CTF.CTF_COD = SMK.SMK_CTF )
INNER JOIN CNV CV ON ( ( CV.CNV_COD = OSM.OSM_CNV AND SMM.SMM_CTH_NUM IS NULL ) OR ( CV.CNV_COD = SMM.SMM_CNV_COD AND SMM.SMM_CTH_NUM IS NOT NULL ) )
INNER JOIN EXT ON ( EXT.EXT_OSM_SERIE = SMM.SMM_OSM_SERIE )
AND ( EXT.EXT_OSM_NUM = SMM.SMM_OSM )
AND ( EXT.EXT_SMM_NUM = SMM.SMM_NUM )
INNER JOIN PSV ON ( PSV.PSV_COD = SMM.SMM_MED )
INNER JOIN MNS ON ( MNS.MNS_NUM = EXT.EXT_MNS_NUM )
AND ( MNS.MNS_SERIE = EXT.EXT_MNS_SERIE )
WHERE
( SMM.SMM_SFAT <> 'C' )
AND NOT ( SMM.SMM_COD IN ( '00731121','00230107','00731135','00731136','00731133','AJUSTE','00731137','00731138','00731139','00731140','00731141 ' ) )
AND ( SMM.SMM_STR = '085' )
AND ( SMM.SMM_COD <> 'COPARTIC' )
AND ( CV.CNV_CAIXA_FATURA <> 'C' )
AND ( ( CV.CNV_COD <> '107' ) OR NOT ( CTF.CTF_COD IN ('100', '2000', '300') ) ) -- *** é isso mesmo??? CEM, 2 MIL, TREZENTOS ***
AND ( EXT.EXT_DT_MOV BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59' )
AND ( MNS.MNS_IND_LIBERADO = 'S' )
AND ( MNS.MNS_TIPO <> 'NR' )
GROUP BY
STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
CV.CNV_COD,
CV.CNV_NOME,
OSM.OSM_SERIE,
OSM.OSM_NUM,
PSV.PSV_NOME
ORDER BY
OSM.OSM_NUM ASC,
STR.STR_NOME ASC,
CV.CNV_NOME ASC,
CTF.CTF_NOME ASC
GOSTEI 0
Anderson Gonçalves
17/02/2020
Cara entra em contato comigo ai, seu caso demanda atenção maior.
Skype: anderson@case13.com.br
Skype: anderson@case13.com.br
GOSTEI 0
Isnar Filho
17/02/2020
Meu nobre, boa tarde. Testei aqui, o código rodou, vou testar no banco real agora para comparar se os resultados estão batendo e entro em contato contigo, valeu? Mas desde já agradeço pela ajuda, irmão.
tente algo assim:
SELECT
STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
MIN( SMM.SMM_VLR ),
CV.CNV_COD CNV_COD_CV,
CV.CNV_NOME CNV_NOME_CV,
OSM.OSM_SERIE,
OSM.OSM_NUM,
SUM( EXT.EXT_VALOR_RECEB ),
SUM( EXT.EXT_VALOR_GLOSA ),
PSV.PSV_NOME
FROM PAC
INNER JOIN OSM ON ( OSM.OSM_PAC = PAC.PAC_REG )
INNER JOIN SMM ON ( SMM.SMM_OSM_SERIE = OSM.OSM_SERIE )
AND ( SMM.SMM_OSM = OSM.OSM_NUM )
INNER JOIN STR ON ( STR.STR_COD = SMM.SMM_STR )
INNER JOIN SMK ON ( SMK.SMK_TIPO = SMM.SMM_TPCOD )
AND ( SMK.SMK_COD = SMM.SMM_COD )
INNER JOIN CTF ON ( CTF.CTF_TIPO = SMK.SMK_TIPO )
AND ( CTF.CTF_COD = SMK.SMK_CTF )
INNER JOIN CNV CV ON ( ( CV.CNV_COD = OSM.OSM_CNV AND SMM.SMM_CTH_NUM IS NULL ) OR ( CV.CNV_COD = SMM.SMM_CNV_COD AND SMM.SMM_CTH_NUM IS NOT NULL ) )
INNER JOIN EXT ON ( EXT.EXT_OSM_SERIE = SMM.SMM_OSM_SERIE )
AND ( EXT.EXT_OSM_NUM = SMM.SMM_OSM )
AND ( EXT.EXT_SMM_NUM = SMM.SMM_NUM )
INNER JOIN PSV ON ( PSV.PSV_COD = SMM.SMM_MED )
INNER JOIN MNS ON ( MNS.MNS_NUM = EXT.EXT_MNS_NUM )
AND ( MNS.MNS_SERIE = EXT.EXT_MNS_SERIE )
WHERE
( SMM.SMM_SFAT <> 'C' )
AND NOT ( SMM.SMM_COD IN ( '00731121','00230107','00731135','00731136','00731133','AJUSTE','00731137','00731138','00731139','00731140','00731141 ' ) )
AND ( SMM.SMM_STR = '085' )
AND ( SMM.SMM_COD <> 'COPARTIC' )
AND ( CV.CNV_CAIXA_FATURA <> 'C' )
AND ( ( CV.CNV_COD <> '107' ) OR NOT ( CTF.CTF_COD IN ('100', '2000', '300') ) ) -- *** é isso mesmo??? CEM, 2 MIL, TREZENTOS ***
AND ( EXT.EXT_DT_MOV BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59' )
AND ( MNS.MNS_IND_LIBERADO = 'S' )
AND ( MNS.MNS_TIPO <> 'NR' )
GROUP BY
STR.STR_NOME,
SMM.SMM_STR,
OSM.OSM_PAC,
PAC.PAC_NOME,
CTF.CTF_CATEG,
SMK.SMK_CTF,
CTF.CTF_NOME,
CV.CNV_COD,
CV.CNV_NOME,
OSM.OSM_SERIE,
OSM.OSM_NUM,
PSV.PSV_NOME
ORDER BY
OSM.OSM_NUM ASC,
STR.STR_NOME ASC,
CV.CNV_NOME ASC,
CTF.CTF_NOME ASC
GOSTEI 0