Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 case function with row count

Author  Topic 

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-09-04 : 06:22:27

hi
can any one help with
case function with row count

if row count is greater then 1 or >1 case1
if row count is zero then case two

------this is my table and cloumn ---sample code

DECLARE
@rc tinyint ,
@var varchar(50)
set @var = (SELECT emd.tpa_id
from dbo.EMPLOYER_DIVISION AS ED,EMPLOYEE_MISC_DATA AS EMD
where emd.tpa_id=ed.tpa_id
and
emd.empe_key=ed.division_key)
SELECT ed.tpa_id,
ed.division_key,
emd.tpa_id,
emd.empe_key
from dbo.EMPLOYER_DIVISION AS ED,EMPLOYEE_MISC_DATA AS EMD


database

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 06:26:30
quote:
Originally posted by oracle_corrgi

hi
can any one help with
case function with row count

if row count is greater then 1 or >1 case1
if row count is zero then case two

------this is my table and cloumn ---sample code

DECLARE
@rc tinyint ,
@var varchar(50)
set @var = (SELECT emd.tpa_id
from dbo.EMPLOYER_DIVISION AS ED,EMPLOYEE_MISC_DATA AS EMD
where emd.tpa_id=ed.tpa_id
and
emd.empe_key=ed.division_key)
SELECT ed.tpa_id,
ed.division_key,
emd.tpa_id,
emd.empe_key
from dbo.EMPLOYER_DIVISION AS ED,EMPLOYEE_MISC_DATA AS EMD
database

IF EXISTS (SELECT * FROM dbo.EMPLOYER_DIVISION AS ED inner join EMPLOYEE_MISC_DATA AS EMD on emd.tpa_id = ed.tpa_id and emd.empe_key = ed.division_key)
SELECT emd.tpa_id
from dbo.EMPLOYER_DIVISION AS ED
inner join EMPLOYEE_MISC_DATA AS EMD on emd.tpa_id = ed.tpa_id and emd.empe_key = ed.division_key
ELSE
SELECT ed.tpa_id,
ed.division_key,
emd.tpa_id,
emd.empe_key
from dbo.EMPLOYER_DIVISION AS ED
cross join EMPLOYEE_MISC_DATA AS EMD


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -