|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help with subqueryO.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. 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 Show quote "CD" <C*@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. 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. Show quote "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" <C*@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. > > > 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. Show quote "CD" <C*@discussions.microsoft.com> wrote in message news:76E9117D-F510-4C81-BDA5-97A0F5D3827C@microsoft.com... >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" <C*@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. >> >> >> Hey, looks like that works! Performance shouldn't be a big problem b/c the
tables won't be too big. Show quote "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" <C*@discussions.microsoft.com> wrote in message > news:76E9117D-F510-4C81-BDA5-97A0F5D3827C@microsoft.com... > >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" <C*@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. > >> > >> > >> > > > Guess I'm still learning ;-)
Show quote "Jerry Spivey" <jspi***@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" <C*@discussions.microsoft.com> wrote in message > news:76E9117D-F510-4C81-BDA5-97A0F5D3827C@microsoft.com... >>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" <C*@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. >>> >>> >>> > > 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 Show quote "CD" <C*@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. Try this:
SELECT RIGHT(REPLICATE('0',15)+ CAST(amount*100 AS NVARCHAR(15)),15) FROM table_name ; -- Show quoteDavid 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. Show quote "David Portas" wrote: > 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. 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 --  |
|||||||||||||||||||||||
Other interesting topics