Home » Developer & Programmer » Forms » Run with a single Code (SQL)
 Run with a single Code [message #651622] |
Fri, 20 May 2016 22:36  |
 |
Salehin
Messages: 63
Registered: March 2013
Location: Chittagong, Bangladesh
|
Member |
|
|
Dear brother & Sister, I am facing a small problem in my office, we have separate column for separate section i.e. Cutting, Finishing, Quality, Operator etc.
I have to run my code separately for each section. Is there any code to run in one time, I run N_GROSS_OP for Operator, N_GROSS_qc for Qualtiy, N_GROSS_fin for Finishing, N_GROSS_cut for Cutting.
UPDATE emp_payment p
SET p.N_GROSS_OP = p.N_GROSS_OP + (((p.N_GROSS_OP - 1100) / 1.4) * .07),
p.gross=p.N_GROSS_op
WHERE empno IN
(SELECT o.empno
FROM emp_official o, emp_personal ep
WHERE o.joining_date BETWEEN '01-may-2015' AND '31-may-2015'
AND O.COMPANY_NAME='Clifton Apparels Ltd'
AND EP.STATUS='Active'
and o.section_name = 'Sewing'
and o.ot_ent = 'Yes'
and o.empno=ep.empno)
/
I attach the emp_payment form.
Please help
|
|
|
|
| Re: Run with a single Code [message #651649 is a reply to message #651622] |
Sun, 22 May 2016 01:11  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You have an error in your code. You need to use TO_DATE and the corresponding format for your date. I have corrected that in the code below.
If I understand your question, you are currently running multiple separate update statements, such as the two below, where one uses the column n_gross_op and the value 'Sewing' for section_name, while the other uses the column n_gross_qc and the value 'Quality' for section_name. You have various such pairs of column and value and you want to know if you can create one update statement that will do the same thing as running all of them separately. I don't see any beneficial way to do this. There is nothing wrong with running multiple update statements. However, somebody else may have some idea. Since you have attached an fmb file, this is apparently a Forms question and there may be somebody who is expert with Forms that can suggest something that can be done within Forms that cannot be done from just SQL or PL/SQL.
UPDATE emp_payment p
SET p.N_GROSS_OP = p.N_GROSS_OP + (((p.N_GROSS_OP - 1100) / 1.4) * .07),
p.gross=p.N_GROSS_op
WHERE empno IN
(SELECT o.empno
FROM emp_official o, emp_personal ep
WHERE o.joining_date BETWEEN TO_DATE ('01-may-2015', 'dd-mon-yyyy')
AND TO_DATE ('31-may-2015', 'dd-mon-yyyy')
AND O.COMPANY_NAME='Clifton Apparels Ltd'
AND EP.STATUS='Active'
and o.section_name = 'Sewing'
and o.ot_ent = 'Yes'
and o.empno=ep.empno);
UPDATE emp_payment p
SET p.N_GROSS_QC = p.N_GROSS_QC + (((p.N_GROSS_QC - 1100) / 1.4) * .07),
p.gross=p.N_GROSS_QC
WHERE empno IN
(SELECT o.empno
FROM emp_official o, emp_personal ep
WHERE o.joining_date BETWEEN TO_DATE ('01-may-2015', 'dd-mon-yyyy')
AND TO_DATE ('31-may-2015', 'dd-mon-yyyy')
AND O.COMPANY_NAME='Clifton Apparels Ltd'
AND EP.STATUS='Active'
and o.section_name = 'Quality'
and o.ot_ent = 'Yes'
and o.empno=ep.empno);
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Run with a single Code [message #651718 is a reply to message #651678] |
Mon, 23 May 2016 15:30 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
A merge would work, as shown below. I don't know if it would be any more efficient. You might test and see.
-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM emp_payment ORDER BY empno
2 /
EMPNO GROSS N_GROSS_OP N_GROSS_QC N_GROSS_FIN N_GROSS_CUT
---------- ---------- ---------- ---------- ----------- -----------
75321 0 5700
75375 0 6300
75376 0 4000
75381 0 4000
75382 0 3000
75641 0 2000
75656 0 2000
99999 0 1000
8 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM emp_official ORDER BY empno
2 /
EMPNO JOINING_DATE COMPANY_NAME SECTION_N OT_
---------- --------------- -------------------- --------- ---
75321 Sat 23-May-2015 Clifton Apparels Ltd Operator Yes
75375 Sat 23-May-2015 Clifton Apparels Ltd Operator Yes
75376 Sat 23-May-2015 Clifton Apparels Ltd Cutting Yes
75381 Sat 23-May-2015 Clifton Apparels Ltd Cutting Yes
75382 Sat 23-May-2015 Clifton Apparels Ltd Finishing Yes
75641 Sat 23-May-2015 Clifton Apparels Ltd Quality Yes
75656 Sat 23-May-2015 Clifton Apparels Ltd Quality Yes
99999 Sat 23-Apr-2016 Clifton Apparels Ltd Quality Yes
8 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM emp_personal ORDER BY empno
2 /
EMPNO STATUS
---------- ------
75321 Active
75375 Active
75376 Active
75381 Active
75382 Active
75641 Active
75656 Active
99999 Active
8 rows selected.
-- merge and results:
SCOTT@orcl_12.1.0.2.0> MERGE INTO emp_payment p
2 USING (SELECT o.empno, o.section_name
3 FROM emp_official o, emp_personal ep
4 WHERE o.joining_date BETWEEN TO_DATE ('01-may-2015', 'dd-mon-yyyy')
5 AND TO_DATE ('31-may-2015', 'dd-mon-yyyy')
6 AND O.COMPANY_NAME = 'Clifton Apparels Ltd'
7 AND EP.STATUS = 'Active'
8 and o.ot_ent = 'Yes'
9 and o.empno = ep.empno) t
10 ON (p.empno = t.empno)
11 WHEN MATCHED THEN UPDATE SET
12 p.n_gross_op =
13 (CASE WHEN t.section_name = 'Operator'
14 THEN p.N_GROSS_OP + (((p.N_GROSS_OP - 1100) / 1.4) * .07)
15 ELSE p.n_gross_op
16 END),
17 p.n_gross_qc =
18 (CASE WHEN t.section_name = 'Quality'
19 THEN p.N_GROSS_qc + (((p.N_GROSS_qc - 1100) / 1.4) * .07)
20 ELSE p.n_gross_qc
21 END),
22 p.n_gross_fin =
23 (CASE WHEN t.section_name = 'Finishing'
24 THEN p.N_GROSS_fin + (((p.N_GROSS_fin - 1100) / 1.4) * .07)
25 ELSE p.n_gross_fin
26 END),
27 p.n_gross_cut =
28 (CASE WHEN t.section_name = 'Cutting'
29 THEN p.N_GROSS_cut + (((p.N_GROSS_cut - 1100) / 1.4) * .07)
30 ELSE p.n_gross_cut
31 END),
32 p.gross =
33 (CASE WHEN t.section_name = 'Operator' THEN p.n_gross_op
34 WHEN t.section_name = 'Quality' THEN p.n_gross_qc
35 WHEN t.section_name = 'Finishing' THEN p.n_gross_fin
36 WHEN t.section_name = 'Cutting' THEN p.n_gross_cut
37 ELSE p.gross
38 END)
39 /
7 rows merged.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM emp_payment ORDER BY empno
2 /
EMPNO GROSS N_GROSS_OP N_GROSS_QC N_GROSS_FIN N_GROSS_CUT
---------- ---------- ---------- ---------- ----------- -----------
75321 5700 5930
75375 6300 6560
75376 4000 4145
75381 4000 4145
75382 3000 3095
75641 2000 2045
75656 2000 2045
99999 0 1000
8 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Run with a single Code [message #651743 is a reply to message #651730] |
Tue, 24 May 2016 05:02 |
|
Salehin
Messages: 63
Registered: March 2013
Location: Chittagong, Bangladesh
|
Member |
|
|
here is my code where I select N_GROSS_op for Operator which can show only sewing section. But I need all section will be show.
select ep.empno, ep.b_name, ep.card_no,ep.first_name||' ' ||ep.middle_name||' '||ep.last_name ename,
ep.religion_name, em.des_name,ep.age, em.N_GROSS_op , eo.section_name,eo.joining_date, em.grade,eo.floor_desc
from emp_personal ep, emp_official eo, emp_payment em
where ep.empno = eo.empno
and ep.empno = em.empno(+)
--and eo.section_name = 'Sewing'
and eo.joining_date between '1-APR-2015' and '30-APR-2015'
--and ((eo.joining_date between '1-may-2014' and '31-may-2014') or (eo.joining_date between '1-may-2015' and '31-may-2015'))
and ep.status = 'Active'
and eo.ot_ent = 'Yes'
--and em.grade between '3' and '6'
--and eo.floor_desc=:floor
and eo.company_name = :unit
order by ep.empno
[Updated on: Tue, 24 May 2016 06:31] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jul 31 05:03:29 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
