Using CASE in the WHERE clause.

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
pyro
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 323
Joined: Thu, 21 Sep 2006 10:21 am
Location: Barala - TT

Personal I'm trying to use the case inside a clause where the logic is simple but do not set it, it gives SQL error ended unexpectedly on line 17 and I could not find the solution:

Select all

 
SELECT DATA, 
                   COD_REPRE             , 
                   NOME_REPRE            , 
                   COD_ACA               , 
                   DES_ACA               , 
                   QTDE_CX_PREVISTO      , 
                   VLR_PREVISTO          , 
                   QTDE_CX_VENDIDO       , 
                   VLR_VENDIDO           , 
                   QTDE_CX_FATURADO      , 
                   VLR_FATURADO          , 
                   VLR_FATURADO_LIQ      , 
                   PERC_CX_PREV_X_FATUR  , 
                   PERC_VLR_PREV_X_FATUR 
        FROM  V_COTA_REPRE_PROD 
     WHERE  DATA      BETWEEN :V_DATA_INI AND :V_DATA_FIM                
          CASE :WEB_COD_REP WHEN IS NOT NULL THEN  
            AND COD_REPRE  = :WEB_COD_REP       
          ELSE 
            CASE :COD_SUP_WEB IS NOT NULL THEN 
              AND COD_REPRE IN(select r.cod_repre 
                               from supervisores s, representantes r, login_web l 
                               where l.codigo    = s.func_cod_func 
                               and s.cod_sup   = r.regiao_cod_reg 
                               and l.codigo    = :COD_SUP_WEB) 
            END 
          END           
          AND DATA IS NOT NULL 
ORDER BY COD_ACA, DATA 
Does anyone know if the building above is correct?
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother,

gives a search here in the forum, by case .
You will find topics that can help you.
Whatever, send it there.
Last edited by Trevisolli on Thu, 24 May 2007 8:16 am, edited 2 times in total.
pyro
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 323
Joined: Thu, 21 Sep 2006 10:21 am
Location: Barala - TT

Dude in Boua, this link that you passed refers to this same topic I opened.
I'vê already researched the forum and I did not find anything from the genre (Case at Clause Were).
vlw.
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Trevisolli must have fooled.
See this link: http://en.glufke.net/oracle/viewtopic.php?t=12

The right is Case When. See this example above! :-O
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

True, I have already eliminated the link, dr_gori (valeu).
Another thing, marry the WHERE clause, I'vê never seen no.
If someone knows something, send it there.
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

I sent the link there in the top post ...
There is yes, it's Case When.
It's very good!
pyro
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 323
Joined: Thu, 21 Sep 2006 10:21 am
Location: Barala - TT

Guys I found a solution using Union because with a case was not going to roll because I would need to use a subselect.
But Case in the Were Clause works Yes.
amorroni
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 8
Joined: Wed, 30 May 2007 6:28 pm
Location: SP

Someone has a practical example uses:?: Ndo Case in the WHERE clause? [/b]
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Usually using Case When when it is very complicated to do the condition using decode, or other functions.
See below:

Select all

select 
  campo1 
, campo2 
, case 
    when taxa>0 and valor>=100 and valor <1000 then salario*2 
    when taxa=  and valor>=1000 then salario*1.5 
    when valor < 100 then salario *3 
  end novo_valor 
from tabela 
Try to do this without the case when :-d
in the quoted link Previously, it has a very simple example using CaseWhen and the same thing not using..vey that is very complicated to do and little "understandable" ..
: -O
cleberzumba
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 223
Joined: Thu, 14 Dec 2006 10:45 am
Location: Brasília-DF
Cleber Zumba de Souza

Case in the WHERE clause ?? .... how? ... I never saw ... put there so I can see!
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

The case I say is not in the clause where. It does not make sense to use Case in the WHERE clause!
erthal
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 130
Joined: Mon, 22 Nov 2004 1:45 pm
Location: Niterói - RJ
Gustavo Erthal Jr. | TRISCAL
...................................................
Rio de Janeiro | (21) 2507-2010
São Paulo | (11) 3167-0526
www.triscal.com.br

Gentlemen,

to use a case in the WHERE clause is very simple, you need equal to operate, for example

Select all

SELECT .... 
FROM ... 
WHERE 1 = CASE A = B THEN 1 ELSE = END 
] Resolved the issue. And I have seen and much about using this feature in the WHERE clause, because it all depends on the model.

Hugs
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Galera, taking an old topic here


I could not understand this logic of Erthal

I need to do this:

Select all

DEFINE var1 = 'G' ou 'S'; 
 
 
SELECT georgset.orgs_ds || DECODE(georgset.orgs_dt_final,NULL,' (Ativo)',' (Inativo)') Label, 
       georgset.orgs_cd Value 
  FROM georgset, rhexpencar  
 WHERE georgset.orgs_cd = rhexpencar.orgs_cd 
   AND georgset.empr_cd = '01' 
   AND rhexpencar.func_cd = '006112' 
       
   AND georgset.orgs_dt_final IS NULL;             
 
 ORDER BY 1
