September 23, 2004 at 1:30 am
Hi,
I am having a typical senario where I have one master table
like employee and other one is error log
create table emp(
empid int IDENTITY (1, 1),
empname varchar(100),
Address varchar(200),
city varchar(100),
state varchar(100),
country varchar(50),
)
create table emp_error_log(
id int IDENTITY (1, 1),
empid int,
fieldname varchar(100),
errormessage varchar(200)
)
select * from emp_error_log
insert into emp (empname,Address,city,state,country) values('x','madapur','hyd','ap','india')
insert into emp (empname,Address,city,state,country) values('y','madapur','hyd','ap','india')
insert into emp (empname,Address,city,state,country) values('z','madapur','hyd','ap','india')
insert into emp (empname,Address,city,state,country) values('m','madapur','hyd','ap','india')
insert into emp (empname,Address,city,state,country) values('n','madapur','hyd','ap','india')
insert into emp_error_log(empid,fieldname,errormessage) values(1,'Address','Not able to insert full text')
insert into emp_error_log(empid,fieldname,errormessage) values(1,'city','Not able to insert full text')
insert into emp_error_log(empid,fieldname,errormessage) values(1,'state','Not able to insert full text')
insert into emp_error_log(empid,fieldname,errormessage) values(1,'country','Not able to insert full text')
insert into emp_error_log(empid,fieldname,errormessage) values(2,'Address','Not able to insert full text')
insert into emp_error_log(empid,fieldname,errormessage) values(2,'state','Not able to insert full text')
insert into emp_error_log(empid,fieldname,errormessage) values(2,'country','Not able to insert full text')
Now the requirement is i have to join both tables together and when data is returning it should result set like
employee table fields along with the errormessage.
if I selected empid=1 the it should be like
empid,empname,Address, errormessage , city , errormessage ,state, errormessage ,country, errormessage
1 , x , madapur, Not able to insert full text ,hyd,Not able to insert full text,ap,Not able to insert full text,india,Not able to insert full text
please help me how to get this in database. The requirement is you should not use any cursors
September 23, 2004 at 2:03 am
SELECT empid ,
empname,
Address,
MAX(CASE(errlog.FieldName='Address' THEN errormessage ELSE '' END) AS CityError ,
city ,
MAX(CASE(errlog.FieldName='City' THEN errormessage ELSE '' END) AS CityError ,
state,
MAX(CASE(errlog.FieldName='State' THEN errormessage ELSE '' END) AS StateError ,
country,
MAX(CASE(errlog.FieldName='Country' THEN errormessage ELSE '' END) AS CountryError ,
FROM dbo.emp INNER JOIN dbo.emp_error_log AS errlog
ON emp.empid = errlog.empid
GROUP BY empId,empname,Address,City,State,Country
September 23, 2004 at 2:47 am
Thanks david .
I still getting null values i haved tuned a bit witht he syntax.
Can you check what's wrong
SELECT errlog.empid,emp.empname,
Address, MAX( CASE errlog.FieldName when Address THEN errormessage ELSE '' END) AS AddressError,
city , MAX(CASE errlog.FieldName when city THEN errormessage ELSE '' END) AS CityError ,
state, MAX(CASE errlog.FieldName when state THEN errormessage ELSE '' END) AS StateError ,
country, MAX(CASE errlog.FieldName when country THEN errormessage ELSE '' END) AS CountryError
FROM dbo.emp INNER JOIN dbo.emp_error_log AS errlog
ON emp.empid = errlog.empid
GROUP BY errlog.empId,empname,Address,city,State,Country
September 23, 2004 at 4:57 am
From the data you supplied you would get an empty value for Employee 2 city error because there is no city entry.
September 24, 2004 at 8:53 am
1. It would be an easier problem if the emp_error_log had only one record per employee,
with a separate error field for each data column. So, here's how to create such a
#temp table or sub-query from your error log:
SELECT empid,
MAX(CASE WHEN fieldname='Address' THEN errormessage ELSE '') AS addr_error,
MAX(CASE WHEN fieldname='City' THEN errormessage ELSE '') AS city_error,
MAX(CASE WHEN fieldname='State' THEN errormessage ELSE '') AS state_error,
MAX(CASE WHEN fieldname='Country' THEN errormessage ELSE '') AS country_error
FROM emp_error_log
GROUP BY empid
2. The above query produces a single row for each employee with any error.
3. Then,to get a listing of all employees with errors:
SELECT emp.empid, emp.empname,
emp.Address, err.addr_error,
emp.city, err.city_error,
emp.state, err.state_error,
emp.country, err.country_error
FROM emp,
( SELECT empid,
MAX(CASE WHEN fieldname='Address' THEN errormessage ELSE '') AS addr_error,
MAX(CASE WHEN fieldname='City' THEN errormessage ELSE '') AS city_error,
MAX(CASE WHEN fieldname='State' THEN errormessage ELSE '') AS state_error,
MAX(CASE WHEN fieldname='Country' THEN errormessage ELSE '') AS country_error
FROM emp_error_log
GROUP BY empid
) AS err
WHERE emp.empid = err.empid
Good luck.
Bob Monahon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply