How t o make the two select statement into one

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 2005 Forums

Transact-SQL (2005)

How t o make the two select statement into one

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-06-02 : 07:55:16

I had tried like this when the table is empty

SELECT
CASE
WHEN COUNT(ID) = 0
THEN '00' + CAST( 1 AS VARCHAR)
WHEN LEN(ID + 1) = 1
THEN '00' + CAST(ID + 1 AS VARCHAR)
WHEN LEN(ID + 1) = 2
THEN '0' + CAST(ID + 1 AS VARCHAR)
WHEN LEN(ID + 1) >= 3
THEN CAST(ID + 1 AS VARCHAR)
END
FROM Testing
GROUP BY ID

and didn't give any result after I truncated the table.

But if the table has record it gives 002.

Why this happens. Please explain.

So I separated the query like this.

Is there is any other way to makes this two query into a single.

DECLARE @DateId AS VARCHAR(50)

SELECT @DateId =
CASE
WHEN COUNT(ID) = 0
THEN '00' + CAST( 1 AS VARCHAR)
END
FROM Testing

SELECT @DateId =
CASE
WHEN LEN(ID + 1) = 1
THEN '00' + CAST(ID + 1 AS VARCHAR)
WHEN LEN(ID + 1) = 2
THEN '0' + CAST(ID + 1 AS VARCHAR)
WHEN LEN(ID + 1) >= 3
THEN CAST(ID + 1 AS VARCHAR)
END
FROM Testing

Thanks,
Babu Kumarasamy.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-02 : 08:00:20

Use union or union all as per your requirement.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 08:33:44

UNION?

Try this

SELECT		ID,
		REPLACE(STR(COUNT(*), 3), ' ', '0')
FROM		Testing
GROUP BY	ID

E 12°55'05.63"
N 56°04'39.26"

baburk
Posting Yak Master

108 Posts

Posted - 2009-06-02 : 08:45:20

quote:
Originally posted by Peso

UNION?

Try this
SELECT ID,
REPLACE(STR(COUNT(*), 3), ' ', '0')
FROM Testing
GROUP BY ID

E 12°55'05.63"
N 56°04'39.26"

This didn't help when I truncated the table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 08:49:27

[code]DECLARE @Sample TABLE
(
ID INT
)

SELECT REPLACE(STR(COUNT(*), 3), ' ', '0')
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-02 : 09:02:26

Try this


SELECT		
		REPLACE(STR(COUNT(*)+1, 3), ' ', '0')
FROM		Testing

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources