Using ROWCOUNT - 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

SQL Server Development (2000)

Using ROWCOUNT

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2009-02-02 : 22:54:01

[code]
INSERT INTO tbl_emp_load
(
emp_id,
category_type_id
)
SELECT
emp_id,
CASE
WHEN time_zone_id IN(0,1) THEN @1_E_type_id
WHEN time_zone_id IN(2) THEN @1_C_type_id
WHEN time_zone_id IN(3) THEN @1_M_type_id
WHEN time_zone_id IN(4,5,6) THEN @1_P_type_id
WHEN time_zone_id IN(7,8,9,10) THEN @1_O_type_id
ELSE
@Z_type_id
END AS category_type_id
FROM
tbl_state_load

WHERE
state = 'CA'

In the above mentioned query the category_type_id is assigned based on time_zone_id value but the
problem is :
@1_E_type_id with state ='CA' can have 100 records
@1_C_type_id with state ='CA' can have 25 records
@1_M_type_id with state ='CA' can have 250 records
@1_O_type_id with state ='CA' can have 500 records

But the requirement is to pass restricted threshold for each type_id.For eg:

Only need 30 @1_E_type_id records
Only need 90 @1_C_type_id records
Only need 120 @1_M_type_id records
Only need 40 @1_O_type_id records

How can use the SET ROWCOUNT option with this case statement so that I can restrict the no of records[/code]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-03 : 04:13:02

Hm, there is probably a better way but this is the first that comes to mind:

SELECT TOP 100 emp_id, @1_E_type_id AS category_type_id
FROM tbl_state_load 
WHERE state = 'CA' AND time_zone_id IN(0,1)

UNION ALL

SELECT TOP 25 emp_id, @1_C_type_id AS category_type_id
FROM tbl_state_load 
WHERE state = 'CA' AND time_zone_id = 2

UNION ALL

...

- Lumbago

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2009-02-03 : 10:51:06

I can't use TOP because my threshold values keeps on changing.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-03 : 11:19:03

Umh...what are you basing the rowcount of then?

quote:
Only need 30 @1_E_type_id records
Only need 90 @1_C_type_id records
Only need 120 @1_M_type_id records
Only need 40 @1_O_type_id records

This looks fixed to me...?

- Lumbago

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2009-02-03 : 11:36:15

Sorry to mention that the values for the threshold will be passed through as parameters in Stored procedure

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-03 : 11:50:44

This actually works, just remember to put () around the @top in the selects:

DECLARE @top int
SET @top = 20

SELECT TOP (@top) * FROM table

- Lumbago

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2009-02-03 : 12:01:54

This doesn't work 2000: It gives an error :
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '('.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-03 : 12:08:15

Ooops...2000, my bad. Sorry...

Will have to think a bit more on that one then...

- Lumbago

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2009-02-03 : 12:53:30

Any other ideas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources