Home > Archive > SQL Server Programming > September 2005 > Help with subquery
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Help with subquery
|
|
|
| O.k. the following query works, unless there is more that one value a column.
It tells me, "Subquery returned mor than 1 value. This is not permitted
when the subquery follows =, or when the subquery is used as an expression".
How can I get this query to format each item in a column?
declare @Integer money
select @Integer = (select amount from table_name)
select left( replicate("0", 15-len('amount')) +
convert(nvarchar,@Integer*100),15)
The query pads the value in the column with leading zeros until the value is
equal to a length of 15. I need it to change every row in the column.
| |
| Jerry Spivey 2005-09-29, 7:02 pm |
| CD,
Why are you using @Integer? Why not write the query without a subquery?
Can you provide the DDL and INSERTs (sample data)?
HTH
Jerry
"CD" <CD@discussions.microsoft.com> wrote in message
news:7C70FDC9-9B9E-4822-8724-6C9548B4B349@microsoft.com...
> O.k. the following query works, unless there is more that one value a
> column.
> It tells me, "Subquery returned mor than 1 value. This is not permitted
> when the subquery follows =, or when the subquery is used as an
> expression".
> How can I get this query to format each item in a column?
>
> declare @Integer money
>
> select @Integer = (select amount from table_name)
>
> select left( replicate("0", 15-len('amount')) +
> convert(nvarchar,@Integer*100),15)
>
> The query pads the value in the column with leading zeros until the value
> is
> equal to a length of 15. I need it to change every row in the column.
| |
| Tony Sebion 2005-09-29, 7:02 pm |
| I think a query like this might be better than the separate declare
statement and two select statements:
select right(replicate('0',15) +
ltrim(rtrim(convert(varchar,amount))),15
) from <table_name>
Good luck,
Tony Sebion
"CD" <CD@discussions.microsoft.com> wrote in message
news:7C70FDC9-9B9E-4822-8724-6C9548B4B349@microsoft.com:
> O.k. the following query works, unless there is more that one value a column.
> It tells me, "Subquery returned mor than 1 value. This is not permitted
> when the subquery follows =, or when the subquery is used as an expression".
> How can I get this query to format each item in a column?
>
> declare @Integer money
>
> select @Integer = (select amount from table_name)
>
> select left( replicate("0", 15-len('amount')) +
> convert(nvarchar,@Integer*100),15)
>
> The query pads the value in the column with leading zeros until the value is
> equal to a length of 15. I need it to change every row in the column.
| |
| David Portas 2005-09-29, 7:02 pm |
| Try this:
SELECT RIGHT(REPLICATE('0',15)+
CAST(amount*100 AS NVARCHAR(15)),15)
FROM table_name ;
--
David Portas
SQL Server MVP
--
"CD" wrote:
> O.k. the following query works, unless there is more that one value a column.
> It tells me, "Subquery returned mor than 1 value. This is not permitted
> when the subquery follows =, or when the subquery is used as an expression".
> How can I get this query to format each item in a column?
>
> declare @Integer money
>
> select @Integer = (select amount from table_name)
>
> select left( replicate("0", 15-len('amount')) +
> convert(nvarchar,@Integer*100),15)
>
> The query pads the value in the column with leading zeros until the value is
> equal to a length of 15. I need it to change every row in the column.
| |
|
| Almost, but not quite. This does not take off the decimal places and doesn't
account for the fact that the length of numbers in each row can be different.
For instance, if the value is 600.00 I need to get 000000000060000 and if
the number is 999999999.00 I need 000099999999900.
"David Portas" wrote:
[color=darkred]
> Try this:
>
> SELECT RIGHT(REPLICATE('0',15)+
> CAST(amount*100 AS NVARCHAR(15)),15)
> FROM table_name ;
>
> --
> David Portas
> SQL Server MVP
> --
>
>
>
> "CD" wrote:
>
| |
|
| I have to get rid of the decimal point (move it over 2 places), so I need to
multiply by 100 and my understanding is that I need the value to be an int to
do so.
"Jerry Spivey" wrote:
> CD,
>
> Why are you using @Integer? Why not write the query without a subquery?
> Can you provide the DDL and INSERTs (sample data)?
>
> HTH
>
> Jerry
> "CD" <CD@discussions.microsoft.com> wrote in message
> news:7C70FDC9-9B9E-4822-8724-6C9548B4B349@microsoft.com...
>
>
>
| |
| Jerry Spivey 2005-09-29, 7:02 pm |
| Not sure if this is the best for performance but...extending David's code a
bit:
SELECT RIGHT(REPLICATE('0',15)+ SUBSTRING(CAST(salary*100 AS
NVARCHAR(15)),1,CHARINDEX('.',CAST(salary*100 AS NVARCHAR(15)))-1),15) AS
'New Number'
FROM temployeesalary
HTH
Jerry
PS - David nice coding!!! Learn from you everyday man.
"CD" <CD@discussions.microsoft.com> wrote in message
news:76E9117D-F510-4C81-BDA5-97A0F5D3827C@microsoft.com...[color=darkred]
>I have to get rid of the decimal point (move it over 2 places), so I need
>to
> multiply by 100 and my understanding is that I need the value to be an int
> to
> do so.
>
> "Jerry Spivey" wrote:
>
| |
| David Portas 2005-09-29, 7:02 pm |
| SELECT RIGHT(REPLICATE('0',15)+
CAST(CAST(amount*100 AS BIGINT) AS NVARCHAR(15)),15)
FROM table_name ;
Of course if you had posted DDL and an INSERT script I would have
tested it the first time... You'll get quicker more accurate answers
that way because those who respond won't need to make unsupported
guesses about your requirements.
--
David Portas
SQL Server MVP
--
| |
|
| Hey, looks like that works! Performance shouldn't be a big problem b/c the
tables won't be too big.
"Jerry Spivey" wrote:
> Not sure if this is the best for performance but...extending David's code a
> bit:
>
> SELECT RIGHT(REPLICATE('0',15)+ SUBSTRING(CAST(salary*100 AS
> NVARCHAR(15)),1,CHARINDEX('.',CAST(salary*100 AS NVARCHAR(15)))-1),15) AS
> 'New Number'
> FROM temployeesalary
>
> HTH
>
> Jerry
>
> PS - David nice coding!!! Learn from you everyday man.
> "CD" <CD@discussions.microsoft.com> wrote in message
> news:76E9117D-F510-4C81-BDA5-97A0F5D3827C@microsoft.com...
>
>
>
| |
| Jerry Spivey 2005-09-29, 7:02 pm |
| Guess I'm still learning ;-)
"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:uzTE30RxFHA.2924@TK2MSFTNGP15.phx.gbl...
> Not sure if this is the best for performance but...extending David's code
> a bit:
>
> SELECT RIGHT(REPLICATE('0',15)+ SUBSTRING(CAST(salary*100 AS
> NVARCHAR(15)),1,CHARINDEX('.',CAST(salary*100 AS NVARCHAR(15)))-1),15) AS
> 'New Number'
> FROM temployeesalary
>
> HTH
>
> Jerry
>
> PS - David nice coding!!! Learn from you everyday man.
> "CD" <CD@discussions.microsoft.com> wrote in message
> news:76E9117D-F510-4C81-BDA5-97A0F5D3827C@microsoft.com...
>
>
|
|
|
|
|