Home » SQL & PL/SQL » SQL & PL/SQL » Set a range into in time (Forms,)
Set a range into in time [message #652318] Tue, 07 June 2016 03:06 Go to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Hello friends,
Happy holy Ramadan mubarak,
Here I share a problem with you. In our office there are some section which are shifting duty i.e. dyeing, printing etc. Every Friday their shift changes. When we change their shift, day shift in time is not there. We have to input both in time randomly within range 7:50:00 AM to 8:10:00 AM, I do it manually.

http://s33.postimg.org/4d72mmnlb/123.jpg

Is there any code that generate time randomly between 7:50:00 AM to 8:10:00 AM. I dont know the exact sytex of input a range of time data to a column, my ATTENDANCE_DETAILS table column is below
SQL> desc ATTENDANCE_DETAILS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ATT_DATE                                           DATE
 EMPNO                                              VARCHAR2(20)
 IN_TIME                                            DATE
 OUT_TIME                                           DATE
 OTHOUR                                             NUMBER
 LATE                                               NUMBER
 STATUS                                             VARCHAR2(10)
 IN_TIME2                                           DATE
 OUT_TIME2                                          DATE
 OTHOUR2                                            NUMBER
 STATUS2                                            VARCHAR2(10)
So, which empno is null in_time and att_date is '13-06-15' they should be update

Report message to a moderator

Re: Set a range into in time [message #652320 is a reply to message #652318] Tue, 07 June 2016 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Quote:
Is there any code that generate time randomly between 7:50:00 AM to 8:10:00 AM.


SQL> select to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS') from dual;
TO_CHAR(
--------
08:09:36

1 row selected.

SQL> /
TO_CHAR(
--------
07:56:16

1 row selected.

SQL> /
TO_CHAR(
--------
08:06:27

1 row selected.

SQL> /
TO_CHAR(
--------
07:56:14

1 row selected.

SQL> /
TO_CHAR(
--------
08:08:40

1 row selected.

SQL> /
TO_CHAR(
--------
07:55:33

1 row selected.

SQL> /
TO_CHAR(
--------
07:58:08

1 row selected.

Report message to a moderator

Re: Set a range into in time [message #652327 is a reply to message #652320] Tue, 07 June 2016 06:17 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Michel,
Nice code!!

Report message to a moderator

Re: Set a range into in time [message #652378 is a reply to message #652327] Tue, 07 June 2016 23:34 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Thank's Michel

I tried this code but it only select, not updated. how can I update my in_time? sorry I am just learning coding. 
INSERT INTO AD.IN_TIME to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS') from dual;
SELECT AD.EMPNO, AD.OUT_TIME, AD.IN_TIME
FROM ATTENDANCE_DETAILS AD
WHERE AD.OUT_TIME IS NOT NULL
and ad.in_time is null
AND AD.ATT_DATE= TO_DATE ('13-06-2015', 'dd-MM-yyyy');
I also tried this code
SQL> UPDATE ATTENDANCE_DETAILS AD
  2    SET  AD.IN_TIME  to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS') fr
om dual
  3    where AD.ATT_DATE= TO_DATE ('13-06-15', 'dd-MM-yy')
  4    AND AD.OUT_TIME IS NOT NULL
  5    and ad.in_time is null;
  SET  AD.IN_TIME  to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS') from du
                   *
ERROR at line 2:
ORA-00927: missing equal sign

[Updated on: Tue, 07 June 2016 23:46]

Report message to a moderator

Re: Set a range into in time [message #652382 is a reply to message #652378] Wed, 08 June 2016 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to think a little bit... or wait for Barbara to spoonfeed you.

Report message to a moderator

Re: Set a range into in time [message #652386 is a reply to message #652378] Wed, 08 June 2016 01:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
Salehin wrote on Tue, 07 June 2016 21:34

SQL> UPDATE ATTENDANCE_DETAILS AD
  2    SET  AD.IN_TIME  to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS') fr
om dual
  3    where AD.ATT_DATE= TO_DATE ('13-06-15', 'dd-MM-yy')
  4    AND AD.OUT_TIME IS NOT NULL
  5    and ad.in_time is null;
  SET  AD.IN_TIME  to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS') from du
                   *
ERROR at line 2:
ORA-00927: missing equal sign


This tells you that you are missing an equal sign (=) on line 2:

ERROR at line 2:
ORA-00927: missing equal sign

This is line 2 that is missing the = sign:

SET AD.IN_TIME to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS') fr
om dual

The proper syntax for an update statement is:

UPDATE ...
SET ... = ...

So, you need to put the = sign where it belongs in line 2:

SET AD.IN_TIME = ...

That will fix that error, then you will have to figure out what causes the next error and so on.

You should probably be truncating the ad.in_time, not sysdate, which was just used as an example and you don't need from dual, which was also just part of the example to show how to generate the date and time, not how to update.

Filling in a random time sounds like a strange business requirement.

Report message to a moderator

Re: Set a range into in time [message #652387 is a reply to message #652386] Wed, 08 June 2016 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 

UPDATE ATTENDANCE_DETAILS AD
SET  AD.IN_TIME = to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS')
where AD.ATT_DATE= TO_DATE ('13-06-15', 'dd-MM-yy')
AND AD.OUT_TIME IS NOT NULL
and ad.in_time is null;

Report message to a moderator

Re: Set a range into in time [message #652388 is a reply to message #652386] Wed, 08 June 2016 01:13 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Thank's
I tired this code

SQL> UPDATE ATTENDANCE_DETAILS AD
  2    SET  AD.IN_TIME = to_char(trunc(AD.IN_TIME)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS'
) 
  3    where AD.ATT_DATE= TO_DATE ('13-06-15', 'dd-MM-yy')
  4    AND AD.OUT_TIME IS NOT NULL
  5    and ad.in_time is null;

132 rows updated.
But I don't find any update.

thank's Michel I tried your one

SQL> UPDATE ATTENDANCE_DETAILS AD
  2  SET  AD.IN_TIME = to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS')
  3  where AD.ATT_DATE= TO_DATE ('13-06-15', 'dd-MM-yy')
  4  AND AD.OUT_TIME IS NOT NULL
  5  and ad.in_time is null;
SET  AD.IN_TIME = to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS')
                  *
ERROR at line 2:
ORA-01843: not a valid month

[Updated on: Wed, 08 June 2016 01:15]

Report message to a moderator

Re: Set a range into in time [message #652389 is a reply to message #652388] Wed, 08 June 2016 01:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
It shows that 132 rows were updated. How did you check for updates? Did you display it so that it shows the time and not just the date?

Report message to a moderator

Re: Set a range into in time [message #652394 is a reply to message #652389] Wed, 08 June 2016 01:32 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
 
EMPNO                IN_TIME   OUT_TIME
-------------------- --------- ---------
00032157                       01-JUN-15
00033379                       13-JUN-15
00033405                       13-JUN-15
00001609                       13-JUN-15
00001677                       13-JUN-15
00001694                       13-JUN-15
00001699                       13-JUN-15
00001701                       13-JUN-15
00001719                       13-JUN-15
00001726                       14-JUN-15
00001731                       13-JUN-15

EMPNO                IN_TIME   OUT_TIME
-------------------- --------- ---------
00004473                       13-JUN-15
00075713                       13-JUN-15
00073194                       14-JUN-15
00062509                       14-JUN-15
00062521                       13-JUN-15
00062587                       14-JUN-15
00050899                       14-JUN-15
00051909                       14-JUN-15
00047087                       14-JUN-15
00038722                       14-JUN-15
00037360                       13-JUN-15

132 rows selected.
not updated. here I see may be some problem in date. It show 13-JUN-15, 14-JUN-15

[Updated on: Wed, 08 June 2016 01:37]

Report message to a moderator

Re: Set a range into in time [message #652395 is a reply to message #652388] Wed, 08 June 2016 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have not the same message than you:
SQL> UPDATE ATTENDANCE_DETAILS AD
  2  SET  AD.IN_TIME = to_char(trunc(sysdate)+dbms_random.value(28200,29400)/86400,'HH24:MI:SS')
  3  where AD.ATT_DATE= TO_DATE ('13-06-15', 'dd-MM-yy')
  4  AND AD.OUT_TIME IS NOT NULL
  5  and ad.in_time is null;
UPDATE ATTENDANCE_DETAILS AD
       *
ERROR at line 1:
ORA-00942: table or view does not exist

Report message to a moderator

Re: Set a range into in time [message #652397 is a reply to message #652394] Wed, 08 June 2016 02:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
You need to add the random value to the att_date, not the null in_time. That is why you were getting the null values in the updated column.

You should not use to_char, as you are inserting into a date column. That is what causes the error about not being a valid month.

Also, you should use full year formats instead of 2 digits, as it may not know 2015 from 1915.

Please copy and paste and test the corrected code below.

UPDATE attendance_details ad
SET    ad.in_time = TRUNC (ad.att_date) + (DBMS_RANDOM.VALUE (28200,29400)/86400)
WHERE  ad.att_date = TO_DATE ('13-06-2015', 'dd-MM-yyyy')
AND    ad.out_time IS NOT NULL
AND    ad.in_time IS NULL;

Report message to a moderator

Re: Set a range into in time [message #652401 is a reply to message #652397] Wed, 08 June 2016 03:53 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Wow!! Its working, but I want to set late='00:00:00' and trying to to this syntex

SQL> UPDATE attendance_details ad
  2  SET    ad.in_time2= TRUNC (ad.att_date) + (DBMS_RANDOM.VALUE (28200,29400)/86400),
  3  ad.in_time= TRUNC (ad.att_date) + (DBMS_RANDOM.VALUE (28200,29400)/86400), ad.status='P', ad.st
atus2='P', ad.late=to_char('00:00:00','HH24:MI:SS')
  4  WHERE  ad.att_date = TO_DATE ('13-06-2015', 'dd-MM-yyyy')
  5  AND    ad.out_time IS NOT NULL
  6  AND    ad.in_time2 IS NULL;
ad.in_time= TRUNC (ad.att_date) + (DBMS_RANDOM.VALUE (28200,29400)/86400), ad.status='P', ad.status2
                                                                                                    
ERROR at line 3:
ORA-01722: invalid number

Report message to a moderator

Re: Set a range into in time [message #652402 is a reply to message #652401] Wed, 08 June 2016 04:13 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just TRUNC the date value.
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate, trunc(sysdate) from dual;

SYSDATE             TRUNC(SYSDATE)
------------------- -------------------
08.06.2016 11:13:34 08.06.2016 00:00:00

SQL>

Report message to a moderator

Re: Set a range into in time [message #652407 is a reply to message #652401] Wed, 08 June 2016 05:00 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Salehin wrote on Wed, 08 June 2016 10:53
Wow!! Its working, but I want to set late='00:00:00' and trying to to this syntex

Based on your first post, the column LATE has data type NUMBER.
'00:00:00' is a string literal. It is not a number. Or is it meant to be a simple zero (0)? Then, why not use it directly?
ad.late=0
Maybe you should realize the difference between storing data (NUMBER) and their presentation (which may be done by Forms in any format, e.g. 01:02:03 for 3723).

Additionally, you really should get acquainted with the functions which you use.
Some nice guys from Oracle already did that job of documenting them and their output is available e.g. online on http://docs.oracle.com/en/.
For the version 12c, the function TO_CHAR has three implementations:
http://docs.oracle.com/database/121/SQLRF/functions215.htm#SQLRF06128 for string parameter (note that there are no other parameters)
http://docs.oracle.com/database/121/SQLRF/functions216.htm#SQLRF06129 for date parameter (with conditional format parameter)
http://docs.oracle.com/database/121/SQLRF/functions217.htm#SQLRF06130 for numeric parameter (with conditional format parameter)
So, you pass it a string and a format parameter. You may be calling the one with the first date or numeric parameter; regarding the message, Oracle chooses the latter conversion, so you do this:
to_char(TO_NUMBER('00:00:00'),'HH24:MI:SS')
which fails as '00:00:00' is not a NUMBER.

Report message to a moderator

Re: Set a range into in time [message #652409 is a reply to message #652407] Wed, 08 June 2016 06:07 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
flyboy

Based on your first post, the column LATE has data type NUMBER.


Ooops! It really is! I should have read the whole discussion, instead of jumping into the middle of it with a useless comment. Sorry.

Report message to a moderator

Re: Set a range into in time [message #652411 is a reply to message #652409] Wed, 08 June 2016 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Don't be sorry, there would have been no error if he had posted the CREATE TABLE statement.

Report message to a moderator

Re: Set a range into in time [message #652426 is a reply to message #652411] Wed, 08 June 2016 11:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
This is from another of his posts.

http://www.orafaq.com/forum/m/652012/#msg_652012

SQL> desc ATTENDANCE_DETAILS;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------
 ATT_DATE                                           DATE
 EMPNO                                              VARCHAR2(20)
 IN_TIME                                            DATE
 OUT_TIME                                           DATE
 OTHOUR                                             NUMBER
 LATE                                               NUMBER
 STATUS                                             VARCHAR2(10)
 MANUAL_STATUS                                      VARCHAR2(20)
 DEDUCT_VAL                                         NUMBER(3)
 IN_TIME2                                           DATE
 OUT_TIME2                                          DATE
 OTHOUR2                                            NUMBER
 NOROT                                              NUMBER
 EXTRAOT                                            NUMBER
 LATE2                                              NUMBER
 STATUS2                                            VARCHAR2(10)
 MANUAL_ATT                                         VARCHAR2(20)
 DEDUCT_DAYS                                        NUMBER
 REMARKS                                            VARCHAR2(250)
 S_OT                                               NUMBER
 FLOOR_DESC                                         VARCHAR2(15)
 S_OUT_TIME                                         DATE
 S_IN_TIME                                          DATE
 ADVANCE                                            NUMBER
 LEAVE                                              VARCHAR2(5)

Report message to a moderator

Re: Set a range into in time [message #652431 is a reply to message #652426] Wed, 08 June 2016 17:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
This is from another of his posts.


which does not contain CREATE TABLE and INSERT as you pointed OP to.
If it takes for him as much time as to understand what this means as it takes for him to understand UPDATE syntax I think we'll all be dead before it happens.

Report message to a moderator

icon14.gif  Re: Set a range into in time [message #652435 is a reply to message #652431] Wed, 08 June 2016 21:54 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Thank you all for this support. God bless you Smile .
I get what I want, pray for me that I can learn pl/sql.

Report message to a moderator

Re: Set a range into in time [message #652440 is a reply to message #652435] Thu, 09 June 2016 00:03 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Salehin

God bless (...) I get what I want (...)


Right; minimum effort, maximum result. God must be merciful (while Godess' name is Barbara).

Report message to a moderator

Re: Set a range into in time [message #653742 is a reply to message #652440] Mon, 18 July 2016 02:50 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
How can I add another column shift_code from emp_official?

Report message to a moderator

Re: Set a range into in time [message #653744 is a reply to message #653742] Mon, 18 July 2016 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another? We have none till now.

Report message to a moderator

Re: Set a range into in time [message #653745 is a reply to message #653742] Mon, 18 July 2016 03:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
Salehin wrote on Mon, 18 July 2016 00:50
How can I add another column shift_code from emp_official?


Add shift_code to what in what way? Do you want to update the emp_official column from something to something or apply a filter condition or what? Please post a complete example that includes create table statements, insert statements for data, the results that you want, what you have tried, and what results you got.

Report message to a moderator

Re: Set a range into in time [message #653747 is a reply to message #653745] Mon, 18 July 2016 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please post a complete example that includes create table statements, insert statements for data, the results that you want, what you have tried, and what results you got.


Good luck to get it from him!

Report message to a moderator

Re: Set a range into in time [message #653748 is a reply to message #653747] Mon, 18 July 2016 03:19 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
 
UPDATE attendance_details ad
SET    ad.in_time2= TRUNC (ad.att_date) + (DBMS_RANDOM.VALUE (28200,29400)/86400),
ad.in_time= TRUNC (ad.att_date) + (DBMS_RANDOM.VALUE (28200,29400)/86400), 
ad.status='P',ad.status2='P', ad.late=0, ad.late2=0 
WHERE  ad.att_date = TO_DATE ('16-07-2016', 'dd-MM-yyyy')
AND    ad.out_time IS NOT NULL
AND    ad.in_time2 IS NULL;
In my office I have two shift i.e day shift, Night shift and they have code for day 'SH105' and night 'SH108' but this column is in emp_official table. Now how can I add this shift code to my syntex.

thank's in advance.

Report message to a moderator

Re: Set a range into in time [message #653753 is a reply to message #653748] Mon, 18 July 2016 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As always, not what has been asked.

Report message to a moderator

Re: Set a range into in time [message #653758 is a reply to message #653753] Mon, 18 July 2016 05:48 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
salehin,

What is the difference between in_time2 and in_time. I don't know if your aware but two calls to DBMS_RANDOM will produce 2 different values (see below). Also what links emp_official to the attendance_details table? The task is easy to do, just need some more information.

test>select DBMS_RANDOM.VALUE (28200,29400),DBMS_RANDOM.VALUE (28200,29400) from dual;

DBMS_RANDOM.VALUE(28200,29400) DBMS_RANDOM.VALUE(28200,29400)
------------------------------ ------------------------------
28612.4758 28668.7093

[Updated on: Mon, 18 July 2016 05:48]

Report message to a moderator

icon5.gif  Re: Set a range into in time [message #653833 is a reply to message #653758] Wed, 20 July 2016 00:34 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Can anyone give me a sample of a question? Embarassed

Report message to a moderator

Re: Set a range into in time [message #653834 is a reply to message #653833] Wed, 20 July 2016 00:36 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Bill yeah I want to link emp_official table to my current syntax. No problem if in_time and in_time2 is differ. thank's

Report message to a moderator

Re: Set a range into in time [message #653838 is a reply to message #653833] Wed, 20 July 2016 01:54 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Have you considered consulting the documentation (available under the links I provided earlier) about UPDATE statement and all its possibilities?

So, for the last time, I will navigate you directly to the UPDATE statement for 12c: http://docs.oracle.com/database/121/SQLRF/statements_10008.htm#SQLRF01708
(by the way, I cannot find whether you posted the Oracle version you use; I used the latest one anyway)
It seems like you want to use correlated subquery in the SET clause. There are also SQL examples on the bottom of that page.

Unfortunately, people here can work only with known objects - tables with known structure (columns including type) and sample data for illustrating the requirements.
You provided only table name which is quite useless, do you not think?

Report message to a moderator

Re: Set a range into in time [message #653948 is a reply to message #653838] Sat, 23 July 2016 06:35 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
 
SQL> UPDATE attendance_details ad
  2  SET    ad.in_time2= TRUNC (ad.att_date) + (DBMS_RANDOM.VALUE (28200,29400)/86400),
  3  ad.in_time= TRUNC (ad.att_date) + (DBMS_RANDOM.VALUE (28200,29400)/86400), 
  4  ad.status='P',ad.status2='P', ad.late=0, ad.late2=0 
  5  WHERE  empno IN
  6  (select eo.empno
  7  from emp_official eo
  8  where eo.shift_code='SH105'
  9  and ad.att_date = TO_DATE ('09-01-2016', 'dd-MM-yyyy')
 10  AND    ad.out_time IS NOT NULL
 11  AND    ad.in_time2 IS NULL)
 12  /

110 rows updated.

Thank you everybody finally I done it.
Bless for me

Report message to a moderator

Re: Set a range into in time [message #653949 is a reply to message #653948] Sat, 23 July 2016 06:54 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Your mother should be proud of you for this accomplishment after a month & a half effort.

Report message to a moderator

Previous Topic: SQL query for formatted data display
Next Topic: Break a > 300k CLOB string into 32k chunks
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 02:01:30 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.