Data Conversion

Hello everyone,

I am running SQL-2000, I have a table that one field ddefined as char. The data is actually Dollar values(no $ signs just 99.25 for example). I need to convert this column from char to Numeric. I am trying to use Enterprise manager to redesign the table but I get "error converting data type VARCHAR to numeric". Enterprise manager shows the field as CHAR. I have no Idea why that error is comming up. I would like any info that could help me with this conversion. Thanks in advance.

EvThe isnum function can be used to find values in your data which cannot be converted to numbers:

select *
from [YOURTABLE]
where isnum([VALUEFIELD]) = 0|||Hi you can use the following query
select value=convert(numeric,ddefined) from table

Madhivanan|||I ran the ISNUM function and all rows are good. However I get the same error.|||I might be off here, but perhaps there's a value in there that's a valid numeric but can't be converted from varchar to numeric (see: http://www.dbforums.com/t998353.html) ? Anyway, I ran into the same thing a while back; http://www.dbforums.com/t1023776.html got it solved.|||You are right. I am not sure why I can't convert to Numeric but I am able to convert to MONEY. Thats works fine for the application. Thanks for your help.

Ev

Data Conversion

Here is my table:
CustomerShortName varchar (50)
CustomerID varchar(8)
OrderMonth char(2)
OrderYear char(4)
OrderCount varchar(50)
RegionID varchar(50)
Then the report is based on this sproc:
CREATE PROCEDURE dbo.renMonthlyOrderReport
(
@.StartMonth AS INT,
@.StartYear AS INT,
@.RegionIDs varchar(25)
)
AS
DECLARE @.Month1 AS INT, @.Month2 AS INT, @.Month3 AS INT, @.Month4 AS INT, @.Month5 AS INT, @.Month6 AS INT
DECLARE @.Year1 AS INT, @.Year2 AS INT, @.Year3 AS INT, @.Year4 AS INT, @.Year5 AS INT, @.Year6 AS INT
SELECT @.Month1 = @.StartMonth
SELECT @.Month2 = @.StartMonth + 1
SELECT @.Month3 = @.StartMonth + 2
SELECT @.Month4 = @.StartMonth + 3
SELECT @.Month5 = @.StartMonth + 4
SELECT @.Month6 = @.StartMonth + 5
SELECT @.Year1 = @.StartYear
SELECT @.Year2 = @.StartYear
SELECT @.Year3 = @.StartYear
SELECT @.Year4 = @.StartYear
SELECT @.Year5 = @.StartYear
SELECT @.Year6 = @.StartYear
IF @.Month1 > 12 SELECT @.Year1 = @.Year1 + 1
IF @.Month2 > 12 SELECT @.Year2 = @.Year2 + 1
IF @.Month3 > 12 SELECT @.Year3 = @.Year3 + 1
IF @.Month4 > 12 SELECT @.Year4 = @.Year4 + 1
IF @.Month5 > 12 SELECT @.Year5 = @.Year5 + 1
IF @.Month6 > 12 SELECT @.Year6 = @.Year6 + 1
IF @.Month1 > 12 SELECT @.Month1 = @.Month1 - 12
IF @.Month2 > 12 SELECT @.Month2 = @.Month2 - 12
IF @.Month3 > 12 SELECT @.Month3 = @.Month3 - 12
IF @.Month4 > 12 SELECT @.Month4 = @.Month4 - 12
IF @.Month5 > 12 SELECT @.Month5 = @.Month5 - 12
IF @.Month6 > 12 SELECT @.Month6 = @.Month6 - 12
SELECT
DISTINCT(dbo.TD_SALES.CustomerShortName),
CAST([dbo].[TD_SALES].OrderMonth as int) as OrderMonth,
CAST([dbo].[TD_SALES].OrderYear as int) OrderYear,
[dbo].[TD_SALES].RegionID,
dbo.TD_SALES.OrderCount
FROM
[dbo].[TD_SALES]
WHERE
(
[dbo].[TD_SALES].OrderMonth = @.Month1 AND [dbo].[TD_SALES].OrderYear = @.Year1 OR
[dbo].[TD_SALES].OrderMonth = @.Month2 AND [dbo].[TD_SALES].OrderYear = @.Year2 OR
[dbo].[TD_SALES].OrderMonth = @.Month3 AND [dbo].[TD_SALES].OrderYear = @.Year3 OR
[dbo].[TD_SALES].OrderMonth = @.Month4 AND [dbo].[TD_SALES].OrderYear = @.Year4 OR
[dbo].[TD_SALES].OrderMonth = @.Month5 AND [dbo].[TD_SALES].OrderYear = @.Year5 OR
[dbo].[TD_SALES].OrderMonth = @.Month6 AND [dbo].[TD_SALES].OrderYear = @.Year6
)
AND
[dbo].[TD_SALES].RegionID IN (SELECT * FROM RegionsTable(@.RegionIDs))
ORDER BY
dbo.TD_SALES.RegionID,
CAST([dbo].[TD_SALES].OrderMonth as int) DESC,
CAST([dbo].[TD_SALES].OrderYear as int) DESC,
dbo.TD_SALES.OrderCount DESC,
dbo.TD_SALES.CustomerShortName
GO
The OrderCount field is varchar because the data can be actual count , * , or null. But right now in my report I have a matrix table. How would I go about displaying the * character, null or a actual number but still be able to use the matrix for subtotaling?
Thanks,
AlTry this as the expression in your data cell (this will treat * as 0 for
aggregation purposes).
=iif(InScope("matrix1_ColumnGroup") AND
InScope("matrix1_RowGroup"),Fields!OrderCount.Value,Sum(iif(Fields!OrderCoun
t.Value="*",0,Fields!OrderCount.Value)))
Change the group names to be the innermost row and column group names in
your matrix.
If you want any * to result in * for the aggregate, you'd do this instead:
=iif(InScope("matrix1_ColumnGroup") AND
InScope("matrix1_RowGroup"),Fields!OrderCount.Value,
iif(Sum(iif(Fields!OrderCount.Value="*",1,0))>0,"*",Sum(iif(Fields!OrderCoun
t.Value="*",0,Fields!OrderCount.Value)))
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Al" <Al@.discussions.microsoft.com> wrote in message
news:D3794A37-C147-492F-BE37-870BF036C541@.microsoft.com...
> Here is my table:
> CustomerShortName varchar (50)
> CustomerID varchar(8)
> OrderMonth char(2)
> OrderYear char(4)
> OrderCount varchar(50)
> RegionID varchar(50)
> Then the report is based on this sproc:
> CREATE PROCEDURE dbo.renMonthlyOrderReport
> (
> @.StartMonth AS INT,
> @.StartYear AS INT,
> @.RegionIDs varchar(25)
> )
> AS
> DECLARE @.Month1 AS INT, @.Month2 AS INT, @.Month3 AS INT, @.Month4 AS INT,
@.Month5 AS INT, @.Month6 AS INT
> DECLARE @.Year1 AS INT, @.Year2 AS INT, @.Year3 AS INT, @.Year4 AS INT, @.Year5
AS INT, @.Year6 AS INT
> SELECT @.Month1 = @.StartMonth
> SELECT @.Month2 = @.StartMonth + 1
> SELECT @.Month3 = @.StartMonth + 2
> SELECT @.Month4 = @.StartMonth + 3
> SELECT @.Month5 = @.StartMonth + 4
> SELECT @.Month6 = @.StartMonth + 5
> SELECT @.Year1 = @.StartYear
> SELECT @.Year2 = @.StartYear
> SELECT @.Year3 = @.StartYear
> SELECT @.Year4 = @.StartYear
> SELECT @.Year5 = @.StartYear
> SELECT @.Year6 = @.StartYear
> IF @.Month1 > 12 SELECT @.Year1 = @.Year1 + 1
> IF @.Month2 > 12 SELECT @.Year2 = @.Year2 + 1
> IF @.Month3 > 12 SELECT @.Year3 = @.Year3 + 1
> IF @.Month4 > 12 SELECT @.Year4 = @.Year4 + 1
> IF @.Month5 > 12 SELECT @.Year5 = @.Year5 + 1
> IF @.Month6 > 12 SELECT @.Year6 = @.Year6 + 1
> IF @.Month1 > 12 SELECT @.Month1 = @.Month1 - 12
> IF @.Month2 > 12 SELECT @.Month2 = @.Month2 - 12
> IF @.Month3 > 12 SELECT @.Month3 = @.Month3 - 12
> IF @.Month4 > 12 SELECT @.Month4 = @.Month4 - 12
> IF @.Month5 > 12 SELECT @.Month5 = @.Month5 - 12
> IF @.Month6 > 12 SELECT @.Month6 = @.Month6 - 12
>
> SELECT
> DISTINCT(dbo.TD_SALES.CustomerShortName),
> CAST([dbo].[TD_SALES].OrderMonth as int) as OrderMonth,
> CAST([dbo].[TD_SALES].OrderYear as int) OrderYear,
> [dbo].[TD_SALES].RegionID,
> dbo.TD_SALES.OrderCount
>
> FROM
> [dbo].[TD_SALES]
> WHERE
> (
> [dbo].[TD_SALES].OrderMonth = @.Month1 AND [dbo].[TD_SALES].OrderYear =@.Year1 OR
> [dbo].[TD_SALES].OrderMonth = @.Month2 AND [dbo].[TD_SALES].OrderYear =@.Year2 OR
> [dbo].[TD_SALES].OrderMonth = @.Month3 AND [dbo].[TD_SALES].OrderYear =@.Year3 OR
> [dbo].[TD_SALES].OrderMonth = @.Month4 AND [dbo].[TD_SALES].OrderYear =@.Year4 OR
> [dbo].[TD_SALES].OrderMonth = @.Month5 AND [dbo].[TD_SALES].OrderYear =@.Year5 OR
> [dbo].[TD_SALES].OrderMonth = @.Month6 AND [dbo].[TD_SALES].OrderYear =@.Year6
> )
> AND
> [dbo].[TD_SALES].RegionID IN (SELECT * FROM RegionsTable(@.RegionIDs))
> ORDER BY
> dbo.TD_SALES.RegionID,
> CAST([dbo].[TD_SALES].OrderMonth as int) DESC,
> CAST([dbo].[TD_SALES].OrderYear as int) DESC,
> dbo.TD_SALES.OrderCount DESC,
> dbo.TD_SALES.CustomerShortName
> GO
>
> The OrderCount field is varchar because the data can be actual count ,
* , or null. But right now in my report I have a matrix table. How would I
go about displaying the * character, null or a actual number but still be
able to use the matrix for subtotaling?
> Thanks,
> Al|||I forgot something. You need to cast as well. Try this:
=iif(InScope("matrix1_MonthGroup") AND
InScope("matrix1_LenderNameGroup"),Fields!OrderCount.Value,
Sum(iif(Fields!OrderCount.Value="*",0,CDbl(Fields!OrderCount.Value))))
Or use CInt instead if the numbers are all integers.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Al" <Al@.discussions.microsoft.com> wrote in message
news:203A383D-AAF1-49BD-8BC3-05CE64858D23@.microsoft.com...
> I used your expression in the data cell
> =iif(InScope("matrix1_MonthGroup") AND
InScope("matrix1_LenderNameGroup"),Fields!OrderCount.Value,
Sum(iif(Fields!OrderCount.Value="*",0,Fields!OrderCount.Value)))
> I get the desired output for the * char but it still returns '#Error'
if the condition of Fields!OrderCount.Value = "*" is not true.
> "Chris Hays [MSFT]" wrote:
> > Try this as the expression in your data cell (this will treat * as 0 for
> > aggregation purposes).
> >
> > =iif(InScope("matrix1_ColumnGroup") AND
> >
InScope("matrix1_RowGroup"),Fields!OrderCount.Value,Sum(iif(Fields!OrderCoun
> > t.Value="*",0,Fields!OrderCount.Value)))
> >
> > Change the group names to be the innermost row and column group names in
> > your matrix.
> >
> > If you want any * to result in * for the aggregate, you'd do this
instead:
> > =iif(InScope("matrix1_ColumnGroup") AND
> > InScope("matrix1_RowGroup"),Fields!OrderCount.Value,
> >
iif(Sum(iif(Fields!OrderCount.Value="*",1,0))>0,"*",Sum(iif(Fields!OrderCoun
> > t.Value="*",0,Fields!OrderCount.Value)))
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "Al" <Al@.discussions.microsoft.com> wrote in message
> > news:D3794A37-C147-492F-BE37-870BF036C541@.microsoft.com...
> > > Here is my table:
> > > CustomerShortName varchar (50)
> > > CustomerID varchar(8)
> > > OrderMonth char(2)
> > > OrderYear char(4)
> > > OrderCount varchar(50)
> > > RegionID varchar(50)
> > >
> > > Then the report is based on this sproc:
> > > CREATE PROCEDURE dbo.renMonthlyOrderReport
> > > (
> > > @.StartMonth AS INT,
> > > @.StartYear AS INT,
> > > @.RegionIDs varchar(25)
> > > )
> > > AS
> > > DECLARE @.Month1 AS INT, @.Month2 AS INT, @.Month3 AS INT, @.Month4 AS
INT,
> > @.Month5 AS INT, @.Month6 AS INT
> > > DECLARE @.Year1 AS INT, @.Year2 AS INT, @.Year3 AS INT, @.Year4 AS INT,
@.Year5
> > AS INT, @.Year6 AS INT
> > >
> > > SELECT @.Month1 = @.StartMonth
> > > SELECT @.Month2 = @.StartMonth + 1
> > > SELECT @.Month3 = @.StartMonth + 2
> > > SELECT @.Month4 = @.StartMonth + 3
> > > SELECT @.Month5 = @.StartMonth + 4
> > > SELECT @.Month6 = @.StartMonth + 5
> > >
> > > SELECT @.Year1 = @.StartYear
> > > SELECT @.Year2 = @.StartYear
> > > SELECT @.Year3 = @.StartYear
> > > SELECT @.Year4 = @.StartYear
> > > SELECT @.Year5 = @.StartYear
> > > SELECT @.Year6 = @.StartYear
> > >
> > > IF @.Month1 > 12 SELECT @.Year1 = @.Year1 + 1
> > > IF @.Month2 > 12 SELECT @.Year2 = @.Year2 + 1
> > > IF @.Month3 > 12 SELECT @.Year3 = @.Year3 + 1
> > > IF @.Month4 > 12 SELECT @.Year4 = @.Year4 + 1
> > > IF @.Month5 > 12 SELECT @.Year5 = @.Year5 + 1
> > > IF @.Month6 > 12 SELECT @.Year6 = @.Year6 + 1
> > >
> > > IF @.Month1 > 12 SELECT @.Month1 = @.Month1 - 12
> > > IF @.Month2 > 12 SELECT @.Month2 = @.Month2 - 12
> > > IF @.Month3 > 12 SELECT @.Month3 = @.Month3 - 12
> > > IF @.Month4 > 12 SELECT @.Month4 = @.Month4 - 12
> > > IF @.Month5 > 12 SELECT @.Month5 = @.Month5 - 12
> > > IF @.Month6 > 12 SELECT @.Month6 = @.Month6 - 12
> > >
> > >
> > > SELECT
> > > DISTINCT(dbo.TD_SALES.CustomerShortName),
> > > CAST([dbo].[TD_SALES].OrderMonth as int) as OrderMonth,
> > > CAST([dbo].[TD_SALES].OrderYear as int) OrderYear,
> > > [dbo].[TD_SALES].RegionID,
> > > dbo.TD_SALES.OrderCount
> > >
> > >
> > > FROM
> > > [dbo].[TD_SALES]
> > >
> > > WHERE
> > > (
> > > [dbo].[TD_SALES].OrderMonth = @.Month1 AND [dbo].[TD_SALES].OrderYear => > @.Year1 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month2 AND [dbo].[TD_SALES].OrderYear => > @.Year2 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month3 AND [dbo].[TD_SALES].OrderYear => > @.Year3 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month4 AND [dbo].[TD_SALES].OrderYear => > @.Year4 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month5 AND [dbo].[TD_SALES].OrderYear => > @.Year5 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month6 AND [dbo].[TD_SALES].OrderYear => > @.Year6
> > > )
> > > AND
> > > [dbo].[TD_SALES].RegionID IN (SELECT * FROM RegionsTable(@.RegionIDs))
> > >
> > > ORDER BY
> > > dbo.TD_SALES.RegionID,
> > > CAST([dbo].[TD_SALES].OrderMonth as int) DESC,
> > > CAST([dbo].[TD_SALES].OrderYear as int) DESC,
> > > dbo.TD_SALES.OrderCount DESC,
> > > dbo.TD_SALES.CustomerShortName
> > > GO
> > >
> > >
> > > The OrderCount field is varchar because the data can be actual
count ,
> > * , or null. But right now in my report I have a matrix table. How
would I
> > go about displaying the * character, null or a actual number but still
be
> > able to use the matrix for subtotaling?
> > >
> > > Thanks,
> > > Al
> >
> >
> >