Case in Sub Select SQL SERVER

Question

Case in Sub Select SQL SERVER

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good afternoon, I'm having trouble making a case in a subquery.

I need to bring an information called the order_id from the sf_vendas_boleto table, however when it comes NULL I need to bring the order_id from the sf_vendas_online table.

Follow the snippet ...

(case when  MAX(id_pedido) is null then (select MAX(id_pedido) from sf_vendas_online where id_venda = sf_vendas.id) else MAX(id_pedido) end id_pedido) id_pedido,

FOLLOW THE COMPLETE QUERY

SELECT * FROM(SELECT ROW_NUMBER() OVER (ORDER BY sf_vendas.id desc) as row,sf_vendas.*,P.nome_pessoa,P.sobrenome_pessoa,
    ISNULL((SELECT top 1 descricao_documento FROM sf_vendas_parcelas INNER JOIN sf_tipo_documento ON sf_tipo_documento.id = sf_vendas_parcelas.tipo_documento WHERE id_venda = sf_vendas.id),'CORTESIA') id_vendas_parcelas, 
    (select MAX(bol_data_parcela) from sf_vendas_boleto where id_venda = sf_vendas.id) bol_data_parcela, 
    (select MAX(id) from sf_vendas_boleto where id_venda = sf_vendas.id) bol_id, 
    (select SUM(quantidade) from sf_vendas_itens where id_venda = sf_vendas.id) quantidade, 
    (select SUM(valor_bruto) from sf_vendas_itens where id_venda = sf_vendas.id) valor_bruto, 
    (select SUM(valor_desconto) from sf_vendas_itens where id_venda = sf_vendas.id) valor_desconto, 
    (select MAX(data_pagamento) from sf_vendas_parcelas where id_venda = sf_vendas.id) data_pagameto, 
    (select SUM(valor_pago) from sf_vendas_parcelas where id_venda = sf_vendas.id) valor_pago, 
    (select sum(valor_bruto - valor_desconto) from sf_vendas_itens where id_venda = sf_vendas.id) valor_total,
    (select MAX(bol_valor) from sf_vendas_boleto where id_venda = sf_vendas.id) bol_valor,        
    (case when  MAX(id_pedido) is null then (select MAX(id_pedido) from sf_vendas_online where id_venda = sf_vendas.id) else MAX(id_pedido) end id_pedido) id_pedido,
    (select MAX(bol_nosso_numero) from sf_vendas_boleto where id_venda = sf_vendas.id) bol_nosso_numero
    from dbo.sf_vendas INNER JOIN sf_pessoa P ON P.id = sf_vendas.pessoa_venda) as x ORDER BY id desc

sql sql-server

asked by anonymous 18.10.2018 / 20:18

2 answers

How to open a message screen in all views that the user enters? + Laravel Consume json Retrofit in my real device (smartphone)

score 0 · Answer 1

Following is an example of using the ISNULL () and COALESCE () functions

declare @ID_1 int = null
declare @ID_2 int = 17

select ISNULL(@ID_1, @ID_2) teste1, COALESCE(@ID_1, @ID_2) teste2

If you have more conditions to apply, use COALESCE. For example:

declare @ID_1 int = null
declare @ID_2 int = null
declare @ID_3 int = 17

select COALESCE(@ID_1, @ID_2, @ID_3) teste1

score 0 · Answer 2

I was able to solve this code ...

ISNULL((SELECT MAX(id_pedido) FROM sf_vendas_online where id_venda = sf_vendas.id),(SELECT MAX(id_pedido) FROM sf_vendas_boleto where id_venda = sf_vendas.id)) id_pedido,