SQL View Data Type Issue
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.
USE [XXXXXXTest]
GO
/****** Object: View [dbo].[InvoiceDetail_vw_De
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER VIEW [dbo].[InvoiceDetail_vw_De
Description, Salesperson, SvcFrequency, Quantity, Amount, UnitPrice, TaxAmount, StartDate, EndDate, PrintRecord, LineOrderKey1, LineOrderKey2, LineOrderKey3,
LineOrderKey4, PONbr, POSource, Tax, DiscountAmount )
AS
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)
UNION
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
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.
USE [XXXXXXTest]
GO
/****** Object: View [dbo].[InvoiceDetail_vw_De
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER VIEW [dbo].[InvoiceDetail_vw_De
Description, Salesperson, SvcFrequency, Quantity, Amount, UnitPrice, TaxAmount, StartDate, EndDate, PrintRecord, LineOrderKey1, LineOrderKey2, LineOrderKey3,
LineOrderKey4, PONbr, POSource, Tax, DiscountAmount )
AS
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)
UNION
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
ASKER
tshel,
Thanks for the comment.
I know how to change the type in Crystal, what I need to know is how to get the Data Type corrected in the DB view so it matches the true type in the orginal table. Since they don't match, whenever I reference the field from the View, Crystal throws up and cannot continue.
Regards.
Thanks for the comment.
I know how to change the type in Crystal, what I need to know is how to get the Data Type corrected in the DB view so it matches the true type in the orginal table. Since they don't match, whenever I reference the field from the View, Crystal throws up and cannot continue.
Regards.
Yes That's What I meant. So sorry if my reposnse wasn't clear. Yes you would need to change the SQL syntax in the SQL view using the STR() SQL function ... Here is the MSDN link to help you through this...
http://msdn.microsoft.com/en-us/library/aa259237(SQL.80).aspx
Hope it helps...
Thanks!
http://msdn.microsoft.com/en-us/library/aa259237(SQL.80).aspx
Hope it helps...
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sounds like you have your own solution great! -- however, before points are refunded back to asker, in as much as I appreciate that, please consider that it wasn't posted that the float was absolutely required in the beginning and the questions were:
a) Why was the same data type not carried into the View?
b) How can I alter the data type?
which were rboth esearched and answered in accordance with that in consideration ---
Thank You.
a) Why was the same data type not carried into the View?
b) How can I alter the data type?
which were rboth esearched and answered in accordance with that in consideration ---
Thank You.
ASKER
Since the original request specifically listed FLOAT vs. INT, I believe the problem and intent were clear. I appreciate the effort and thought provided but it was not the solution to the problem. I am not a frequent user of this great service other than reading questions from others which usually gives me the info I need, but I believe points are usually only awarded for solutions or information to guides someone directly to the solution.
If the moderators believe I am off on this, I will be glad to award the points.
If the moderators believe I am off on this, I will be glad to award the points.
Thanks for the consideration. I will leave it to thier discretion then. good luck in your future efforts have a great day.
Hi!
In answer to your question(s):
a) Why was the same data type not carried into the View?
b) How can I alter the data type?
Values of float are truncated when they are converted to any integer type.
When you want to convert from float or real to character data, using the STR string function is usually more useful than CAST( ). This is because STR enables more control over formatting.
For more information on Data Type Conversions see: http://msdn.microsoft.com/en-us/library/ms191530.aspx
Hope it helps ...