|
|
|
|
|
|
|
|
|
|
|
|
| Re: Updating Status W to A [message #652082 is a reply to message #652079] |
Wed, 01 June 2016 02:53   |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Also indent your code properly - the AND on line 8 should be indented to the same level as the 2nd WHERE.
Can lv_from and lv_to have times other than midnight?
If so your query will ignore all values on 31st Jan after midnight, which I doubt is what you want.
|
|
|
|
| Re: Updating Status W to A [message #652149 is a reply to message #652012] |
Thu, 02 June 2016 15:01 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your problem is not totally clear. Part of it is clear, but some of it is not. It would enable us to understand the whole problem and help you better if you were to provide create table and insert statements for your starting data and post an example of what you want after the update, in addition to your worded description. It makes it simpler if you can limit it to just the necessary columns, such as any columns to be updated, any columns needed to determine which columns to be updated, any columns needed to join the tables, and any key columns. It also helps if your data and attempted update match, such as updating data for the same month and year. It is useless to try to run an update statement for dates in January of 2015 on data for May of 2016.
I gather that you have a leave_entry_details table and you need to process all rows in that table, using that data to update some of the columns in some of the rows of the attendance_details table. I gather that, for each row in the leave_entry_details table, you want to update both the status and status2 columns to the value of 'A' for the rows in the attendance_details table where the empno in the attendance_details table is the same as the empno in that row of the leave_entry_details table and the att_date in the attendance_details table is between the lv_from and lv_to dates in that row of the leave_entry_details_table.
Since the empno and lv_from and lv_date must come from the same row and there might be multiple rows for the same empno, I think this situation is a good candidate for a MERGE statement. I have provided some limited sample data below and shown how to update the status and status2 to 'A' for the appropriate employee and dates. I am just trying to address how to update the status in general for provided dates in general here. The rest of your problem, as to what you do or do not want to update from what value to what value, depending on whether or not one of the dates is a Friday and what value you want for number of days, you will have to provide further clarification on that, if you cannot figure out how to modify the merge statement to include those things.
-- starting data:
SCOTT@orcl_12.1.0.2.0> SELECT empno, lv_from, lv_to
2 FROM leave_entry_details
3 /
EMPNO LV_FROM LV_TO
-------------------- --------------- ---------------
00000624 Wed 18-May-2016 Fri 20-May-2016
00000624 Sun 22-May-2016 Tue 24-May-2016
2 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT att_date, empno, status, status2
2 FROM attendance_details
3 /
ATT_DATE EMPNO STATUS STATUS2
--------------- -------------------- ---------- ----------
Tue 17-May-2016 00000624
Wed 18-May-2016 00000624
Thu 19-May-2016 00000624
Fri 20-May-2016 00000624
Sat 21-May-2016 00000624
Sun 22-May-2016 00000624
Mon 23-May-2016 00000624
Tue 24-May-2016 00000624
8 rows selected.
-- merge statement to update rows:
SCOTT@orcl_12.1.0.2.0> MERGE INTO attendance_details ad
2 USING (SELECT empno, lv_from, lv_to
3 FROM leave_entry_details) led
4 ON (ad.empno = led.empno AND
5 ad.att_date BETWEEN led.lv_from AND led.lv_to)
6 WHEN MATCHED THEN UPDATE SET ad.status = 'A', ad.status2 = 'A'
7 /
6 rows merged.
-- results:
SCOTT@orcl_12.1.0.2.0> SELECT att_date, empno, status, status2
2 FROM attendance_details
3 /
ATT_DATE EMPNO STATUS STATUS2
--------------- -------------------- ---------- ----------
Tue 17-May-2016 00000624
Wed 18-May-2016 00000624 A A
Thu 19-May-2016 00000624 A A
Fri 20-May-2016 00000624 A A
Sat 21-May-2016 00000624
Sun 22-May-2016 00000624 A A
Mon 23-May-2016 00000624 A A
Tue 24-May-2016 00000624 A A
8 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Updating Status W to A [message #652233 is a reply to message #652213] |
Sun, 05 June 2016 01:13 |
 |
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
|
Member |
|
|
Sorry I tried but failed 
Where should I write this code?
Tried on top of the code.
SQL> SET AD.REMARKS='Leave'
SP2-0735: unknown SET option beginning "AD.REMARKS..."
SQL> MERGE INTO attendance_details ad
2 USING (SELECT empno, lv_from, lv_to
3 FROM leave_entry_details) led
4 ON (ad.empno = led.empno AND
5 ad.att_date BETWEEN led.lv_from AND led.lv_to AND
6 ad.att_date BETWEEN TO_DATE ('01-apr-2015', 'dd-mon-yyyy')
7 AND TO_DATE ('30-apr-2015', 'dd-mon-yyyy'))
8 WHEN MATCHED THEN UPDATE SET ad.status = 'A', ad.status2 = 'A';
1742 rows merged.
SQL> commit;
Commit complete.
SQL>
Tried on bottom of code.
SQL> MERGE INTO attendance_details ad
2 USING (SELECT empno, lv_from, lv_to
3 FROM leave_entry_details) led
4 ON (ad.empno = led.empno AND
5 ad.att_date BETWEEN led.lv_from AND led.lv_to AND
6 ad.att_date BETWEEN TO_DATE ('01-apr-2015', 'dd-mon-yyyy')
7 AND TO_DATE ('30-apr-2015', 'dd-mon-yyyy'))
8 WHEN MATCHED THEN UPDATE SET ad.status = 'A', ad.status2 = 'A'
9 SET AD.REMARKS='Leave';
SET AD.REMARKS='Leave'
*
ERROR at line 9:
ORA-00933: SQL command not properly ended
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Updating Status W to A [message #653244 is a reply to message #653240] |
Thu, 30 June 2016 00:33 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Salehin wrote on Wed, 29 June 2016 20:58Is there any way to run your code to Oracle 8.1.7. As I said you earlier that my office database is running Oracle 8.1.7
No, you can't use MERGE on 8.1.7. It has been so long that I can't remember for sure, but I don't think you could even use the ANSI join syntax in 8.1.7. I don't have 8.1.7 to test with, so I can't be sure what will run on it. The following is about the simplest update statement that I can think of that might work on such an outdated version.
UPDATE attendance_details ad
SET ad.status = 'A', ad.status2 = 'A', ad.remarks = 'Leave'
WHERE EXISTS
(SELECT *
FROM leave_entry_details d, emp_official o
WHERE ad.empno = d.empno
AND d.empno = o.empno
AND o.company_name = 'Clifton Apparels Ltd'
AND ad.att_date BETWEEN d.lv_from AND d.lv_to)
AND ad.att_date BETWEEN TO_DATE ('01-01-2015', 'dd-mm-yyyy')
AND TO_DATE ('31-01-2015', 'dd-mm-yyyy');
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Updating Status W to A [message #653365 is a reply to message #653355] |
Mon, 04 July 2016 02:59 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Are you trying to update rows that are locked by another session?
Run this when it hangs to check:
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
, try it yourself. All that's needed is adding
