Condition with special DECODE

[DanielNN]

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]

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.

[dr_gori]

Select all

DECODE(R.SET_OF_BOOKS_ID, 0, 101, 101)

hehehehehe I love this forum session :-d

[DanielNN]

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.

[gpilger]

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]

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]

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.

[dr_gori]

I think it would be perfect like this:

Select all

AND P.APPLICATION_ID = 101

: - D