Solved

SQL View Data Type Issue

Posted on 2008-06-19
9
2,198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How our DevOps Teams Maximize Uptime

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

 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

724 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