Solved

SQL View Data Type Issue

Posted on 2008-06-19
9
2,187 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org (http://seleniumhq.org) Go to that link and select download selenium in the right hand columnThat will then direct you to their downlo…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

747 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

14 Experts available now in Live!

Get 1:1 Help Now