Click here to Skip to main content
15,902,938 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
I have a table employees with some columns.
I want to set the default value of Commission column to 1000 for those records whose Job value is Manager. Or in other words , if the Job value is ' Manager' then set the default value of Commission to '1000'. How can i do this using sql query?
Posted
Comments
Anisuzzaman Sumon 18-Dec-15 12:52pm    
what have you tried till now?

1 solution

Hi.

If exists a table with this description
SQL
CREATE TABLE TBL_EMPLOYEES(
	EMPLOYEE_ID INT NOT NULL IDENTITY
	, NAME VARCHAR(300)
	, JOB VARCHAR(50)
	, COMISSION FLOAT
	, CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID)
)


And
C#
when you run the data selection is obtained


SQL
SELECT * FROM TBL_EMPLOYEES

EMPLOYEE_ID	NAME	JOB	       COMISSION
1	        JOHN	MANAGER	   NULL
2	        CLARK	SALES	   NULL


The query for data with the conditions set forth
SQL
SELECT 
EMPLOYEE_ID
, NAME
, JOB
, CASE 
	WHEN JOB = 'MANAGER' THEN 1000
	ELSE COMISSION	
	END COMISSION
FROM TBL_EMPLOYEES


And this is the result:
EMPLOYEE_ID	NAME	JOB	       COMISSION
1	        JOHN	MANAGER	   1000
2	        CLARK	SALES	   NULL


Regards...
 
Share this answer
 
Comments
aarif moh shaikh 19-Dec-15 1:59am    
Good one +5.

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