SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to get all child records from a table with multiple self referential integrity constraints?

User_RPCWIMay 23 2019 — edited May 28 2019

As per the requirement i have to identified data from table t1. Once the data is identified i need to identify its child records as well.

I could easily identify its childs if there is only one self referential constraint.

select * from t1 start with c1=100 connect by prior c1=c2;

But My table has multiple self referential integrity constraints like below

create table t1( c1 number pk, c2 number references c1, c3 number references c1, c4 number references c1);

i have tried to identify the childs of each self RI separately. But the problem here is after identifying the childs i have to delete all the childs and parent records.

With the approach of identifying the self referential Integrity separately we may find one record is child in one self RI the same record might be parent in other Self RI. so while deleting we are facing child record found issue.

so i have used the below query to identify all the childs in one shot. But the query is running for hours and not getting finished.

select * from t1 start with c1=100 connect by prior c1=c2 or prior c1=c3 or prior c1=c4;

i thought of using the below query

select * from t1 start with c1=100 connect by prior c1=c2 union select * from t1 start with c1=100 connect by prior c1=c3 union select * from t1 start with c1=100 connect by prior c1=c4;

But after identifying the childs i have delete all identified data. while deleting in bulk mode im getting errors like child records found. Upon debug we identified with this approach a record might be parent in one self RI and child in another self RI. so we have to build tree with all constraints . Please let me know if i missing something

We need to move this data to another db before deleting . All the identified rows will have to be moved and then purged.

Please suggest any way to identify all child records to be moved and purged.

Thanks

Centinul

Are you looking for something like this?

1.

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

TRUN
----
5.50

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.

1 person found this helpful

Frank Kulash

Hi, Abhi,

user9095963 wrote:
hi all,

i want to do two things

1. i want to convert minutes to hours and minutes that is
if there are 350 minutes it should be 5.50

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

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

2. i have two column time allowed hrs and time allowed mins now i want to join this two in
this way
for example time allowed hrs is 5 and time allowed mins is 40 then i should be join as 5.40

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'
				      )

1 person found this helpful

760814

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

760814

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

Frank Kulash

user9095963 wrote:
...
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

What happens when you try it?

user9095963 wrote:
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

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.

760814

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

760814

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

Shahid Ali

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.

760814

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

Frank Kulash

Hi,

user9095963 wrote:
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

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.

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

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.

760814

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

Frank Kulash

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.

760814

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

Frank Kulash

Answer

Hi,

The code is a little more readable now, but only a little.

For example, I can now see that you're calling GREATEST where the 1st argument is a string:

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

This may be causing the error, if the 2nd argument is a NUMBER, but how can anybody find out what the 2nd argument to GREATEST is?
Even if this is not causing the ORA-01722 error, there's a good chance it will be giving you the wrong results. The string '2.00' is greater than the string '10.45', because '2' is greater than '1'. Perhaps you meant to do GREATEST first, and then TO_CHAR, or perhaps you menat something else altogether.

You could simplify you code a lot if you did some calculations (such as (timeallowedhrs * 60) + timeallowedmin) only once, in a sub-query.
For example:

INSERT INTO  prtempincent (...)
WITH   got_a_t	AS
(
	SELECT DISTINCT
		(timeallowedhrs * 60) + timeallowedmin	AS a
	,	(timetakenhrs   * 60) + timetakenmin	AS t
	,	jobcardno
	,	shop
	...
)
SELECT	jobcardno
,	'3'
,	... FLOOR ( (a - t)
	    	  / 60
		  ) || ':'
		    || TO_CHAR ( MOD ( (a - t)
		       	       	     , 60
				     )
			       , 'FM00'
			       ) ...

I find

a - t

easier to read and understand (and debug), than

(timeallowedhrs*60+timeallowedmins)-(timetakenhrs*60+timetakenmins)

How about you?

Marked as Answer by 760814 · Sep 27 2020

760814

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

760814

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

760814

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.

1 - 17

Added on May 23 2019

#sql, #sql-pl-sql

18 comments

3,720 views

SQL & PL/SQL

How to get all child records from a table with multiple self referential integrity constraints?

Comments

Post Details