Field condition with subquery

Navigation
2

Select example: 

SELECT campo1,
(ROUND(((t3.quantidade * t2.distancia *
(SELECT TOP 1 valor FROM frete WHERE datainicio <= t1.dataemissao AND tipo = t4.tipo ORDER BY DATAINICIO DESC))
- (t1.fretevalor - t5.pedagios))
/ (SELECT TOP 1 valor FROM diesel WHERE datainicio <= t1.dataemissao ORDER BY datainicio DESC),3)) saldo
FROM tabela1 t1
LEFT ...

In this example, I have only 2 columns ( campo1 and saldo ), saldo computed by subquerys ( and so should be ) .

It is not the case to bring all relationships (JOINS) because the field is calculating correctly and should not be changed. The question is how to create the condition for column saldo , according to its value.

It happens that, sometimes the value of column saldo comes negative , so in this case, I'd like to put it as 0 value.

I can do with CASE , but I would have to repeat 2x all this query (or not!), example: 

SELECT campo1, ROUND((CASE WHEN 
(((t3.quantidade * t2.distancia *
(SELECT TOP 1 valor FROM frete WHERE datainicio <= t1.dataemissao AND tipo = t4.tipo ORDER BY DATAINICIO DESC))
- (t1.fretevalor - t5.pedagios))
/ (SELECT TOP 1 valor FROM diesel WHERE datainicio <= t1.dataemissao ORDER BY datainicio DESC)) < 0 
THEN 0 ELSE 
(((t3.quantidade * t2.distancia *
(SELECT TOP 1 valor FROM frete WHERE datainicio <= t1.dataemissao AND tipo = t4.tipo ORDER BY DATAINICIO DESC))
- (t1.fretevalor - t5.pedagios))
/ (SELECT TOP 1 valor FROM diesel WHERE datainicio <= t1.dataemissao ORDER BY datainicio DESC))END),3) saldo
FROM tabela1 t1
LEFT ...
  • Is there another way to compare value as CASE but without repeating the entire subquery (as in the example)?
asked by anonymous 08.08.2018 / 20:43

1 answer

2

Consider using CTE to make the code readable and easy to maintain. 

-- código #1 v3
;with 
ColetaDados as (
SELECT campo1, t3.quantidade, t2.distancia,
       (SELECT TOP (1) f.valor FROM frete as f WHERE f.datainicio <= t1.dataemissao AND f.tipo = t4.tipo ORDER BY f.DATAINICIO DESC) as valor_frete,
       t1.fretevalor, t5.pedagios,
       (SELECT TOP (1) d.valor FROM diesel as d WHERE d.datainicio <= t1.dataemissao ORDER BY d.datainicio DESC) as valor_diesel
  from tabela1 t1
       left ...
),
CalculoSaldo as (
SELECT campo1, 
       case when valor_diesel = 0 then NULL
            else (((quantidade * distancia * valor_frete) - (fretevalor - pedagios)) / valor_diesel) end as pre_saldo
  from ColetaDados
)
SELECT campo1, 
       saldo= round (case when pre_saldo < 0 then 0 else pre_saldo end, 3) as saldo
  from CalculoSaldo;

Make sure the formulas are correct; I may have forgotten something.

The CTE CollectSaldo only obtains the data, without making any calculation. The second CTE, CalculoSaldo, calculates the balance according to the formula. At the end, SELECT does the negative value treatment and rounds the value to 3 decimal places. One step at a time. Internally the optimizer merges everything into a single command and generates the execution plan.

In the case of correlated subqueries, I suggest that you always identify the columns with alias .

When possible, read the "Modular Programming with Table Expressions (CTE)" article, which is in the Articles section of SQL Port .

For this case it seems to me that you can also use APPLY instead of correlated subqueries. Maybe it will get even more efficient.

    
08.08.2018 / 23:01
Quicksort vs Radix-sort How to return reregistered records between tables in laravel