row count - SQL Server Forums

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)

row count

Author

Topic

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-09-04 : 03:06:06

hi

i think i have confused u all or unable to explain prorperly

see i want to write case function
if the output record is >1 can case 1
else case two

say i have more then one record output or zero
if it is zero case2
else case 1

sample code

-----SELECT ed.tpa_id,
ed.division_key,
emd.tpa_id,
emd.empe_key
from dbo.EMPLOYER_DIVISION AS ED,EMPLOYEE_MISC_DATA AS EMD

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)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-04 : 03:09:37

don't quite understand your requirement. Can you post some sample table & data ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-04 : 03:09:57

using Having Query

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 03:38:48

I think he means something like this

SELECT		ed.tpa_id,
		ed.division_key,
		emd.tpa_id,
		emd.empe_key,
		ISNULL(rctpa_id, ed.tpa_id)
from		dbo.EMPLOYER_DIVISION AS ED,
LEFT join	EMPLOYEE_MISC_DATA AS EMD ON emd.tpa_id = ed.tpa_id and emd.empe_key = ed.division_key

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 05:28:26

Oh, now you have to questions in the same topic. For the first question (the original)

SELECT		ed.tpa_id,
		ed.division_key,
		emd.tpa_id,
		emd.empe_key,
		case
			when @rc = 1 then RCONE
			when @rc > 2 then RCTWO
			ELSE NULL
		END
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
order by	5

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 05:31:56

Second question is harder to tell when we know nothing about @divstr looks

CREATE procedure GetDivisionKeyAssigEmployee
(
	@empid varchar(20),
	@tpid varchar(20),
	@divstr varchar(20),
	@rs tinyint OUTPUT
)
as

SET NOCOUNT ON

declare	@ekey char(2),
	@divs2 varchar(10)

select @ekey = empe_key
from	vw_employee
where	tpa_id = @tpid
	and empr_id = @empid

select	@divs2 = division_key
from	vw_employee_misc_data
where	tpa_id = @tpid
	and empe_key = @ekey

if @divs2 = substring(@divstr, 1, charindex(',', @divstr, 1) - 1)
	select @rs = 1
else
	if @divs2 = substring(@divstr, charindex(',', @divstr, 1) + 1, 2)
		select @rs = 1
	else
		if @divs2 = substring(@divstr, charindex(',', @divstr, charindex(',', @divstr, 1) + 1), 2)
			select @rs = 1
		else
			select @rs = 0

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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

Don't change the original posting. Now we have no idea what you are trying to achieve.
What is CASE 1 and what is CASE 2?
What is output record?

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-04 : 11:12:40

quote:
Originally posted by Peso

Don't change the original posting. Now we have no idea what you are trying to achieve.
What is CASE 1 and what is CASE 2?
What is output record?

Peter Larsson
Helsingborg, Sweden

Aha.. i guess its better to put quotes and then reply so it helpful to others for the future reference.. also..

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources