SQL & PL/SQL

Are you looking for something like this?

1.

SQL> SELECT TRUNC(350/60) || '.' || (350 - TRUNC(350/60)*60) FROM DUAL;

TRUN
----
5.50

2.

SQL> WITH data AS
  2  (
  3          SELECT 5 AS HR
  4          ,      40 AS MIN
  5          FROM    DUAL
  6  )
  7  SELECT  HR || '.' || MIN AS HR_MIN
  8  FROM    DATA
  9  /

HR_M
----
5.40

If not, it is always helpful to provide the following:

1. Oracle version (SELECT * FROM V$VERSION)
2. Sample data in the form of CREATE / INSERT statements.
3. Expected output
4. Explanation of expected output (A.K.A. "business logic")
5. Use \

 tags for #2 and #3. See FAQ (Link on top right side) for details.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Hi, Abhi, If x is the number of minutes (such as 350):

TO_CHAR ( FLOOR (x / 60)) || ':'
	  	     	  || TO_CHAR ( MOD (x, 60)
			     	     , 'FM00'
				     )
 

It would make more sense to store the combined hours and minutes as the total number of minutes (340 in this case).
Given that you have two numbers:

TO_CHAR (time_allowed_hrs) || '.'
			   || TO_CHAR ( time_allowed_mins
			      	      , 'FM00'
				      )

hi ,

thanks all for your prompt reply

will it convert larger or any value of minutes to hours and minutes i mean in our cases we have 4000 minutes etc i mean higher

Regards
Abhi

Hi Frank,

just implemented second solution u provided, to_char one

here is my query i m using it in oracle report 6i

INSERT INTO PRTEMPINCENT(JOBCARDNO,CARDCODE,SHOP,TICKETNO,TIMEALLOWED,TIMETAKEN,
TIMESAVED,TIMEWORKED,ALLOCATION,TIMELOST)
(SELECT distinct JOBCARDNO,'3',SHOP,TICKETNO,TO_CHAR (timeallowedhrs) || '.'
|| TO_CHAR ( timeallowedmins, 'FM00' ),TO_CHAR (timetakenhrs) || '.'
|| TO_CHAR ( timetakenmins, 'FM00')
,GREATEST(TIMEALLOWEDHRS - TIMETAKENHRS,0),0,
WORKORDERNO,GREATEST(TIMETAKENHRS - TIMEALLOWEDHRS ,0)
FROM PRTRINCI
WHERE PAYPERIOD = :P_1);
now when i i am using it two times oracle report shows me error that to many declarations of to_char match this call?? when i m using it only for time allowed hrs it works or for time taken hrs it works, but when i implement like above it don't work

Regards
Abhi

Edited by: user9095963 on Apr 5, 2010 10:32 AM

Hi What happens when you try it? It sounds like a column is always NULL, and the query above can't figure out what the data type is supposed to be.
To work around that, use

CAST (column_name AS NUMBER) 

instead of column_name.

If you'd like help, post a little sample data (CREATE TABLE and INSERT statements) and the results you want from that data.
If you can illustrate your problem using tables in the scott or hr schemas, then you can skip the sample data.

hi Frank,
timeallowedhrs, timeallowedmins, timetakenhrs, timetakenmins are different column of prtrinci table

here the query i write in sql navigator

select TO_CHAR (timetakenhrs) || '.'|| TO_CHAR ( timetakenmins, 'FM00') from prtrinci

the output is like this
16.00
21.15
34.00
67.00
9.00
24.00
3.00
22.00
22.00

well when i m executing the above query for both timeallowedhrs and timeallowedmins it shows the error that i mention previously

if i do it this way


INSERT INTO PRTEMPINCENT(JOBCARDNO,CARDCODE,SHOP,TICKETNO,TIMEALLOWED,TIMETAKEN,
TIMESAVED,TIMEWORKED,ALLOCATION,TIMELOST)
(SELECT distinct JOBCARDNO,'3',SHOP,TICKETNO,TO_CHAR (timeallowedhrs) || '.'
|| TO_CHAR ( timeallowedmins, 'FM00' ),timeallowedhrs
,GREATEST(TIMEALLOWEDHRS - TIMETAKENHRS,0),0,
WORKORDERNO,GREATEST(TIMETAKENHRS - TIMEALLOWEDHRS ,0)
FROM PRTRINCI
WHERE PAYPERIOD = :P_1);

timeallowedhrs, timeallowedmins, timetakenhrs, timetakenmins are diffrent column of prtrinci table
the o/p will be

