Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: calc field
Hi!
Yes, the most appropriate is CASE, but NVL would be just fine. In case of dates functions, look up the SQLhelp, and there are number of DATE FORMATS. You will find the exact format you are looking for....
BR,
Darko
"Michel Cadot" <micadot2removethat_at_free.fr> wrote in message
news:3f7a7ea3$0$27031$626a54ce_at_news.free.fr...
>
> "Roger Smith" <rsmith22_at_fastmail.net> a écrit dans le message de
> news:5Vmeb.35789$H86.845371_at_news1.telusplanet.net...
> > Last question
> >
> > to_date(concat(nvl(SomeYear, 1990), '/01/01'),'YYYY/MM/DD')
> >
> > This works fine as long as SomeYear is 4 digits....any way to trap 2
digits?
> >
> > Roger
> >
> > "Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message
> > news:3F79EF49.DFB4F3B_at_remove_spam.peasland.com...
> > > You nested NVL functions, or DECODES, or we didn't even show you 9i's
> > > new CASE statement.......
> > >
> > > Cheers,
> > > Brian
> > >
> > > Roger Smith wrote:
> > > >
> > > > IF both are null, can I set a default?
> > > >
> > > > "Michel Cadot" <micadot2removethat_at_free.fr> wrote in message
> > > > news:3f79e71d$0$10410$626a54ce_at_news.free.fr...
> > > > >
> > > > > "Roger Smith" <rsmith22_at_fastmail.net> a écrit dans le message de
> > > > > news:4ileb.35770$H86.839814_at_news1.telusplanet.net...
> > > > > > Need suggestions on how to accomplish this, I know you can
create a
> > > > > > calculated field in sql:
> > > > > >
> > > > > > select (productPrice * 1.5) as "NewPrice"
> > > > > >
> > > > > > but my calc field is complicated by if then, if productPrice not
> > null
> > > > then
> > > > > >
> > > > > > select (productPrice * 1.5) as "NewPrice" else
> > > > > >
> > > > > > select (WarehousePrice * 1.5) as "NewPrice"
> > > > > >
> > > > > > and so on... can this be done in sql?
> > > > > >
> > > > > > Roger
> > > > > >
> > > > > >
> > > > >
> > > > > Use the NVL function:
> > > > > select nvl(productPrice,WarehousePrice)*1.5 as "NewPrice" ...
> > > > >
> > > > > Hope this helps
> > > > > Michel Cadot
> > > > >
> > > > >
> > >
> > > --
> > > ===================================================================
> > >
> > > Brian Peasland
> > > dba_at_remove_spam.peasland.com
> > >
> > > Remove the "remove_spam." from the email address to email me.
> > >
> > >
> > > "I can give it to you cheap, quick, and good. Now pick two out of
> > > the three"
> >
> >
>
> Assuming SomeYear is a number field you can use the RR format element:
>
> to_date(to_char(mod(nvl(SomeYear, 1990),100),'00')||'/01/01','RR/MM/DD')
>
> Hope this helps
> Michel Cadot
>
>
>
Received on Wed Oct 01 2003 - 04:13:21 CDT