How to use GROUP_CONCAT in Stored procedure MySQL -


i'm trying use comma separated values (@job_skills) in query follows in stored procedure. not work expected. amy consider first value comma separated string. there other way patch ?

select group_concat(skillid) job_skills tbljob_skill jskl jskl.jobpostingid = param_jobid @job_skills;

    block6: begin         declare curl_job12 cursor select * (select js.jobseekerid tbljobseeker_skill jss inner join tbmstjobseeker js on (js.jobseekerid = jss.jobseekerid) inner join tblrecommended_jobseekers_details rjd on (js.jobseekerid=rjd.jobseekerid) jss.skillid in (@job_skills) , js.isactive = 'y' , js.lat<>0 , js.lang<>0 , rjd.sessionid=param_sessionid group js.jobseekerid) m;         declare continue handler not found set job_done12 = true;         open curl_job12;             read_loop_job12: loop                 fetch curl_job12 jsid, miles;                 if job_done12                     leave read_loop_job12;                 end if;                 insert temp_skills (jobid,skill,jobseekerid) values (param_sessionid,@job_skills,jsid);                 if jsid <> ''                     select count(1) check_exists tblrecommended_jobseekers_details sessionid = param_sessionid , type = 'match 1 - skills' , jobseekerid = jsid;                     if check_exists = 0                         start transaction;                         insert temp_jobseekers_scores (jobseekerid,score,type,miles) values (jsid,score_skills,'match 1',miles);                         insert tblrecommended_jobseekers_details (jobseekerid,score,type,miles,sessionid) values (jsid,score_skills,'match 1 - skills',miles,param_sessionid);                         commit;                     end if;                 end if;             end loop;         close curl_job12;     end block6;

generated comma separated values set , treated single string when used in function. instead have use find_in_set compare other values.

change where clause in cursor definition below.

declare curl_job12 cursor          select * (             select js.jobseekerid                tbljobseeker_skill jss               inner join tbmstjobseeker js                       on (js.jobseekerid = jss.jobseekerid)               inner join tblrecommended_jobseekers_details rjd                       on (js.jobseekerid=rjd.jobseekerid)               --  jss.skillid in (@job_skills)               find_in_set( jss.skillid, @job_skills )                 , js.isactive = 'y'                 , js.lat<>0                 , js.lang<>0                 , rjd.sessionid=param_sessionid               group js.jobseekerid         ) m;

refer documentation:

  • find_in_set(str,strlist)
    • returns value in range of 1 n if string str in string list strlist consisting of n substrings

Comments

Post a Comment

Popular posts from this blog

javascript - Using jquery append to add option values into a select element not working -

i'm looking dynamically populate select element names of items stored array. for reason it's not working , i'm not sure why. html: <div class="col-md-4 col-md-offset-4"> <select class="select1"> </select> , <select class="select2"> </select> </div> note i'm using bootstrap layout. here's jquery i'm using try , populate ".select1" jquery: select: function() { $.each(state.greens, function(index, value) { $(".select1").append("<option value =' " + index + " '>" + state.greens[index].name + "</option>"); }); } note function 'select' stored within object called 'display'. state.greens name of array i'm trying access. so @ end of html page call display.select(); call function. no error messages showing in console. also, saw question: appending
Read more

Android soft keyboard reverts to default keyboard on orientation change -

i'm building android soft keyboard , can't seem fix bug - have arabic , qwerty keyboard , when rotate device on qwerty keyboard (or arabic shift), it's if program has "restarted" , becomes arabic keyboard without shift. the onsaveinstancestate(bundle savedinstancestate) not work because application not extend activity inputmethodservice . i put following in android manifest android:configchanges="keyboard|keyboardhidden|orientation" android:windowsoftinputmode="stateunchanged|adjustresize"> i tried using @override public void onconfigurationchanged(configuration newconfig) { super.onconfigurationchanged(newconfig); log.i(mydebug, "config changed " + currentkeyboard.equals(qwerty)); } however, currentkeyboard.equals(qwerty)) results false , made sure true before orientation change. any appreciated. i think applications fault. if application restarting on orientation change
Read more

Rendering JButton to get the JCheckBox behavior in a JTable by using images does not update my table -

i rendering jbutton image on simulate jcheckbox behavior. why don't let jtable render jcheckbox? because need bigger checkboxes , there's no way can resize them (as far know). so, have own table cell renderer , editor. both renders jbutton , checkbox images on them. trying change image checked image unchecked image (or vice versa) , update cell value (boolean true or false). however, reason, getcelleditorvalue() not update table. so, release mouse, value goes original one. any appreciated! lot! public class tableexample extends jframe { jscrollpane pane; jpanel panel; jtable table; object[][] data; mytablemodel tm; renderer buttoncolumn; public tableexample(object[][] data) throws ioexception { string[] columns = {"column#1", "column#2", "column#3", "column#4", "column#5", "column#6"}; this.data = data; this.setpreferredsize(new dimension(700, 500));
Read more