Error Message Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Error Message Help

Author

Topic

Boi
Starting Member

1 Post

Posted - 2012-04-16 : 19:11:12

Can anyone help me in solving the following error message:

Msg 245, Level 16, State 1, Line 16
Conversion failed when converting the varchar value 'Accounting clerk' to data type int.

Here is the database:

------------------------------------------------*
-- Create the KudlerCC database
------------------------------------------------*
-- DROP DATABASE KudlerFF
-- GO

CREATE DATABASE KudlerCC
GO

------------------------------------------------*
-- Now switch to the Kudler database
------------------------------------------------*
USE KudlerCC
GO

------------------------------------------------*
-- Let's Create the Job Title Table First
-- Job ID will be the key.
------------------------------------------------*
CREATE TABLE Job_title
(
Job_ID int NOT NULL PRIMARY KEY,
EEO_1_classification varchar(50) NOT NULL,
Job_title varchar(60) NOT NULL,
Job_description varchar(255) NOT NULL,
Exempt_status bit NOT NULL
)
GO

------------------------------------------------*
-- Insert the Rows into the Job Title Table
------------------------------------------------*
INSERT into Job_title
(Job_ID, EEO_1_classification, Job_title, Job_description, Exempt_status)
VALUES
(1, 'Office/Clerical', 'Accounting clerk', 'Computes, classifies, records, and verifies numerical data for use in maintaining accounting records.', 0),
(2, 'Officials & Managers', 'Asst. Manager', 'Supervises and coordinates activities of workers in department of food store. Assists store manager in daily operations of store.', 1),
(3, 'Sales Workers', 'Bagger', 'Places customer orders in bags. Performs carryout duties for customers.', 0),
(4, 'Sales Workers', 'Cashier', 'Operates cash register to itemize and total customer’s purchases in grocery store.', 0),
(5, 'Technician', 'Computer Support Specialist', 'Installs, modifies, and makes minor repairs to personal computer hardware and software systems, and provides technical assistance and training to system users.', 0),
(6, 'Officials & Managers', 'Director of Finance & Accounting', 'Plans and directs the finance and accounting activities for Kudler Fine Foods.', 1),
(7, 'Craft Workers (Skilled)', 'Retail Asst. Bakery & Pastry', 'Obtains or prepares food items requested by customers in retail food store.', 0),
(8, 'Operatives (Semi skilled)', 'Retail Asst. Butchers and Seafood Specialists', 'Obtains or prepares food items requested by customers in retail food store.', 0),
(9, 'Office/Clerical', 'Stocker', 'Stores, prices and restocks merchandise displays in store.', 0)
GO

------------------------------------------------*
-- Select the Rows from the Job Title Table
------------------------------------------------*
select * from job_title
GO

------------------------------------------------*
-- Now Let's Create the Employee Table.
-- Emp_ID will be the primary key and IDENTITY column.
-- Job_ID is the foreign key used to join the
-- Employee table to the Job_title table.
------------------------------------------------*
CREATE TABLE Employee
(
Emp_ID int IDENTITY NOT NULL PRIMARY KEY,
Last_name varchar(30) NOT NULL,
First_name varchar(30) NOT NULL,
Emp_address varchar(80) NOT NULL,
Emp_city varchar(25) NOT NULL,
State_code char(2) NOT NULL,
Telephone_area_code char(3) NOT NULL,
Telephone_number varchar(7) NOT NULL,
EEO_1_classification varchar(50) NOT NULL,
Hire_date smalldatetime NULL,
Emp_salary money NOT NULL,
Emp_gender char(1) NOT NULL,
Emp_age int NOT NULL,
Job_ID int NOT NULL FOREIGN KEY REFERENCES Job_title(Job_ID)
)
GO

