Solved

SQL View Data Type Issue

Posted on 2008-06-19
9
2,188 Views
Last Modified: 2013-11-06
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_DetailsAndTaxes]    Script Date: 06/19/2008 07:47:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
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 )
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
0
Comment
Question by:CBrien
  • 4
  • 3
9 Comments
 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 21828519
Hi
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 ...

0
 

Author Comment

by:CBrien
ID: 21830383
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.
0
 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 21835454
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!
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Accepted Solution

by:
CBrien earned 0 total points
ID: 21846125
Since I needed the field to be defined as a float number, I don't believe the STR would work.

I found an error in my original Modify View statement.  The field was in the wrong column in the first Select statement.  That caused the resulting definition to have the wrong data type and invalid values were being returned to the report.
0
 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 21847996
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.
0
 

Author Comment

by:CBrien
ID: 21848682
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.
0
 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 21849089
Thanks for the consideration. I will leave it to thier discretion then. good luck in your future efforts have a great day.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now