Clarity

Greetings,

I am trying to create a process for updating custom Primary skill field from OOB skill values for a particular parent. As a user can have multiple skills i have to select 1 skill. I was able to get the process working when i select 1 resource but not able to update for all resources.

Below is the query when I select  1 ressource it works.

update

odf_ca_resource odf SET si_primary_skill =

  (select rsa.skill_id

  from  rsm_skills_associations rsa,rsm_skills rs

  where rs.parent_skill_id = '5001061'

  and  rsa.skill_id = rs.id

  and rsa.object_id = '5555003'

  and  ROWNUM <=1)

  WHERE odf.si_primary_skill IS NULL

  and odf.id = '5555003'

Below is query that I am trying to use update for all resources. There is one basic sql query statement i am missing but not able to point it.

update

  odf_ca_resource odf SET si_primary_skill =

  (select rsa.skill_id

  from  rsm_skills_associations rsa,rsm_skills rs

  where rs.parent_skill_id = '5001061'

  and  rsa.skill_id = rs.id

  and  ROWNUM <=1)

  WHERE odf.si_primary_skill IS NULL

  and odf.id = rsm_skills_associations.object_id

Thanks,

Ma

Hi

Are you getting any error message when you trying to run this Update Query? if so,may I know the error message.

I'm just thinking the query may give you error message - the last line of the query looks not convincing to me as you have used rsm_skills_association table in the join condition which may not be accessible out side of your sub query of the select statement.

Please try the below query - However  I STRONGLY RECOMMEND you to try this Query in your TEST / Pilot environment. As I don't have your environment to re-produce the query exactly - The Query I have developed purely based on my understanding/assumptions.

Update odf_ca_resource odf SET rego_primary_skill = rsa.skill_id

FROM odf_ca_resource odf

innerjoin rsm_skills_associations rsa on odf.id = rsa.object_id and odf.rego_primary_skill IS NULL

innerjoin rsm_skills rs on rsa.skill_id = rs.id and rs.parent_skill_id = '5001061' and  ROWNUM <=1

Hope this helps.

Best Regards

Maddineni

Thank you Maddineni. I will give it a try and let you know.

Try this

UPDATE          ODF_CA_RESOURCE OCR
SET             SI_PRIMARY_SKILL =      (
                                                                SELECT          RSA.SKILL_ID 
                                                                 FROM           RSM_SKILLS_ASSOCIATIONS RSA INNER JOIN
                                                                                RSM_SKILLS RS ON
                                                                                RSA.SKILL_ID = RS.ID
                                                                WHERE           RS.PARENT_SKILL_ID = 5001061 AND
                                                                                OCR.ID = RSA.OBJECT_ID AND
                                                                                ROWNUM <=1
                                                                )
WHERE           OCR.SI_PRIMARY_SKILL IS NULL AND
EXISTS (
                                                                SELECT          RSA.SKILL_ID 
                                                                 FROM           RSM_SKILLS_ASSOCIATIONS RSA INNER JOIN
                                                                                RSM_SKILLS RS ON
                                                                                RSA.SKILL_ID = RS.ID
                                                                WHERE           RS.PARENT_SKILL_ID = 5001061 AND
                                                                                OCR.ID = RSA.OBJECT_ID AND
                                                                                ROWNUM <=1
                                                                );

Thank you Andrew. The query worked.

Appreciate your help.

Hello Maddineni,

I tried the query and was giving error. Sorry time constrains not able to explore much.

Appreciate your help.

Thanks,

Manjusha

Sorry if I am being a killjoy but be really careful with direct SQL updates.

I might be wrong, but you should be able to read the skill and write this with xog back into the resource object.

Absolutely agree Andy. The only reason i going with this is updating a custom primary skill which is going to be much faster than preparing the xml file.

Thanks,

Manjusha