Re: Simple Oracle Questions!

In article <2snmct$cla_at_ucunix.san.uc.edu> pateljb_at_ucunix.san.uc.edu (Jay Patel) writes:
>Folks,
>
>I'm learning Oralce (6.0), so please, excuse my simple questions.
>
>1) How'd I subtract Time??
>
> I tried :
>
> to_char(f1, 'HH24:MM:SS') - to_char(f2, 'HH24:MM:SS')
>
> but, it doesn't work.
>

select to_number(to_char(f1,'HH24'))*3600+to_number(to_char(f1,'MM'))*60
       +to_number(to_char(f1,'SS')) - 
      (to_number(to_char(f2,'HH24'))*3600+to_number(to_char(f2,'MM'))*60
       +to_number(to_char(f2,'SS')))

This will give you the difference in seconds. I have not actually done this specific query but ones very similar. I would validate this before you go deplying it widely.

By extension, I think you can see how you would include days into the equation assuming the same month. There are less straight forward means of doing that, too (I think) You can also nest the above expression inside of other functions to convert seconds back to hours, minutes and seconds. (The proof is left to the reader) :-)

>2) Can I interst a character(i.e., *) in the output of a select??
>
> select Name, Zip from Tab1;
>
> I want the output to be
>
> Name1 * Zip1
> Name2 * Zip2
> Name3 * Zip3
>
select name||' * '||zip from tab1;

You get this one for free ;-)

-- 
============================================================================
Michael Sallwasser  | Down one path is utter dispair and hopelessness. Down 
Long Beach CA       | the other is total destruction. Let us choose wisely.
============================================================================