Aggregate Case Conditions

SQL Apprentice Question
I have a table AAA which contains two columns fileid and typecd

for single fileid there can be either 1, 2 or 3 typecds home, host and
payroll
for e.g.


Fileid Typecd
100 Home
100 host


105 home


106 host
106 payroll


107 home
107 host


107 payroll


Now there is second table BBB which contains fileid(primary key) and
itemflag


Now i have to set itemflag for a fileid based on the above table.. like
if fileid contains only home then itemflag should be 1
only host then itemflag should be 2
only payroll then itemflag should be 3
home and host then itemflag should be 4
home and payroll then itemflag should be 5
host and payroll then itemflag should be 6
home host and payroll then itemflag should be 7


I tried this but this is not working giving error.


Insert into BBB
Select AAA.fileid,
(select
case sum(case AAA.typeCd when 'Home' then 1
when 'Host' then 2
when 'Payroll' then 4 end)
when 1 then 1
when 2 then 2
when 3 then 4
when 4 then 3
when 5 then 5
when 6 then 6
when 7 then 7 end
) as ItemFlg,
FROM AAA (nolock)


This is not working because white fetching first row from AAA, the
typecd can contain any of the three value,it is
not having all the three values at a single time.


Can any one help me with this.


Celko Answers
Please post DDL, as you have been asked to do before.

CREATE TABLE AAA
(file_id INTEGER NOT NULL,
type_cd CHGAR(7) NOT NULL
CHECK (type_cd IN ('home', 'host', 'payroll')),
PRIMARY KEY (file_id, type_cd));



>> Now there is second table BBB which contains file_id(primary key) and


item_flag <<

This is a bad design. The "item_flag" is computed and should not be
presisted in a base table. Use a VIEW instead


CREATE VIEW BBB (file_id, item_flag)
AS
SELECT file_id,
CASE WHEN COUNT(*) = 3 THEN 7 -- all 3 type codes
WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'home' THEN 1
WHEN MIN(type_cd) = 'host' AND MAX(type_cd) = 'host' THEN
2
WHEN MIN(type_cd) = 'payroll' AND MAX(type_cd) = 'payroll'
THEN 3
WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'host' THEN 4
WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'payroll'
THEN 5
WHEN MIN(type_cd) = 'host' AND MAX(type_cd) = 'payroll'
THEN 6
ELSE NULL END
) AS item_flag
FROM AAA
GROUP BY file_id;


The VIEW will always be current and does not require constant updating.