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
Now there is second table BBB which contains fileid(primary key) and
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
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.
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
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)
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
WHEN MIN(type_cd) = 'payroll' AND MAX(type_cd) = 'payroll'
WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'host' THEN 4
WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'payroll'
WHEN MIN(type_cd) = 'host' AND MAX(type_cd) = 'payroll'
ELSE NULL END
) AS item_flag
GROUP BY file_id;
The VIEW will always be current and does not require constant updating.