1 3 02964 413 3.3 2
here 3.3 is timeallowed combining timeallowed hrs and time allowed mins, and 2 is only timeallowedhrs

now when i m using ur query in my above query for both timeallowed and timetaken, the error is coming

if i use ur query for either time allowed or time taken it works but not for both at a time

also here i find that 30 mins is being reduced to 3 but is it possible that 30 mins be shown as 30 only and not 3???

Regards
Abhi

Edited by: user9095963 on Apr 5, 2010 11:34 AM

Hi Frank,

Your formula

TO_CHAR ( FLOOR (x / 60)) || ':'
|| TO_CHAR ( MOD (x, 60)
, 'FM00'
)
works perfect, well when i am using this formula in my query it shows

ora-1722 error invalid number error, filed is number

can u give same formula for number filed

also one more thing

now as we converted minutes to hours and minutes now how can i convert back hours and minutes to minutes

for example 24.31 how to convert this in to minutes

Regards
Abhi

Convert hour and minute (HH.MM) to minute.

let number variable is x (24.31)

select (trunc(x)*60) + ((x - trunc(x))*100 ) from dual;

(24*60) + (.31*100) = number of minute.

Hi

whenever i m using the above mention formula i m facing ora-1722 invalid number error

in my first case when i used the franks' floor method and tried to insert the data field that was number it gave above error

now when my filed is varchar then also the minute formula is giving me same error

how to sove this problem ?? plz help

Regards
Abhi

Edited by: user9095963 on Apr 9, 2010 11:12 AM

Hi, Apparantly, you are not passing a number to that forumla. Perhaps you're starting with a number, then doing something that trnasforms it into some other data type, without you being aware of it.
Post a self-contained script that I can run to reproduce the error. Include CREATE TABLE and INSERT statements, queries, or anythiong else that I'll need to get the error myself. I see someone has already posted a good way. You can simplify that a little Bby using MOD:

