Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL View Data Type Issue

Posted on 2008-06-19
9
Medium Priority
?
2,203 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
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
The viewer will learn how to use and create keystrokes 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.

688 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