Where if VAR1 = 'G' The comparison should be:

Select all

AND georgset.orgs_dt_final IS NOT NULL
and if VAR1 = ' S ':

Select all

AND georgset.orgs_dt_final IS NULL

Can you do this with the case in the WHERE clause or otherwise?
User avatar
mic_mota
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Wed, 12 Mar 2008 4:29 pm
Location: São Bernardo do Campo-SP
Abraço,

Michelle Mota

I thought of something like this (I did not test): Roll:

Select all

DEFINE var1 = 'G' ou 'S';  
 
SELECT georgset.orgs_ds || DECODE(georgset.orgs_dt_final,NULL,' (Ativo)',' (Inativo)') Label,  
       georgset.orgs_cd Value  
  FROM georgset, rhexpencar  
 WHERE georgset.orgs_cd = rhexpencar.orgs_cd  
   AND georgset.empr_cd = '01'  
   AND rhexpencar.func_cd = '006112'  
       
   AND ((georgset.orgs_dt_final IS NULL and var1 = 'S') or  
            (georgset.orgs_dt_final IS NOT NULL and var1 = 'G'));             
 
 ORDER BY 1 

Abrazzz
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

humm, good alternative, can be ,! : Idea:

Thanks for the tip
jeanferreira
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Wed, 30 Apr 2008 3:10 pm
Location: São Paulo - SP
Na vida devemos fazer três coisas, estudar, estudar e estudar !!!

8) Hello Galera, really works the case when in the Were Clause, below is an example of how I implemented the company I work:

Select all

Select * from <NOME_TABELA> 
where EMIS_IND_ENDOSSO = 204 
   and emis_num_endosso = CASE WHEN 1 <> -1 THEN '2' ELSE EMIS_NUM_ENDOSSO END 
   and EMIS_EMPRESA = 1 
   and EMIS_RAMO_ITEM = CASE WHEN 1 <> -1 THEN 18 ELSE EMIS_RAMO_ITEM END 
   and emis_num_apolice = '1085004466401' 
This is a practical example that it really works, but by my tests only works with comparison, that is, <field> = case .....


User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Wow, more than noted! hehe

thanks for all tips aí

really, after 3 years tramping in the area, I still find SQL a thing muito Fuck! :O
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

in the good sense
carlynhos77
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 172
Joined: Mon, 24 Oct 2016 7:20 pm

Hello, good night ... I'm picking up here the same son without father, I need to make a case, without select, example:

Select all

  CASE 
    WHEN :APP_PAGE_ID = 101 
      THEN '7' 
    WHEN :APP_PAGE_ID = 102 
      THEN '999' 
    ELSE '0' 
  END case; 
I'm creating an application item , so the result will load the item

could anyone help me? I thank
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

Hello,

I did not understand the problem.
Inside PLSQL Do you want to make these changes?

You must use "if ..... then ... else ...". In case.

Anything, what is the mistake that gives?
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

I think he wants to do an IF, Elsif, Elsif, Elsif ...

Thus:

Select all

    IF sales > 50000 THEN 
      bonus := 1500; 
    ELSIF sales > 35000 THEN 
      bonus := 500; 
    ELSE 
      bonus := 100; 
    END IF;
carlynhos77
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 172
Joined: Mon, 24 Oct 2016 7:20 pm

Hello, yes, I used the case in the example so, because it was the tests that I did, but they did not give sure ... but it can be if even, I just want to do with q depending on the page loaded the application item is loaded with A particular code ... I will already have the pages defined and for each page a code pro application item ... Type:

Select all

IF PAGINA = 101 THEN 
  '7' 
ELSE 
IF PAGINA = 102 THEN 
  '8' 
ELSE 
IF PAGINA = 103 THEN 
  '9' 
ELSE 
IF PAGINA = 104 THEN 
  '10' 
ELSE 
'0' 
END IF


I want to test how many pages I need
carlynhos77
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 172
Joined: Mon, 24 Oct 2016 7:20 pm

Hello, sorry, I had not seen the second page ... Dr_Gori's tip worked out, it was what I needed ... Problem I solved .. Thank you very much to all
ballboas
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 98
Joined: Thu, 02 Aug 2007 3:06 pm
Location: sp
Érico Balboa

A pergunta mais cretina é aquela que não é feita

You can use Case When yes, this way, I do not know if you answer.

Select all

  
Variavel := CASE 
    WHEN :APP_PAGE_ID = 101 
      THEN '7' 
    WHEN :APP_PAGE_ID = 102 
      THEN '999' 
    ELSE '0' 
  END; 
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Google [Bot] and 17 guests