Selecting 'x of y' in one query
-
I'm attempting to change a scalar function we have here to a table-valued function and altering what appears to be some simple code is giving me some problems.
I've broken it down to the simplest form that I can and the code is all attached:
CREATE TABLE TestResult(Totalvarchar(200)
);
CREATE TABLE TestData(
Dataint
);
INSERT INTO TestData(Data)
SELECT 1
union all
SELECT 2
union all
SELECT 3
union all
SELECT 4;
INSERT INTO TestResult(Total)
SELECT CONVERT(VarChar,Count(*) + 1)
+ ' of ' +
CONVERT(VarChar,Count(*) + 1 )
FROM dbo.TestData;
select * from TestResult;
INSERT INTO TestResult(Total)
SELECT CONVERT(VarChar,Count(*) + 1)
FROM dbo.TestData
WHERE 1=1
+ ' of ' +
(SELECT CONVERT(VarChar,Count(*) + 1)
FROM dbo.TestData
WHERE 1=1);
select * from TestResult;
--truncate table TestResult;
The effect here is to have two rows in TestResult, which will in this case both be '5 of 5'.
The first INSERT works, but the second one is a different format and I get 'Conversion failed when converting the varchar value ' of ' to data type int.'. In the second INSERT there are two WHERE clauses. In the real world (or what passes for it in these parts) the two clauses will be different and the result will be '1 of 3', '6 of 10' and suchlike. But this simplification here reproduces the error I'm trying to work around.
If someone could show me how to get around this I would appreciate it.
-
Your second insert needs to be a single SELECT
INSERT INTO TestResult(Total)SELECT (SELECT CONVERT(VarChar,Count(*) + 1)
FROM dbo.TestData
WHERE 1=1)
+ ' of ' +
(SELECT CONVERT(VarChar,Count(*) + 1)
FROM dbo.TestData
WHERE 1=1);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
By the looks of what you are doing the where clause (1=1) will actually be different when used in real code. if so try using CASE statement.
INSERT INTO TestResult(Total)select case when 1=1 then CONVERT(VarChar,Count(*) + 1) end
+ ' of ' +
case when 1=1 then CONVERT(VarChar,Count(*) + 1) end
FROM dbo.TestData
-
Oddly enough Celko, I agree with you.
It is maddening that the formatting is done within the SQL and if I was here when the system was designed I would have prevented such a thing, but I wasn't. It was written years before I appeared here and is one of the many things that I'd like to remedy when I get the opportunity. The amount of date formatting I've already removed within existing code is incredible; people seem to think that it has to be human-readable in order to be machine-readable.
First on my list is getting rid of the NOLOCKS. Win that battle and then move on to the next.
-
BrainDonor (11/9/2011)
INSERT INTO TestResult(Total)
SELECT CONVERT(VarChar,Count(*) + 1)
FROM dbo.TestData
WHERE 1=1
+ ' of ' +
(SELECT CONVERT(VarChar,Count(*) + 1)
FROM dbo.TestData
WHERE 1=1);
The posted code is missing a paren.
It's looking for records where
WHERE 1 = ( 1 + ' of ' + .... )It can't convert the ' of ' to an integer in order to add it to the 1.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply