2

I have been trying to use Coalesce function in SQL Server for the qualifications corresponding to employees(i.e. One qualification entry corresponding one emp code and Qual. Code combination) there might be multiple entries corresponding to one emp code.

I have been trying 

UPDATE @emp_mis
SET qualification = COALESCE(@Names + ', ', ' ') + dtl.u_qualification_description,
    specialization = dbo.cmn40_quickcode_desc(@hrou, 'GEN', 'SPLZ', quvw.specialization_code, 1)
FROM hrcmn_epin_qualfn_vw qual (NOLOCK),
     hrgm_qual_lang_dtl dtl (NOLOCK),
     @emp_mis mis,
     hrcmn_qualification_vw quvw (NOLOCK)
--epin_primary_achievement pach (NOLOCK)
WHERE qual.employee_code = mis.emp_code
    AND dtl.qualification_code = qual.achievement_code
    AND dtl.language_code = 1
    AND qual.achievement_code = quvw.qualification_code

but this query is only displaying last qualification.

But I want in for of Q1,Q2,Q3,Q4 corresponding to employee code.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ratnesh Sharma
  • 21
  • 1
  • Can you add database schema to your question? – fabulaspb Jan 04 '16 at 10:01
  • Off topic but... the JOIN syntax you are using is no longer recommended. This [article](https://technet.microsoft.com/en-us/library/ms191472(v=sql.105).aspx) on TechNet is a good guide to using INNER and OUTER joins instead. You should also consider removing the (NOLOCK)s. This particular [Table Hint](https://msdn.microsoft.com/en-GB/library/ms187373.aspx) can return records that no longer exist. According to Microsoft, the ability to use NOLOCK in update and delete queries will be removed in a future version of SQL Server. – David Rushton Jan 04 '16 at 10:09
  • Considering in "hrcmn_epin_qualfn" table we have Emplyee to qualification code mapping and "hrgm_qual_lang_dtl" qualification description is avaialble..... on the basis of that we have to do the concatenation of Rows avaialable. – Ratnesh Sharma Jan 04 '16 at 10:29
  • 1
    i think you need to use stuff and xmlpath functions to get all column values – bmsqldev Jan 04 '16 at 10:35
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged – marc_s Jan 04 '16 at 19:26
  • The method of string concatenation you are using is not guaranteed to work. As you have now discovered. – Martin Smith Jan 04 '16 at 19:34

0 Answers0