Field condition with subquery

Asked

Viewed 321 times

2

Example of select:

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), being the saldo calculated by subquerys (and so it must remain).

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

It turns out that, sometimes the value of the column saldo comes negative, then in this case, I would like to put it as value 0.

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 the value like the CASE but without repeating the whole subquery (as in the example)?
sql sql-server

  • can use variable?

  • @Thiagomagalhães I think it doesn’t work because it pulls the subquerys line by line, isn’t it?! Unless you create a @balance variable, and already use it for CASE. Is that it? If it’s not, then I have no idea.

  • Creating a function to calculate the balance can be an interesting solution.

  • @Williamjohnadamtrindade can not, have to be direct in this query. I am using the CASE even, working, but I opened the question to see if there is a better way that does not require duplicate all the code.

  • 1

    Evaluate using APPLY in place of diesel and freight undercovers. // When the code starts to get hazy, it is because it is past the time to use CTE... ;)

  • @Josédiz very good! APPLY would be the with?

  • 1

    @Rbz No. the with is CTE, as proposed solution below. When possible read the suggested article.

  • @Josédiz I found the CTE article, I took a look over it, really good, I will read in full as soon as possible! About the APPLY from what I found, only uses in functions, there is no way to do.

Show 3 more comments

1 answer

2


Evaluate the use of CTE to make 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;

Check carefully if the formulas are correct; I may have forgotten something.

The CTE Coletasaldo only obtains the data, without making any calculation. The second CTE, Calculosaldo, performs the balance calculation according to the formula. At the end, SELECT takes the negative value treatment and rounds the value to 3 decimal places. One step at a time. Internally the optimizer joins everything in a single command and generates the execution plan.

In the case of correlated sub-consultations I suggest that you always identify the columns with alias.

When possible read the article "Modular programming with table expressions (CTE)", which is in the section Articles SQL Port.

For this case it seems to me that it is also possible to use APPLY in place of the correlated sub-consultations. It may even be more efficient.

  • @I added the title of the article in the answer above, ok?

  • I mistook that answer for the question GROUP BY in query with column by subquery... Actually your answer should work, but you’re using the WITH. So in the case "there isn’t" a shape directly on the line, like the CASE. Just add at the beginning of the answer to be straight, and using the WITH as an alternative. Thanks

  • @Don’t know if I got your last remark right. The CASE remains in the code, only in a clean way. Note on the penultimate line of the code that is round (case when pre_saldo < 0 then 0 else pre_saldo end. The pre-saldo is equivalent to all that mathematical expression. It would be about that comment?

  • More or less. I think it wouldn’t change much because I need to compare the dates and type: WHERE datainicio <= t1.dataemissao AND tipo = t4.tipo, the way you did, you’re not passing this parameter, it’s not!?

  • Jose, I’ll even cancel the answer (but leave the +1), not to confuse, until we hit the final answer, and not to disturb if someone see ok!?

  • @Dates are being compared. See CTE Collected.

  • Let’s go continue this discussion in chat.

Show 2 more comments

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.