I added a field to an exist View in a SQL2005 database. The data definition of the Source Column is FLOAT. In the view, the new column was added as INT.
I updated a Crystal report to use the new field in the View and get an error: Failed to retrieve data from the database. Details: 422000: [Microsoft][ODBC SQL Server Driver] [SQL Server] Error converting data type varchar to float. [Database Vendor Code: 8114]
a) Why was the same data type not carried into the View?
b) How can I alter the data type?
Here is the code I used to originally modify the View (the column DiscoutAmount was what was added): The bulk of the code was created by SQL Management Studio.
/****** Object: View [dbo].[InvoiceDetail_vw_DetailsAndTaxes] Script Date: 06/19/2008 07:47:22 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF
ALTER VIEW [dbo].[InvoiceDetail_vw_DetailsAndTaxes] ( InvoiceDetailUID, InvoiceUID, SiteID, ServiceUID, SourceUID, SourceIDNbr, LineType, LineSubType, SvcCode,
Description, Salesperson, SvcFrequency, Quantity, Amount, UnitPrice, TaxAmount, StartDate, EndDate, PrintRecord, LineOrderKey1, LineOrderKey2, LineOrderKey3,
LineOrderKey4, PONbr, POSource, Tax, DiscountAmount )
SELECT d.InvoiceDetailUID, d.InvoiceUID, d.SiteID, d.ServiceUID, d.SourceUID, d.SourceIDNbr, d.LineType, d.LineSubType, d.SvcCode, d.Description, d.Salesperson,
d.SvcFrequency, d.Quantity, d.Amount, d.UnitPrice, d.TaxAmount, d.StartDate, d.EndDate, d.PrintRecord, d.LineOrderKey1, d.LineOrderKey2, d.LineOrderKey3,
d.LineOrderKey4, d.PONbr, d.POSource, d.DiscountAmount, NULL
FROM [dbo].[InvoiceDetail] d WITH(NOLOCK)
SELECT NULL, d.InvoiceUID, d.SiteID, NULL, NULL, NULL, NULL, NULL, NULL, MAX(t.Description), NULL, NULL, NULL, SUM(td.TaxAmount), NULL, NULL, NULL, NULL, 1,
d.LineOrderKey1, NULL, NULL, NULL, NULL, NULL, td.Tax, NULL
FROM [dbo].[InvoiceTaxDetail] td
INNER JOIN [dbo].[InvoiceDetail] d WITH(NOLOCK) ON d.InvoiceDetailUID = td.InvoiceDetailUID
INNER JOIN [dbo].[Invoice] i WITH(NOLOCK) ON i.InvoiceUID = d.InvoiceUID
INNER JOIN [dbo].[Tax] t WITH(NOLOCK) ON t.Tax = td.Tax
GROUP BY d.InvoiceUID, d.SiteID, d.LineOrderKey1, td.Tax