Solved

SQL View Data Type Issue

Posted on 2008-06-19
9
2,196 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-sql Date Format 9 65
Return Rows as per Quantity of Columns Value In SQL 6 36
Need to create and populate a column map table 5 58
Please explain Equi-join 3 43
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

737 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