------------------------------------------------*
-- Insert the Rows into the Employee Table
------------------------------------------------*
INSERT into Employee
(Last_name, First_name, Emp_address, Emp_city, State_code, Telephone_area_code, Telephone_number, EEO_1_classification, Hire_date, Emp_salary, Emp_gender, Emp_age, Job_ID)
VALUES
('Edelman', 'Glenn', '175 Bishop Lane', 'La Jolla', 'CA', 619, 5550199, 'Sales Workers', '10/7/2003', 21500, 'M', 23, 4),
('McMullen', 'Eric', '763 Church Street', 'Lemon Grove', 'CA', 619, 5550133, 'Sales Workers', '11/01/2003', 13500, 'M', 25, 4),
('Slentz', 'Raj', '123 Torrey Drive', 'North Clairmont', 'CA', 619, 5550123, 'Officials & Managers', '06/01/2000', 48000, 'M', 35, 2),
('Broun', 'Erin', '2045 Parkway Apt. 2B', 'Encinitas', 'CA', 760, 5550100, 'Sales Workers', '03/01/2003', 10530, 'F', 29, 4),
('Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', 760, 5550154, 'Office/Clerical', '10/15/2003', 15000, 'M', 23, 9),
('Esquivez', 'David', '10983 N. Coast Hwy Apt. 902', 'Encinitas', 'CA', 760, 5550108, 'Operatives (Semi skilled)', '07/01/2003', 18500, 'M', 30, 8),
('Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', 858, 5550135, 'Sales Workers', '06/01/2003', 21000, 'F', 19, 4)
GO

------------------------------------------------*
-- Select the Rows from the Employee Table
-- Note that the Employee_ID column values show
------------------------------------------------*
select * from Employee
GO

/*
Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection.
Use salary to restrict data.
*/

SELECT e.First_name, e.Last_name, e.Emp_salary, j.Job_title
FROM employee e INNER JOIN
Job_title j ON e.Job_ID = j.Job_ID
WHERE (e.Emp_salary BETWEEN 10000 AND 55000)

/*
Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection.
Use hire dates to restrict data.
*/
SELECT e.First_name, e.Last_name, e.Hire_date, j.Job_title
FROM employee e INNER JOIN
Job_title j ON e.Job_ID = j.Job_title
WHERE (e.Hire_date BETWEEN '1/1/1999' AND CURRENT_TIMESTAMP)

/*
Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection.
Use telephone area codes to restrict data.
*/
SELECT e.First_name, e.Last_name, e.Emp_gender, e.Hire_date, e.Emp_salary, e.Telephone_area_code, e.Telephone_number, j.Job_title
FROM employee e INNER JOIN
Job_title j ON e.Job_ID = j.Job_ID
WHERE (e.Telephone_area_code LIKE '%2%')

/*
Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection.
Use age to restrict data.
*/
SELECT e.Last_name, e.Emp_age, e.Emp_gender, e.Emp_city, j.Job_description
FROM employee e INNER JOIN
Job_title j ON e.Job_ID = j.Job_ID
WHERE (e.Emp_age LIKE '%3')

/*
Write a SQL query that uses the UNION of the two tables to produce a third table.
*/

SELECT 'Employee' AS [Type], e.First_name AS [Name]
FROM employee e
UNION
SELECT 'Job', j.Job_title
FROM Job_title j

/*
Select employees’ last names and group them by EEO-1 classification.
*/
SELECT COUNT(e.Last_name) AS count_of_last_name, j.EEO_1_classification
FROM employee e INNER JOIN
Job_title j ON e.Job_ID = j.Job_ID
GROUP BY j.EEO_1_classification

/*
Select employees’ last names and group them by salary.
*/SELECT COUNT(Last_name) AS count_of_last_name, Emp_salary
FROM employee e
GROUP BY e.Emp_salary

/*
Select employees’ last names and group them by salary in their EEO-1 classification.
*/
SELECT COUNT(e.Last_name) AS count_of_last_name, e.Emp_salary, j.EEO_1_classification
FROM employee e INNER JOIN
Job_title j ON e.Job_ID = j.Job_ID
GROUP BY e.Emp_salary, j.EEO_1_classification

/*
Select employees’ last names and group them by salary in job titles, grouped into exempt and non-exempt.
*/
SELECT COUNT(e.Last_name) AS count_of_last_name, e.Emp_salary, j.Job_title, j.Exempt_status
FROM employee e INNER JOIN
Job_title j ON e.Job_ID = j.Job_ID
GROUP BY e.Emp_salary, j.Job_title, j.Exempt_status

/*Increase all employees’ salaries with the selected EEO-1 classification by 10 percent*/
Update Employee
SET Emp_salary = Emp_salary + (Emp_salary / 10)
WHERE Job_ID IN (SELECT Job_ID FROM Job_title WHERE EEO_1_classification = 5)

/*Increase all employees’ salaries by 5 percent*/
Update Employee
SET Emp_salary = Emp_salary + (Emp_salary / 20)

