Converting decimal issue

[TeamGD]

In a Select statement I want to convert the values in a numeric field to numeric values with the number of decimals equal to the value of another field.

I’ve tried:

CONVERT (decimal(18 , CurrentDecimal), Price)

But I get an error message “Invalid or missing expression”.

Price field is numeric, precision 18, scale 6
CurrentDecimal field is integer

Is this achievable using Convert ?

Cheers

 

[jby1]

Is this what you are after?

SELECT Price = CONVERT (decimal(18, 6) , CurrentDecimal)

 

[TeamGD]

Thanks for the reply jby1. Your suggestion is not quite what I want.

I’ll expand on my requirements.

Simplified, I have one table as follows:
MarketCode (char)
Price (numeric with precision = 18 and scale = 6)

I have another table as follows:
MarketCode (char)
CurrentDecimal (integer)

I need to display the Prices for each MarketCode as a string with the number of decimals to be displayed in each Price string to be equal the value in CurrentDecimal for the MarketCode.

For example
Table1:
Record1: MarketCode: CBA, Price: 123.560000
Record2: MarketCode: CBA, Price: 456.789000
Record3: MarketCode: NCP, Price: 98.761000

Table2:
Record1: MarketCode: CBA, CurrentDecimal: 3
Record2: MarketCode: NCP, CurrentDecimal: 2

I want a View to display:

Row 1: MarketCode: CBA, CurrentPrice: 123.560
Row 2: MarketCode: CBA, CurrentPrice: 456.789
Row 3: MarketCode: NCP, CurrentPrice: 98.76

The CurrentPrice is the Price rounded to the number of decimals in CurrentDecimal for that MarketCode. I want to know if I can use the Convert function to achieve what I want in CurrentPrice.

Hopefully this clarifies my requirements better.

Cheers

 

[jby1]

In that case, I think you need to build up a string and then execute the string using sp_executesql. The following should help you achieve that.

declare @dec1 decimal(10,6)
declare @scal int
declare @sql nvarchar(100)

select @dec1 = 102.560000

select @scal = 3

select @sql = N'select convert(decimal(10, ' + convert(nvarchar(20),@scal) + '), ' + convert(nvarchar(20), @dec1) + ')'

exec sp_executesql @sql