plsql - copy data after comparing a specific value -

-

i want make procedure in parameters. more specific, have tables folowing table1

|col1 | col2 | col3 | col4 |  col5 | col6|  ---------------------------------------- |600  |  140 |   2  |  10  |  1600 |  1  |  ---------------------------------------- |600  |  140 |   2  |  20  |  1200 |  4  |  ---------------------------------------- |600  |  140 |   2  |  15  |  1100 |  3  |  ---------------------------------------- |600  |  140 |   2  |  35  |  1700 |  2  |  ---------------------------------------- |600  |  140 |   3  |  10  |  1300 |  6  |  ---------------------------------------- |600  |  140 |   3  |  15  |  1100 |  5  |  ----------------------------------------

for same col1 , col2/col3 , check select different values col4 instance col1=600 , col2=140/col3=2 , col2=140/col3=3 return 20 , 35

and insert in table table1 rows 600 , 140 , 3, 20 , 1200 , 7 (seq number) 600 , 140 , 3, 35 , 1700 , 8 (seq number)

but don't know how can insert statement :( 

    procedure copy_columns  (    p_col1         in      a.col1%type,                                      p_from_col2    in      b.col2%type,                                      p_from_col3    in      b.col3%type,                                      p_to_col2      in      b.col2%type,                                      p_to_col3      in      b.col3%type,                                      p_flg1         in      varchar2,                                      p_flg2         in      varchar2,                                      p_flg3         in      varchar2,                                                                            p_flg4         in      varchar2,                                      p_flg5         in      varchar2                                 )       cursor c1 select *                                      col1 = p_col1;      cursor c2 select *                    b                   col1  = p_col1                     , col2  = p_col2                     , col3  = p_col3 ;      begin      if p_flg1='n' or p_flg2='n' or p_flg3='n' or  p_flg4 ='n' or p_flg5 = 'n'         goto label; --do nothing     end if;      if p_flg1 = 'y'         select col4         table1         col1 = p_col1 , col2 = p_from_col2 , col3 = p_from_col3         minus         select col4         table1         col1 = p_col1 , col2 = p_to_col2 , col3 = p_to_col3 -- how insert ?      end if;      if p_flg2 = 'y'         select col4         table2         col1 = p_col1 , col2 = p_from_col2 , col3 = p_from_col3         minus         select col4         table2         col1 = p_col1 , col2 = p_to_col2 , col3 = p_to_col3     end if;     -- ..........     <<label>>     end;

could me please ? thank you

if i'm not wrong, trying values 1 table , trying insert them table. then, way.

insert yourtable(col1,col2,coln)                select col1,col2,coln anothertable;

update

after reading recent comment, seems should use merge. not require use cursor in case. 

merge destination d    using (select col1, col2 source    [condition check]) s    on (d.id = s.id)    when not matched insert (d.col3, d.col4)      values (s.col3, s.col4);

Comments

Post a Comment

Popular posts from this blog

qt - Using float or double for own QML classes -

-
i've created components , helper methods using c++ , qt used in qml gui. of methods calculate gui elements (size, transformations, scene graph items creation). target arm 32 bit using opengl / eglfs render output. opengl using float , qml using real (defined double ). what should use in c++ do not lose precision? if use qreal ( double ) shouldn't lose precision. if git grep float in qtbase.git , can find out bit more: dist/changes-5.2.0-**************************************************************************** dist/changes-5.2.0-* important behavior changes * dist/changes-5.2.0-**************************************************************************** dist/changes-5.2.0- dist/changes-5.2.0- - qt compiled qreal typedef'ed double on dist/changes-5.2.0: platforms. qreal float on arm chipsets before. guarantees more dist/changes-5.2.0- consistent behavior between platforms qt supports, binary dist/chan...
Read more

Create Outlook appointment via C# .Net -

-
i need create outlook appointment using microsoft.office.interop.outlook , while can work locally on own workstation , works if run web application via server's browser, not work when connect server externally. hence, think might permission issue. i changed application pool identity " localsystem " don't access denied error. unfortunately, doesn't work. the app behaves if appointment created, appointment doesn't appear in outlook. here include file on top of aspx.cs page using outlook = microsoft.office.interop.outlook; here code using pop appointment. outlook.application apptapp = new outlook.application(); outlook.appointmentitem appt = apptapp.createitem(outlook.olitemtype.olappointmentitem) outlook.appointmentitem; appt.subject = txtfirstname.text + " " + txtlastname.text; appt.body = txtcomment.text; appt.alldayevent = false; appt.start = datetime.parse(txtreminderdate.text + " 8:00 am"); appt.end = datetime.p...
Read more

ios - Swift Array Resetting Itself -

-
i have 2 arrays: pointhistory , datehistory . have nstimer , , when timer runs out, action called. in action appending int pointhistory , , current date string datehistory . here timer action: func timeraction() { let steppervalue = int(stepper.value) employee.numberofpoints = steppervalue networking.saveemployee(employee, completion: { (error) -> void in if error != nil { let alert = uialertcontroller(title: "error", message: "there error updating \(self.employee.name)'s points", preferredstyle: .alert) let alertaction = uialertaction(title: "ok", style: .cancel, handler: nil) alert.addaction(alertaction) self.presentviewcontroller(alert, animated: true, completion: nil) } }) var dateformatter = nsdateformatter() dateformatter.dateformat = "mm-dd-yyyy" dateformatter.timezone = nstimezone.localtimezone() var date = nsdate() var ...
Read more