Home » Developer & Programmer » Forms » Run with a single Code (SQL)
Run with a single Code [message #651622] Fri, 20 May 2016 22:36 Go to next message
Salehin
Messages: 62
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

Report message to a moderator

Re: Run with a single Code [message #651649 is a reply to message #651622] Sun, 22 May 2016 01:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
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);


Report message to a moderator

Re: Run with a single Code [message #651650 is a reply to message #651622] Sun, 22 May 2016 02:26 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
And another bug: your code will miss out everyone recruited on 31 May. Days are twenty four hours long, you know:
orclz>
orclz> select sysdate from dual;

SYSDATE
-------------------
2016-05-22:08:23:58

orclz> select 'x' from dual where sysdate between to_date('21-may-2016','dd-mon-yyyy') and  to_date('22-may-2016','dd-mon-yyyy');

no rows selected

orclz>

Report message to a moderator

Re: Run with a single Code [message #651675 is a reply to message #651650] Mon, 23 May 2016 02:45 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
Might not be a bug in this case. If I had a joining_date column I'd set it to trunc(sysdate). It's a case where time really shouldn't matter.

As for the original problem. As Barbara said here, and I said in an earlier topic, there is no benefit to coding a single update for this.
It does seem like you shouldn't have multiple gross columns, they appear to be mutually exclusive, so if you combined them into a single column that would fix your issue.

Report message to a moderator

Re: Run with a single Code [message #651677 is a reply to message #651622] Mon, 23 May 2016 02:56 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
You could use this sort of logic to determine which column to change
update emp set sal=(case when job='CLERK' then 1000 else sal end),comm=(case when job='SALESMAN' then 2000 else comm end);
but this sort of convolution is necessary only because the table is not properly normalized.

Report message to a moderator

Re: Run with a single Code [message #651678 is a reply to message #651677] Mon, 23 May 2016 03:15 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's not that simple in this case because job is on another table.
If you wrote it as a merge you could do that

Report message to a moderator

Re: Run with a single Code [message #651718 is a reply to message #651678] Mon, 23 May 2016 15:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
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.

Report message to a moderator

Re: Run with a single Code [message #651719 is a reply to message #651718] Mon, 23 May 2016 23:25 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Wow!! Shocked

I am thinking how many time you spend for my issue? Its really spoon feeding.

Its working!

Thank you very much Barbara Boehmer for your nice effort. God bless you.

Problems are never ends.

New problem is in my emp_check report I have gross column, when I run this report only which section i.e N_GROSS_op for sewing, N_GROSS_qc for Quality etc. is selected that will come. I want on gross column to show every section. Is there any way?

I attach my rdf file

thanks in advance. Razz

Report message to a moderator

Re: Run with a single Code [message #651721 is a reply to message #651719] Tue, 24 May 2016 00:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
Senior Member
Salehin wrote on Mon, 23 May 2016 21:25

...New problem is in my emp_check report I have gross column, when I run this report only which section i.e N_GROSS_op for sewing, N_GROSS_qc for Quality etc. is selected that will come. I want on gross column to show every section. Is there any way?...


I don't understand what this means. Perhaps you can explain it more clearly or post an image. I also don't understand whether this is a problem with the merge or some query or with Forms or Reports. I don't use those, so I can't read the RDF. If this is a Forms or Reports problem, then there may be others that can help.

Report message to a moderator

Re: Run with a single Code [message #651722 is a reply to message #651721] Tue, 24 May 2016 00:41 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
As our company have different section i.e cutting, quality, finishing etc. when I run emp_check report file it shows gross only cutting section because I select n_gross_cut on query. Is there any way to show every section's gross on my report. I attach a picture of my report.
/forum/fa/13133/0/

[mod-edit: image inserted into message body by bb]
  • Attachment: 5%.jpg
    (Size: 179.29KB, Downloaded 3838 times)

[Updated on: Tue, 24 May 2016 00:50] by Moderator

Report message to a moderator

Re: Run with a single Code [message #651723 is a reply to message #651722] Tue, 24 May 2016 00:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
Senior Member
I believe I understand the problem now, that the values for the gross column are missing for all sections except cutting.

Is there some query that you can post that is run within this emp_check report that produces these results?

Report message to a moderator

Re: Run with a single Code [message #651726 is a reply to message #651723] Tue, 24 May 2016 01:10 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Yes, I upload the rdf file earlier.

Report message to a moderator

Re: Run with a single Code [message #651728 is a reply to message #651722] Tue, 24 May 2016 01:22 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
You have seen the logic applied by me and then by Barbara to update different columns depending on the value of another column. You need to apply the same logic to determine what value to display.
It really is time that you tried to write some SQL for yourself.

Report message to a moderator

Re: Run with a single Code [message #651730 is a reply to message #651726] Tue, 24 May 2016 01:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
Senior Member
Salehin wrote on Mon, 23 May 2016 23:10
Yes, I upload the rdf file earlier.


As I said previously, I have nothing with which to read the rdf file, so you need to post the SQL query by itself. As John said, you also need to post what you tried to fix it.

Report message to a moderator

Re: Run with a single Code [message #651743 is a reply to message #651730] Tue, 24 May 2016 05:02 Go to previous messageGo to next message
Salehin
Messages: 62
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

Re: Run with a single Code [message #651749 is a reply to message #651743] Tue, 24 May 2016 07:43 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use a CASE statement to return the appropriate gross column based on the section.
Or better yet - fix the data model and stop having seperate columns for the same bit of data.

Report message to a moderator

Re: Run with a single Code [message #651777 is a reply to message #651743] Tue, 24 May 2016 23:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
Senior Member
The following is a partial example of such a query using a case statement. You will need to add your other values to the case statement and other columns and conditions to your query. Please notice that I have corrected your dates again. You need to recognize that what you have been using are strings and not dates. You need to use to_date with a corresponding date format to avoid getting wrong results.

-- 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       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.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM emp_official ORDER BY empno
  2  /

     EMPNO JOINING_DATE    COMPANY_NAME         SECTION_N OT_
---------- --------------- -------------------- --------- ---
     75321 Sun 24-May-2015 Clifton Apparels Ltd Sewing    Yes
     75375 Sun 24-May-2015 Clifton Apparels Ltd Sewing    Yes
     75376 Sun 24-May-2015 Clifton Apparels Ltd Cutting   Yes
     75381 Sun 24-May-2015 Clifton Apparels Ltd Cutting   Yes
     75382 Sun 24-May-2015 Clifton Apparels Ltd Finishing Yes
     75641 Sun 24-May-2015 Clifton Apparels Ltd Quality   Yes
     75656 Sun 24-May-2015 Clifton Apparels Ltd Quality   Yes
     99999 Sun 24-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.


-- query:
SCOTT@orcl_12.1.0.2.0> select ep.empno,
  2  	    CASE WHEN eo.section_name = 'Sewing'    THEN em.n_gross_op
  3  		 WHEN eo.section_name = 'Quality'   THEN em.n_gross_qc
  4  		 WHEN eo.section_name = 'Finishing' THEN em.n_gross_fin
  5  		 WHEN eo.section_name = 'Cutting'   THEN em.n_gross_cut
  6  	    END gross,
  7  	    eo.section_name, eo.joining_date
  8  from   emp_personal ep, emp_official eo, emp_payment em
  9  where  ep.empno = eo.empno
 10  and    ep.empno = em.empno(+)
 11  and    eo.joining_date between TO_DATE ('1-MAY-2015', 'DD-MON-YYYY')
 12  				and TO_DATE ('31-MAY-2015', 'DD-MON-YYYY')
 13  and    ep.status = 'Active'
 14  and    eo.ot_ent = 'Yes'
 15  order  by ep.empno
 16  /

     EMPNO      GROSS SECTION_N JOINING_DATE
---------- ---------- --------- ---------------
     75321       5930 Sewing    Sun 24-May-2015
     75375       6560 Sewing    Sun 24-May-2015
     75376       4145 Cutting   Sun 24-May-2015
     75381       4145 Cutting   Sun 24-May-2015
     75382       3095 Finishing Sun 24-May-2015
     75641       2045 Quality   Sun 24-May-2015
     75656       2045 Quality   Sun 24-May-2015

7 rows selected.

Report message to a moderator

Re: Run with a single Code [message #651994 is a reply to message #651777] Tue, 31 May 2016 01:51 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Sorry I was busy that's why late.

Wow its working thank you again Barbara, but have a small problem under sewing section we have two category n_gross_op for Operator and n_gross_hlp for Asst.op how can I show them?

/forum/fa/13146/0/
  • Attachment: untitled.jpg
    (Size: 175.09KB, Downloaded 3491 times)

Report message to a moderator

Re: Run with a single Code [message #652003 is a reply to message #651994] Tue, 31 May 2016 03:26 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
Probably by using a variation on the case statement above. We don't you give it a try?

Report message to a moderator

Re: Run with a single Code [message #652004 is a reply to message #652003] Tue, 31 May 2016 03:30 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
I tried this

CASE 
WHEN eo.section_name = 'Sewing'    THEN em.n_gross_op 
WHEN em.grade between '3' and '6' THEN em.n_gross_op 
when eo.section_name = 'Sewing'    THEN em.n_gross_hlp
WHEN em.grade ='7' THEN em.n_gross_hlp
WHEN eo.section_name = 'Quality'   THEN em.n_gross_qc
WHEN eo.section_name = 'Finishing' THEN em.n_gross_fin
WHEN eo.section_name = 'Cutting'   THEN em.n_gross_cut
WHEN eo.section_name = 'Maintenance'   THEN em.new_gross_mng
when eo.section_name ='HRD' then em.new_gross_mng
END gross,


but there is no result. what will be?

Report message to a moderator

Re: Run with a single Code [message #652005 is a reply to message #652004] Tue, 31 May 2016 03:40 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
No result for all records? some records?
If some records then those records either don't match any of the WHEN clauses or the column you're returning in that case is null.
You need to work out which.

Report message to a moderator

Re: Run with a single Code [message #652202 is a reply to message #652005] Fri, 03 June 2016 23:47 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Sorry No result means some record what I needed,
What will be syntex if I query grade in the sewing section. In my syntex you see I tried that because only grade 7 will be n_gross_hlp under sewing section.

Report message to a moderator

Re: Run with a single Code [message #652204 is a reply to message #652202] Sat, 04 June 2016 01:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
Senior Member
WHEN eo.section_name = 'Sewing' AND em.grade ='7' THEN em.n_gross_hlp

Report message to a moderator

Re: Run with a single Code [message #652207 is a reply to message #652204] Sat, 04 June 2016 01:53 Go to previous messageGo to next message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Its working.
Again thank's. God bless you.

Report message to a moderator

Re: Run with a single Code [message #686346 is a reply to message #652207] Sun, 07 August 2022 04:27 Go to previous message
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
Member
Hello everyone,
Thank's for your previous help, Now I have a new query that I have to add new column of Basic. Basic calculation is for finishing 
em.n_gross_fin , round((em.n_gross_fin - 1850)/1.5,0)
Can I add this???

Report message to a moderator

Previous Topic: Slow performance when retriving data
Next Topic: Save File PDF to local directory in windows
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jan 22 07:56:04 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.