View with many, but many case when’s

Asked

Viewed 62 times

3

I’m riding a view in the database that the view pai uses as another query view, which in turn calls again view (in that view is made the sum of several items and brings the total per line), in view 'outside' is picked up the accounts (by number) and play a name for each account, example:

case when conta = '1' then 'chamar desse nome'

But this occurs about 30 times in view, since we have several accounts, and at 3° view who calls this, he puts together several accounts, but using the name given in view previous, so it gets more or less this:

case when conta in ('alugueis', 'agua', 'luz') then ('grupo despesa')

It’s just that there are many accounts, and many groups, have reached a limit where the SQL gave me complexity limit reached, know what can be done to fix?

I’ll post an excerpt from the 3° view here to exemplify:

case 
when (a.num_conta between '1.1.01.001.0001' and '1.2.99.999.9999') then 'TOTAL ATIVO'
when (a.num_conta between '2.1.01.001.0001' and '2.4.99.999.9999') then 'TOTAL PASSIVO'
when (a.num_conta between '2.1.01.001.0001' and '2.4.99.999.9999') then 'TOTAL PASSIVO'
when (a.num_conta between '3.1.01.001.0001' and '3.9.99.999.9999 ') then 'TOTAL RESULTADO' end as 'TC',

case 
when (a.NUM_CONTA between '1.1.01.001.0001' and '1.1.99.999.9999') then '1 - ATIVO CIRCULANTE'
when (a.NUM_CONTA between '1.2.01.001.0001' and '1.2.99.999.9999 ') then '2 - ATIVO NÃO CIRCULANTE'
when (a.NUM_CONTA between '2.1.01.001.0001' and '2.1.99.999.9999 ') then '3 - PASSIVO CIRCULANTE'
when (a.NUM_CONTA between '2.2.01.001.0001' and '2.3.99.999.9999 ') then '4 - PASSIVO NÃO CIRCULANTE'
when (a.NUM_CONTA between '2.4.01.001.0001' and '2.4.99.999.9999 ') then '5 - PATRIMONIO LIQUIDO'
when (a.NUM_CONTA between '3.1.01.001.0001' and '3.9.99.999.9999 ') then '6 - RESULTADO DO PERIODO' end as tipo,
case when (a.tipo_conta in ('01 - CAIXAS E BANCOS', '02 - APLICAÇÕES FINANCEIRAS','03 - NUMERÁRIOS EM TRÂNSITO')) then '01 - TOTAL EQ CAIXA'  
   when (a.tipo_conta in ('01 - CLIENTES','02 - BANCOS CONTAS VINCULADAS','03 - TITULOS DE CAPITALIZAÇÃO','04 - ADIANTAMENTOS','05 - IMPOSTOS A RECUPERAR','OUTROS CRÉDITOS - CHEQUES DEVOLVIDOS','07 - MERCADORIAS EM TRANSITO ATIVO','08 - MUTUO NEWFER','09 - ADIANTAMENTOS PROJETO NEWFER')) then '02 - DIREITOS REALIZAVEIS'
   when (a.tipo_conta in ('DESPESAS EXERCICIO SEGUINTE')) then '04 - DESPESAS DO EXERCICIO SEGUINTE'
   when (a.tipo_conta in ('ESTOQUE')) then '03 - ESTOQUE'....
sql database view case
  • 1

    I don’t know which DBMS you are using but already evaluated the use of CTE (Common Table Expressions) with WITH?

  • I do not know this functionality friend, I will give a search to see, I was able to run eliminating some 'case whens' denials, but it is as the friend above said, the more accounts are entering, I will have to change and add more conditions and soon will burst again

  • 1

    First you do not need between, you can simply take the characters before the second level (1.1, 2.1, 3.1 etc) and make a dirata comparison. By the way I seem to have problem the format that used in the field (for example, what do the points inside the string? if the width is fixed, is storing spurious thing, if it is not fixed, the between will not work qq way) - that you should not have these strings in SQL, but in a separate table (either in SQL or in your application). The @Niero answer the main problems in the way it is applying its logic.

  • @Rafaels. Has the answer solved your question? Do you think you can accept it? See [tour] if you don’t know how to do it. This would help a lot to indicate that the solution was useful to you. You can also vote on any question or answer you find useful on the entire site.

1 answer

1

Wasn’t meant to use CASE, should be a relationship with JOIN or some other way to establish this, ultimately using a stored Procedure (this seems wrong yet), including because this is not something fixed, if someone changes a description, an item numbering, has new data, or leaves some, is something discretionary of the user he will have to ask you to change the code and if you do not change at the time the application stops working properly. The solution is to take this data from another table, and I hope you already have it, otherwise there are several other problems. If you don’t have to think about it, you can’t solve a bad data modeling and architecture problem with a code hack. There has to be a parameterization of these groupings.

The CASE It serves to use with something very stable and that never has to move, besides being something short, you are turning given into code, this is wrong. The main point is that the CASE is the wrong mechanism, has to fix the modeling. Even if you have a solution in the code that does not involve redoing the modeling will be gambiarra.

  • I understand, but what would be the option then? Because in this case, I have not found another way to do, as they need to take existing accounts, group and name the groups, so bring in a BI, today, we do not have the names ready in the program

  • The one I wrote in the reply.

  • but I already use joins to join several tables, my problem would be to play several accounts, in groups passed by the user, because the idea is to have a ready analysis, without having to take account of the account every month, play in excel and assemble this, I cannot see another way to do without using 'case when'...

  • 1

    Your question is about the CASE, and there’s no way to do it this way. In fact you shouldn’t do it in Excel, you have to remodel your data so you can put it all together with JOIN, That you’re not doing with JOIN, is doing with CASE. I can visualize less than you after you know the software you’re working on, I can’t, I can only talk about what I saw, and I saw something wrong to do, you’re using CASE for something he wasn’t created for.

Browser other questions tagged sql database view case

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