Home » SQL & PL/SQL » SQL & PL/SQL » challenging question can any one can solve it and help me
icon6.gif  challenging question can any one can solve it and help me [message #231664] Wed, 18 April 2007 00:11 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

I want to write a procedure to insert data into a table HR_IDCARD with

structure

CREATE TABLE HR_IDCARD
( HR_EMPCODE NUMBER(15) NOT NULL,
HR_DATE DATE,
HR_TIMEIN1 DATE,
HR_TIMEOUT1 DATE,
HR_TIMEIN2 DATE,
HR_TIMEOUT2 DATE,
HR_TIMEIN3 DATE,
HR_TIMEOUT3 DATE )

from a temperory table with structure

CREATE GLOBAL TEMPORARY TABLE TMP_HR_SWAP
(
EVENTDATE DATE NOT NULL,
EVENTTIME DATE NOT NULL,
EMPCODE VARCHAR2(20 BYTE) NOT NULL
) ON COMMIT PRESERVE ROWS;

there should be only one entry for a particular date in HR_IDCARD table but

in temporary table there will be many entries for a particular date with different time that which will be for 3 time-ins time-outs .


Can any one help me

[Updated on: Wed, 18 April 2007 05:58]

Report message to a moderator

Re: challenging question can any one can solve it and help me [message #231677 is a reply to message #231664] Wed, 18 April 2007 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
4 time-ins time-outs but there are only 3*2 columns in the target table!!!

Regards
Michel
Re: challenging question can any one can solve it and help me [message #231704 is a reply to message #231664] Wed, 18 April 2007 03:50 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
RTFM - look for multitable insert in documentation.
Re: challenging question can any one can solve it and help me [message #231711 is a reply to message #231704] Wed, 18 April 2007 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you use multitable insert here?
You insert in a lone table but multitimes in the same row.

Regards
Michel
Re: challenging question can any one can solve it and help me [message #231713 is a reply to message #231711] Wed, 18 April 2007 04:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
In thinking in the direction of pivoting the temp table to get the different time_in/time_out combinations in a single row...or am I just misreading the question here?

MHE
Re: challenging question can any one can solve it and help me [message #231715 is a reply to message #231713] Wed, 18 April 2007 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I think it is a good avenue.

Regards
Michel
Re: challenging question can any one can solve it and help me [message #231823 is a reply to message #231664] Wed, 18 April 2007 09:16 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
rakeshramm,
I am not thrilled with you design. What happens when the user suddenly has four punch in/outs (They went the the doctor during work time), then your design blows up. I would have a one to many table that contains all the punches with a link back to the employee table, this would give total flexibility not matter how many/few times they punch. Experience will show you that you never design for the expected data, you have to design for the edge conditions.
Previous Topic: SQL CASE statement (revised to follow board rules)
Next Topic: pl/sql code
Goto Forum:
  


Current Time: Tue Dec 06 10:11:56 CST 2016

Total time taken to generate the page: 0.17558 seconds