Condition with special DECODE

Este forum é dedicado a códigos errados que se encontram por aí, ou seja, coisas que não se deve fazer de jeito nenhum! Não coloque neste forum dúvidas! (apenas situações bizarras do nosso dia a dia :-)
Post Reply
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

I came across this condition and decode combined, I could not hold the laughter.

Select all

SELECT R.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, 
       R.PERIOD_NAME PERIOD_NAME, 
       R.TO_CURRENCY_CODE TO_CURRENCY_CODE, 
       R.AVG_RATE AVG_RATE 
  FROM APPS.GL_TRANSLATION_RATES R,  
APPS.GL_SETS_OF_BOOKS S,  
APPS.GL_PERIOD_STATUSES P 
 WHERE R.SET_OF_BOOKS_ID = S.SET_OF_BOOKS_ID 
   AND S.SET_OF_BOOKS_ID = P.SET_OF_BOOKS_ID 
-- 
   AND P.APPLICATION_ID = DECODE(R.SET_OF_BOOKS_ID, 0, 101, 101) 
-- 
   AND R.PERIOD_NAME = P.PERIOD_NAME 
   AND R.TO_CURRENCY_CODE = 'IGPM'
djoq
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 54
Joined: Fri, 27 Jan 2012 8:16 am
Location: Londres UK

It already appeared here at work, I called the analyst to show. He told me: if he was there there is some reason, he does not change.
: -The
I do not know what was worse, the code or opinion of it.
PS: I was a trainee.
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

Select all

DECODE(R.SET_OF_BOOKS_ID, 0, 101, 101)
hehehehehe I love this forum session :-d
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

Not to mention that I wanted to match (I do not even know if I can say that, given the constant disguised as Decode) Application_Id with set_of_books_id.
User avatar
gpilger
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 21 Apr 2010 3:34 pm
Location: Novo Hamburgo - RS
Gilson Pilger
"Por não saber que erra impossível, ele foi lá e fez" autor desconhecido

He must have done this to match a value casso the return is null. Something NVL (NULL, 101). Because null is not equal to anything.
thiago rawel
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 16 Jan 2018 1:40 pm

I believe this would be a better option ...

Select all

SELECT R.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, 
       R.PERIOD_NAME PERIOD_NAME, 
       R.TO_CURRENCY_CODE TO_CURRENCY_CODE, 
       R.AVG_RATE AVG_RATE 
  FROM APPS.GL_TRANSLATION_RATES R, 
APPS.GL_SETS_OF_BOOKS S, 
APPS.GL_PERIOD_STATUSES P 
WHERE R.SET_OF_BOOKS_ID = S.SET_OF_BOOKS_ID 
   AND S.SET_OF_BOOKS_ID = P.SET_OF_BOOKS_ID 
-- 
  AND ( R.SET_OF_BOOKS_ID <> 0  AND  
            P.APPLICATION_ID = R.SET_OF_BOOKS_ID ) OR  
         (P.APPLICATION_ID = 101) 
-- 
   AND R.PERIOD_NAME = P.PERIOD_NAME 
   AND R.TO_CURRENCY_CODE = 'IGPM' 
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

Thiago Rawel wrote: I believe that this would be a better option ...

Select all

SELECT R.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, 
       R.PERIOD_NAME PERIOD_NAME, 
       R.TO_CURRENCY_CODE TO_CURRENCY_CODE, 
       R.AVG_RATE AVG_RATE 
  FROM APPS.GL_TRANSLATION_RATES R, 
APPS.GL_SETS_OF_BOOKS S, 
APPS.GL_PERIOD_STATUSES P 
WHERE R.SET_OF_BOOKS_ID = S.SET_OF_BOOKS_ID 
   AND S.SET_OF_BOOKS_ID = P.SET_OF_BOOKS_ID 
-- 
  AND ( R.SET_OF_BOOKS_ID <> 0  AND  
            P.APPLICATION_ID = R.SET_OF_BOOKS_ID ) OR  
         (P.APPLICATION_ID = 101) 
-- 
   AND R.PERIOD_NAME = P.PERIOD_NAME 
   AND R.TO_CURRENCY_CODE = 'IGPM' 
/ quote]

. A major problem is that:

Select all

P.APPLICATION_ID = R.SET_OF_BOOKS_ID
are two totally different information. It is comparing apple with bicycle.
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 it would be perfect like this:

Select all

AND P.APPLICATION_ID = 101
: - D
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest