Re: trouble with a decode statement

Outer join is used in case where information lacks in the secondary table so we have null instead. If you don't use it, you will get only rows which can be joined.

Precisely, the problem is when ytd.fisc_year_yy is null or ytd.fisc_month is null because ytd.fisc_year_yy(+)-1 (also ytd.fisc_month(+)-1). How can you do "null -1 " ?

Cheers.

Christopher Boyle wrote:
>
> Hello, relative newbie here!
>
> We have a view that was created by a consultant who seems to have
> forgotten to leave us the documentation. The view creates fine but if
> you try to do a SELECT * from it , an ora_01722 error occurs. This led
> me to look for any math that might have been done against date fields so
> i could verify that they were in fact date fields. I found these lines
> in the where clause
>
> and a.fisc_year_yy =
> decode(ytd.fisc_year_yy(+),'00','99',lpad(ytd.fisc_year_yy(+)-1,2,'0'))
> and a.fisc_month =
> decode(ytd.fisc_month(+),'13','01',lpad(ytd.fisc_month(+)-1,2,'0'))
>
> fisc_month is a char(2) in both tables. I believe i can figure out the
> first one but the second line eludes me. We do work with 13 fiscal
> months so that isn't the issue but as i follow it (read that greeting
> line again before you laugh) if the month is 13 it will be decoded as
> 01 and if not, 1 will be subtracted from it than if need be it will be
> filled out with "0" to two places. Will Oracle do the implicit
> conversion in order to process the subtraction? Should i expect an
> error when the fiscal month is "01" (we do not have a fiscal month
> "00") What might the purpose be of even making this statement an outer
> join since there will be a default fill in due to the decode?
>
> Thanks in advance for your help
>
> If you desire, replies can be directly sent to cboyle_at_neoucom.edu
Received on Tue Dec 14 1999 - 00:00:00 CET