Ajuda em uma query

17/02/2020

0

SQL Server SQL

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
Isnar Filho

Isnar Filho

Responder

Posts

17/02/2020

Emerson Nascimento

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
Responder
Gostei + 0

17/02/2020

Anderson Gonçalves

Cara entra em contato comigo ai, seu caso demanda atenção maior.

Skype: anderson@case13.com.br
Responder
Gostei + 0

17/02/2020

Isnar Filho

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
Responder
Gostei + 0

Assista grátis a nossa aula inaugural

Assitir aula

Saiba por que programar é uma questão de
sobrevivência e como aprender sem riscos

Assistir agora

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar