AJUDA COM RELATÓRIO IBEXPERT SQL
12/04/2019
0
PESSOAL, BOA TARDE, TENHO O RELATÓRIO ABAIXO SENDO EXECUTADO NO IBEXPERT, INFELIZMENTE NAO CONSIGO COLOCAR ANEXO AQUI PARA DEMONSTRAR COMO ESTA SAINDO, PRECISO SABER SE EXISTE A POSSIBILIDADE DE COLOCAR FILTRO PELO NOME DAS CARTEIRAS,
EXEMPLO,
SE EU SELECIONAR NO FILTRO O NOME "BOLETO", TRAZ SO O SELECT DOS BOLETOS,
SE SELECIONAR "CARTAO" SAI SÓ OS SELECTS DO CARTAO ETC.....
--BOLETO
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 22 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_3'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 24 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_FC_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 154 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_FC_3'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 155 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_MANUAL_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 53 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_MANUAL_FC_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 156 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_MANUAL_FC_3'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 157 AND R.rec_quitado = ''N''
UNION
--CARTAO
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''CARTAO_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 22 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''CARTAO_FC_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 24 AND R.rec_quitado = ''N''
UNION
--FICHA
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''FICHA_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 22 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''FICHA_3'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 24 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''FICHA_FC_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 22 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''FICHA_FC_3'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 24 AND R.rec_quitado = ''N''
EXEMPLO,
SE EU SELECIONAR NO FILTRO O NOME "BOLETO", TRAZ SO O SELECT DOS BOLETOS,
SE SELECIONAR "CARTAO" SAI SÓ OS SELECTS DO CARTAO ETC.....
--BOLETO
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 22 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_3'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 24 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_FC_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 154 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_FC_3'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 155 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_MANUAL_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 53 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_MANUAL_FC_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 156 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''BOLETO_MANUAL_FC_3'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 157 AND R.rec_quitado = ''N''
UNION
--CARTAO
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''CARTAO_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 22 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''CARTAO_FC_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 24 AND R.rec_quitado = ''N''
UNION
--FICHA
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''FICHA_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 22 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''FICHA_3'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 24 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''FICHA_FC_1'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 22 AND R.rec_quitado = ''N''
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, ''FICHA_FC_3'' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 24 AND R.rec_quitado = ''N''
Gustavo Baracca
Curtir tópico
+ 0
Responder
Posts
12/04/2019
Gustavo Baracca
CORRIGINDO O SELECT
CONSIDERAR ESSE ABAIXO
--BOLETO
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 22 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_3' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 24 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_FC_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 154 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_FC_3' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 155 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_MANUAL_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 53 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_MANUAL_FC_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 156 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_MANUAL_FC_3' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 157 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
--CARTAO
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'CARTAO_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 32 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'CARTAO_FC_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 102 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
--FICHA
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'FICHA_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 28 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'FICHA_3' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 23 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'FICHA_FC_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 98 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'FICHA_FC_3' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 96 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
CONSIDERAR ESSE ABAIXO
--BOLETO
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 22 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_3' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 24 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_FC_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 154 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_FC_3' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 155 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_MANUAL_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 53 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_MANUAL_FC_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 156 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'BOLETO_MANUAL_FC_3' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 157 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
--CARTAO
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'CARTAO_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 32 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'CARTAO_FC_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 102 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
--FICHA
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'FICHA_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 28 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'FICHA_3' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 23 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'FICHA_FC_1' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 98 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
UNION
SELECT SUM (R.rec_vrliq - R.rec_vrpago) AS VALOR_TOTAL, 'FICHA_FC_3' AS TIPO_COBRANCA from receber R WHERE R.rec_tipo = 96 AND R.rec_quitado = 'N' AND R.rec_vencimento BETWEEN :DTINI AND :DTFIM
Responder
Clique aqui para fazer login e interagir na Comunidade :)