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
Using CASE in the WHERE clause.
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:
Does anyone know if the building above is correct?
-
- 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
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.
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.
- dr_gori
- 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
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
See this link: http://en.glufke.net/oracle/viewtopic.php?t=12
The right is Case When. See this example above! :-O
-
- 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
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.
Another thing, marry the WHERE clause, I'vê never seen no.
If someone knows something, send it there.
- dr_gori
- 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
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:
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
See below:
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
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
-
- 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!
- dr_gori
- 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
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!
-
- 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
...................................................
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
] 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
to use a case in the WHERE clause is very simple, you need equal to operate, for example
SELECT ....
FROM ...
WHERE 1 = CASE A = B THEN 1 ELSE = END
Hugs
- Porva
- 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
São Paulo/SP
Galera, taking an old topic here
I could not understand this logic of Erthal
I need to do this:
Where if VAR1 = 'G' The comparison should be:
and if VAR1 = ' S ':
Can you do this with the case in the WHERE clause or otherwise?
I could not understand this logic of Erthal
I need to do this:
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
AND georgset.orgs_dt_final IS NOT NULL
AND georgset.orgs_dt_final IS NULL
Can you do this with the case in the WHERE clause or otherwise?
- mic_mota
- 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
Michelle Mota
I thought of something like this (I did not test): Roll:
Abrazzz
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
-
- 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 !!!
Hello Galera, really works the case when in the Were Clause, below is an example of how I implemented the company I work:
This is a practical example that it really works, but by my tests only works with comparison, that is, <field> = case .....
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'
-
- 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:
I'm creating an application item , so the result will load the item
could anyone help me? I thank
CASE
WHEN :APP_PAGE_ID = 101
THEN '7'
WHEN :APP_PAGE_ID = 102
THEN '999'
ELSE '0'
END case;
could anyone help me? I thank
- dr_gori
- 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
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:
Thus:
IF sales > 50000 THEN
bonus := 1500;
ELSIF sales > 35000 THEN
bonus := 500;
ELSE
bonus := 100;
END IF;
-
- 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:
I want to test how many pages I need
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
-
- 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
-
- 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
A pergunta mais cretina é aquela que não é feita
You can use Case When yes, this way, I do not know if you answer.
Variavel := CASE
WHEN :APP_PAGE_ID = 101
THEN '7'
WHEN :APP_PAGE_ID = 102
THEN '999'
ELSE '0'
END;
-
- Information
-
Who is online
Users browsing this forum: Google [Bot] and 17 guests