/*Choose an employee from the Employee table and delete that employe*/
Delete from Employee Where Emp_ID = 4

/*03_Employee_Average*/
Select avg(Emp_salary) from Employee

/*04_Exempt_Employee_Max_Salary*/
SELECT MAX(Emp_salary) FROM Employee
LEFT JOIN Job_title ON Employee.Job_ID = Job_title.Job_ID
WHERE Job_title.Exempt_status = 'Exempt'

/*05_Non-Exempt_Employee_Max_Salary*/
SELECT MAX(Emp_salary) FROM Employee
LEFT JOIN Job_title ON Employee.Job_ID = Job_title.Job_ID
WHERE Job_title.Exempt_status = 'Non-Exempt'

/*06_All_Employee_Max_Salary*/
SELECT MAX(Emp_salary) FROM Employee

/*07_Exempt_Employee_Min_Salary*/
SELECT Min(Emp_salary) FROM Employee
LEFT JOIN Job_title ON Employee.Job_ID = Job_title. Job_ID
WHERE Job_title.Exempt_status = 'Exempt'

/*08_Non-Exempt_Employee_Min_Salary*/
SELECT Min(Emp_salary) FROM Employee
LEFT JOIN Job_title ON Employee.Job_ID = Job_title. Job_ID
WHERE Job_title.Exempt_status = 'Non-Exempt'

/*09_All_Employee_Min_Salary*/
SELECT Min(Emp_salary) FROM Employee

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-17 : 02:20:38

Look for this line:
Job_title j ON e.Job_ID = j.Job_title
You can't join Id with Title

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-17 : 02:21:58

Hint: clicking double on the error message in the result pane will hilight the row.

No, you're never too old to Yak'n'Roll if you're too young to die.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-17 : 08:41:18

These are the queries containing the errors...followed by error messages and meaning of the errors. The rest you have to do.


/*
Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. 
Use hire dates to restrict data.
*/
SELECT e.First_name, e.Last_name, e.Hire_date, j.Job_title
FROM employee e INNER JOIN
Job_title j ON e.Job_ID = j.Job_title
WHERE (e.Hire_date BETWEEN '1/1/1999' AND CURRENT_TIMESTAMP)

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the varchar value 'Accounting clerk' to data type int.

--e.Job_ID is int and j.Job_title is varchar...comparing fields with different datatypes is giving error


/*Increase all employees’ salaries with the selected EEO-1 classification by 10 percent*/
Update	Employee
SET	Emp_salary = Emp_salary + (Emp_salary / 10)
WHERE Job_ID IN (SELECT Job_ID FROM Job_title WHERE EEO_1_classification = 5)

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the varchar value 'Office/Clerical' to data type int.

--EEO_1_classification is varchar...chacking against an int is giving an error


/*04_Exempt_Employee_Max_Salary*/
SELECT MAX(Emp_salary) FROM Employee
LEFT JOIN Job_title ON Employee.Job_ID = Job_title.Job_ID
WHERE	Job_title.Exempt_status = 'Exempt'

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the varchar value 'Exempt' to data type bit.

--Job_title.Exempt_status is bit....checking against varchar is giving an error


/*05_Non-Exempt_Employee_Max_Salary*/
SELECT MAX(Emp_salary) FROM Employee
LEFT JOIN Job_title ON Employee.Job_ID = Job_title.Job_ID
WHERE	Job_title.Exempt_status = 'Non-Exempt'

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the varchar value 'Non-Exempt' to data type bit.

--Job_title.Exempt_status is bit....checking against varchar is giving an error


/*07_Exempt_Employee_Min_Salary*/
SELECT Min(Emp_salary) FROM Employee
LEFT JOIN Job_title ON Employee.Job_ID = Job_title. Job_ID
WHERE	Job_title.Exempt_status = 'Exempt'

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the varchar value 'Exempt' to data type bit.

--Job_title.Exempt_status is bit....checking against varchar is giving an error


/*08_Non-Exempt_Employee_Min_Salary*/
SELECT Min(Emp_salary) FROM Employee
LEFT JOIN Job_title ON Employee.Job_ID = Job_title. Job_ID
WHERE	Job_title.Exempt_status = 'Non-Exempt'

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the varchar value 'Non-Exempt' to data type bit.

--Job_title.Exempt_status is bit....checking against varchar is giving an error

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources