0
create database assessment3;

use assessment3;

create table Employee ( eID int NOT NULL IDENTITY(1,1) PRIMARY KEY, eName varchar(25) NOT NULL, Mgr int NULL, Job text NOT NULL, Salary int NOT NULL, Comm int, hDate date NOT NULL, dID int NOT NULL, );

insert into Employee(eName, Mgr, Job, Salary, Comm, hDate, dID) values ('ken Adams', 1004, 'Salesman', 70000, 20000, '2008-04-12', 1), ('Ru Jones', 1004, 'Salesman', 65000, 15000, '2010-01-18', 1), ('Dhal Sim', 1006, 'Accountant', 88000, NULL, '2001-03-07', 2), ('Ellen Honda', 1006, 'Manager', 118000, NULL, '2001-03-17', 1), ('Mike Bal', 1006, 'Receptionist', 68000, NULL, '2006-06-21', 3), ('Martin Bison', NULL, 'CEO', 210000, NULL, '2010-07-12', 3), ('Shen Li', 1004, 'Salesman', 86000, 18000, '2014-09-18', 1), ('Zang Ross', 1004, 'Salesman', 65000, 10000, '2017-02-02', 1), ('Sagar Kahn', 1004, 'Salesman', 70000, 15000, '2016-03-01', 1);

select * from Employee;

select eID, eName, job from Employee where Job = 'Salesman' and hDate > '01-01-2014';

i have created the DB but when insert the database information my output show an error

Msg 402, Level 16, State 1, Line 30 The data types text and varchar are incompatible in the equal to operator.

(0 rows affected)

how can i resolve this and make the table show in the result. Plus i using SQL Management Studio 2017

Var Malkapuri
  • 9
  • 1
  • 4
  • ('Martin Bison', NULL, 'CEO', 210000, NULL, '2010-07-12', 3), second column for Employee(eName, Mgr, Job, Salary, Comm, hDate, dID) Mgr column which field is not NULL???? – Hasan Mahmood Mar 20 '19 at 14:03
  • 1
    Considering that this looks like hierarchical data, I would suggest that `Mgr` (Manager I assume) *can* be `NULL`; as someone at the top of the hierarchy does not have a manager. – Thom A Mar 20 '19 at 14:06
  • When you get an error message, you can almost always find a solution by googling the error message. – Tab Alleman Mar 20 '19 at 14:11

1 Answers1

0

Since MGR is not nullable column but you are inserting null value into column so it is failing

Try like this 

create table #Employee 
(
    eID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    eName varchar(25) NOT NULL,
    Mgr int NOT NULL,
    Job text NOT NULL, 
    Salary int NOT NULL,
    Comm int,
    hDate date NOT NULL, 
    dID int NOT NULL,
);

alter table #Employee
alter column Mgr int null
insert into #Employee(eName, Mgr,  Job, Salary, Comm, hDate, dID)
values ('ken Adams', 1004, 'Salesman', 70000, 20000, '2008-04-12', 1),
       ('Ru Jones', 1004, 'Salesman', 65000, 15000, '2010-01-18', 1),
       ('Dhal Sim', 1006, 'Accountant', 88000, NULL, '2001-03-07', 2),
       ('Ellen Honda', 1006, 'Manager', 118000, NULL, '2001-03-17', 1),
       ('Mike Bal', 1006, 'Receptionist', 68000, NULL, '2006-06-21', 3),
       ('Martin Bison', NULL, 'CEO', 210000, NULL, '2010-07-12', 3),
       ('Shen Li', 1004, 'Salesman', 86000, 18000, '2014-09-18', 1),
       ('Zang Ross', 1004, 'Salesman', 65000, 10000, '2017-02-02', 1),
       ('Sagar Kahn', 1004, 'Salesman', 70000, 15000, '2016-03-01', 1);
Chanukya
  • 5,833
  • 1
  • 22
  • 36