Aggregate Case Conditions

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.
Regards,
RajeevPlease 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));
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.|||Rajeev,
You forgot to do a GROUP BY for aggregration.
This should work for you.
CREATE TABLE AAA
( FileID int
, TypeCD varchar(10)
)
GO
INSERT INTO AAA VALUES(100,'Home')
INSERT INTO AAA VALUES(100,'Host')
INSERT INTO AAA VALUES(105,'Home')
INSERT INTO AAA VALUES(106,'Host')
INSERT INTO AAA VALUES(106,'Payroll')
INSERT INTO AAA VALUES(107,'Home')
INSERT INTO AAA VALUES(107,'Host')
INSERT INTO AAA VALUES(107,'Payroll')
CREATE TABLE BBB
( FileID int
, ItemFlag int
)
GO
INSERT INTO BBB
SELECT
AAA.FileID
, sum( CASE AAA.TypeCd
WHEN 'Home' THEN 1
WHEN 'Host' THEN 2
WHEN 'Payroll' THEN 4
END
)
FROM AAA (nolock)
GROUP BY AAA.FileID
SELECT *
FROM BBB
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message news:1150388715.269695.235590@.h76g2000c
wa.googlegroups.com...
>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.
>
> Regards,
> Rajeev
>|||Joe,
I agree, the resultset for 'BBB' would be more robust as a VIEW.
However, wouldn't the view be a 'bit' easier to maintain if it was a simple
aggregration rather than the detailed CASE statement you offered?
CREATE VIEW BBB
AS
SELECT
AAA.FileID
, sum( CASE AAA.TypeCd
WHEN 'Home' THEN 1
WHEN 'Host' THEN 2
WHEN 'Payroll' THEN 4
END
) AS 'ItemFlag'
FROM AAA (nolock)
GROUP BY AAA.FileID
And of course, it would be so-o-o-o much simpler if Rajeev were to be storin
g the enumerated values rather than 'Home', 'Post', etc. Another of his desi
gn mistakes -and I sincerely hope that the table isn't really named 'AAA'...
Regards,
-Arnie Rowland
--
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"--CELKO--" <jcelko212@.earthlink.net> wrote in message news:1150394057.944343.243490@.y41g20
00cwy.googlegroups.com...
> 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));
>
> 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.
>|||Arnie Rowland wrote:
> Joe,
I like your answer better!
Yes! and me, too :)