Click here to Skip to main content
15,943,729 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I have a secnario where I need to auto generate a value of a field if in case if it is null.

Ex :TABLE 1. employeeDetails

SQL
empName empId  empExtension
   A      101   null
   B      102   987
   C      103   986
   D      104   null
   E      105   null


TABLE 2. employeeDepartment

SQL
deptName  empId
     HR      101
     ADMIN   102
     IT      103
     IT      104
     IT      105

QUERY

SQL
SELECT empdt.empId, empdprt.deptName, empdt.empExtension
         FROM employeeDetails empdt
       LEFT JOIN employeeDepartment empdprt
         ON empdt.empId = empdprt.empId

RESULT :

SQL
empId deptName empExtension
   101    HR          null
   102    ADMIN       987
   103    IT          986
   104    IT          null
   105    IT          null


Now my question is I want to insert some dummy value which replaces null and auto-increments starting from a 5 digit INT number

EXPECTED OUTPUT:

SQL
empId     deptName    empExtension
      101    HR          12345
      102    ADMIN       987
      103    IT          986
      104    IT          12346
      105    IT          12347

Constraints : I cannot change existing tables structure or any field datatype.
Posted

1 solution

Common Table Expressions (CTE) will always save the day

SQL
with aCTE as (
	SELECT 
		empdt.empId, 
		empdprt.deptName, 
		empdt.empExtension,
		row_number() over(partition by case when empdt.empExtension is null then 1 else 0 end order by empdt.empId) as num -- generate sequential numbers
	FROM 
		employeeDetails empdt
		LEFT JOIN employeeDepartment empdprt ON empdt.empId = empdprt.empId
		)
select 
	a.empId,
	a.deptName,
	isnull(a.empExtension,num) --replaces null empExtension with the num
from aCTE a
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900