SELECT  (TRUNC (x) * 60) + 
	( (MOD (x, 1)
	* 100 
	) 
FROM 	dual;

where x is a NUMBER. If you have a sting, s, instead, use TO_NUMBER (s) in place of x.

Hi Frank,

thanks for your reply

I m using oracle 9i and i m using all this query to build reports in oracle developer suite 6i
>
INSERT INTO PRTEMPINCENT(JOBCARDNO,CARDCODE,SHOP,TICKETNO,TIMEALLOWED,TIMETAKEN, TIMESAVED,TIMEWORKED,ALLOCATION,TIMELOST,PAYPERIOD)
(SELECT distinct JOBCARDNO,'3',SHOP,TICKETNO, timeallowedhrs || '.' || timeallowedmins,timetakenhrs || '.' || timetakenmins,
greatest(TO_CHAR ( FLOOR (((timeallowedhrs*60+timeallowedmins)-(timetakenhrs*60+timetakenmins)) / 60)) || ':'
|| TO_CHAR ( MOD (((timeallowedhrs*60+timeallowedmins)-(timetakenhrs*60+timetakenmins)), 60), 'FM00' ),0),0,WORKORDERNO,
greatest(TO_CHAR ( FLOOR (((timetakenhrs*60+timetakenmins)-(timeallowedhrs*60+timeallowedmins)) / 60)) || ':'
|| TO_CHAR ( MOD( ((timetakenhrs*60+timetakenmins)-(timeallowedhrs*60+timeallowedmins)), 60)
, 'FM00'),0),:P_1 FROM PRTRINCI WHERE PAYPERIOD = :P_1);

>


here what is happening with me the floor formula u gave i applied in this query, but it gave me ora-1722 error as
timeallowed, time taken, time saved , time worked time lost all fields data type was number
then i changed data type to varchar2 then it worked perfectly fine

now i want to convert value stored in timeallowed, timesaved etc to minutes again for eg value stored in time allowed will be 22.10 so i want to convert it back to minutes

when i used the above minutes formula again it gave me same invalid number error although my filed data type is varchar2

Frank kindly provide some solution for minutes to hours and minutes and hour and minutes to minutes that work for number and varchar2

thanks for your support

Regards
Abhi

Edited by: user9095963 on Apr 9, 2010 11:33 AM

Hi,

If I can't re-create the error myself, the chances of me being able to help you are very slim. I really need a self-contained script, like I described in my last message.

I might be able to spot something in your code, if only it were readable. Format it, so that (for example) it's easy to see what the arguments to various functions are, especially when the functions are nested deeply. Type these 6 characters:

{code}

(small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.

HI FRANK,

sorry for not able to put my point, here what i m trying to say

as i said i m using this in my oracle report developer for generating report also same error comes when i use sql navigator

i have tables 1.prtrinci, 2.prtrinch

there are 4 different fields time allowed hrs, time allowed min, time taken hrs, time taken min, data type of all this fields is number

now prtempincent is the table where output of report that will be stored
it has time allowed and time taken fields also their data type is number

now when i used your floor query , the error came was 1722 invalid number error, well i got the error as my data field was number it was giving me error so i changed my data field data type to varchar2 , ti solved my problem

now i am creating another report
for that input table will be prtempincent and output table will be prtempcpl
now i want to convert the time allowed and time taken filed back to minutes , now when i am using formula mentioned by shahid ali
to convert the time allowed and time taken back to min it again give me invalid number error although my both input and output table data type are varchar2

INSERT INTO PRTEMPINCENT
(JOBCARDNO,   CARDCODE,   SHOP  , TICKETNO, TIMEALLOWED,   TIMETAKEN, 

 TIMESAVED,   TIMEWORKED,   ALLOCATION,   TIMELOST,    PAYPERIOD)

   (SELECT distinct JOBCARDNO,'3',SHOP,TICKETNO, 

timeallowedhrs || '.' || timeallowedmins,timetakenhrs || '.' || timetakenmins,

    greatest(TO_CHAR ( FLOOR (((timeallowedhrs*60+timeallowedmins)-(timetakenhrs*60+timetakenmins)) / 60)) || ':'

	 || TO_CHAR ( MOD (((timeallowedhrs*60+timeallowedmins)-(timetakenhrs*60+timetakenmins)), 60), 'FM00' ),0)

,0,WORKORDERNO,
      greatest(TO_CHAR ( FLOOR (((timetakenhrs*60+timetakenmins)-(timeallowedhrs*60+timeallowedmins)) / 60)) || ':'

	  	  || TO_CHAR ( MOD( ((timetakenhrs*60+timetakenmins)-(timeallowedhrs*60+timeallowedmins)), 60)

			     	     , 'FM00'),0),:P_1  FROM PRTRINCI WHERE PAYPERIOD = :P_1);

this is the query that i mentioned input table as prtrinci and output table as prtempincent above
now when the data type of timeallowed, timetaken was number it gave me error 1722 invalid number and when i changed my data type to varchar2 it worked fine

Frank can u give me any formula to convert minutes to hours and minutes and hours and minutes back to minutes for number data type???
hope i m now able to explain my problem

Regards
Abhi

Edited by: user9095963 on Apr 9, 2010 1:32 PM

hi Frank,


i also find a and t easier to read but frankly i don't know that deep bro so

greatest
(TO_CHAR 
            ( FLOOR 

              (((timetakenhrs*60+timetakenmins)-

                 (timeallowedhrs*60+timeallowedmins)) / 60))    || ':'

|| TO_CHAR ( MOD( ((timetakenhrs*60+timetakenmins)-

                                          (timeallowedhrs*60+timeallowedmins)), 60)

			     	              , 'FM00'),0)

the query is working fine as i said you i changed my datatype to varchar2 from number

and the second greatest value is 0 we do this to show that if the worker has done profit or loss if the value is positive then it will show the value else the second greatest value zero

Regards
Abhi

Edited by: user9095963 on Apr 9, 2010 2:19 PM

HI FRANK,

CAN YOU SUGGEST ME HOW TO USE YOUR

TO_CHAR ( FLOOR (x / 60)) || ':'
	  	     	  || TO_CHAR ( MOD (x, 60)
			     	     , 'FM00'
				     )

FORMULA FOR FIELDS WITH NUMBER DATA TYPE'S I HAVE USED TO_NUMBER INSTEAD , BUT DOES NOT WORK BRO, CAN U SUGGEST SOME OTHER FORMULA??

ALSO SUGGEST FORMULA TO CONVERT HOURS AND MIN BACK TO MINS FOR NUMBER FIELD

REGARDS
Abhi

Hi Frank,

My problem got solved,

actually the your floor formula you gave

TO_CHAR ( FLOOR (x / 60)) || ':'
	  	     	  || TO_CHAR ( MOD (x, 60)
			     	     , 'FM00'
				     )

just i have to change ':' to '.' and it worked

Regards
Abhi.