Floating point fun

I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
migration. I've read certain floating-point values can't be represented
accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001? Converting the column to DECIMAL is probably not an
option.
Thanks
Damien
CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
FLOAT(2) )
-- Try and insert the value 4.1 into the float table
INSERT INTO #float_test ( ft_id, rate1 )
SELECT 1, 4.1 UNION
SELECT 2, 4 + .1 UNION
SELECT 3, '4.10' UNION
SELECT 4, 4.11 UNION
SELECT 5, 4.95 UNION
SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
SELECT 7, .1
GO
UPDATE #float_test
SET rate2 = rate1
SELECT * FROM #float_test
DROP TABLE #float_testHi
Read http://www.aspfaq.com/show.asp?id=2477
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:C4AF11D4-0AD6-4EE3-8453-FFF203829E25@.microsoft.com...
> I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
> migration. I've read certain floating-point values can't be represented
> accurately, however, .1 doesn't look like one of them as the FLOAT(2)
> field
> copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1
> round
> to .10000000000000001? Converting the column to DECIMAL is probably not
> an
> option.
> Thanks
> Damien
> CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
> FLOAT(2) )
> -- Try and insert the value 4.1 into the float table
> INSERT INTO #float_test ( ft_id, rate1 )
> SELECT 1, 4.1 UNION
> SELECT 2, 4 + .1 UNION
> SELECT 3, '4.10' UNION
> SELECT 4, 4.11 UNION
> SELECT 5, 4.95 UNION
> SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
> SELECT 7, .1
> GO
> UPDATE #float_test
> SET rate2 = rate1
> SELECT * FROM #float_test
> DROP TABLE #float_test
>|||On Tue, 23 Aug 2005 01:43:07 -0700, Damien
<Damien@.discussions.microsoft.com> wrote:

>I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
>migration. I've read certain floating-point values can't be represented
>accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
>copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
>to .10000000000000001? Converting the column to DECIMAL is probably not an
>option.
It's not clear to me what you expect to happen. Floating-point values
*are* precisely represented by floating-point values. (cough)
OTOH, certain fractional values are not in the domain of certain
floating-point data types. In those cases, software generally picks
the closest value available. (The result is "error of approximation",
not a rounding error.)
You seem to think that every number that ends in '.1' should have the
same behavior. That's simply not true of floating-point data types.
It might help to think of it this way. Neighboring, distinct values in
exact data types are the same distance apart on a number line. That
is, each value in a SQL INTEGER data type is plus or minus 1 from its
neighbor.
But neighboring, distinct values in a floating-point data type are not
the same distance apart on a number line. The closer you get to zero
(from either direction), the more closely spaced the neighboring
distinct values are.
Mike Sherrill|||> Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001?
because under the hood floats are stored as binaries. So, binary
numbers are represented accurately, up to some accuracy, of course.
Decimals are rounded to binaries. When you convert binaries back to
decimals, expect some mismatch|||On 24 Aug 2005 14:36:10 -0700, ford_desperado@.yahoo.com wrote:
[snip]
>Decimals are rounded to binaries.
Error of approximation, which seems to be what you're stumbling
toward, doesn't mean "fixed point numbers are rounded to binary". See
Knuth, vol 2.

floating point calculation

I'm trying to perform a calculation on a field in SQL Server that
should return a value with a decimal point. My problem is that the
value returned is truncated without the decimal point. Is there a
setting that needs to be turned on in SQL server to allow this?

for example

Select 20/3

should return 6.6666667

but instead I get 6"Never" <nevermind@.iname.com> wrote in message
news:e43b4225.0405071534.9a29f0a@.posting.google.co m...
> I'm trying to perform a calculation on a field in SQL Server that
> should return a value with a decimal point. My problem is that the
> value returned is truncated without the decimal point. Is there a
> setting that needs to be turned on in SQL server to allow this?
> for example
> Select 20/3
> should return 6.6666667

No, you should get 6.

Try select 20.0/3.0

You'll get 6.666666

When you say select 20/3 you're telling SQL Server you're starting with
ints, so it converts the answer to an int.

> but instead I get 6

Float to Datetime Conversion

I need to convert values in a float data type field to that of datetime. The float data type field currently contains values such as 20060927,20060928, etc. Any suggestions?
Thanks in advance,
sajmeraconvert(datetime,cast(cast(foo as integer) as char),112)|||Thanks for the quick reply. I also tried the following and got the result.

convert(datetime,convert(varchar(20),convert(int,c onvert(float,<field name>))))

Thanks again!|||SELECT CAST(CONVERT(VARCHAR, Col1) AS DATETIME) AS NewValue
FROM Table1

Float or Int

Does it make a difference if I use the Float or Int data type for a field such as ReceiptNumber or CheckNumber?

Thanks for any thoughts,It absolutely makes a difference -- use an int. Floating-point columns are used for scientific calculations and store floating-point numbers.

And actually, depending on how those columns are being generated and what their purpose is, I feel that varchar might be a better choice. To me, numeric data types are only appropriate for primary keys and for columns where some sort of math is going to happen.

Terri|||Required more memory too..

KP|||Is there a way that I can globally change the float to int in all of the tables in a DB? and is there an example of this some where?

Thank you,|||Well, you can find out where you have used the float data type in your database by issuing this:

SELECT * FROM information_schema.columns WHERE data_type = 'float'

You could write some script that selects this information into a cursor and then you could scroll through the cursor putting together dynamic SQL statements to execute that would do an ALTER TABLE and change the data type. Whether or not this would be worth the trouble is dependent upon how many occurrences you have.

Terri|||Terri,

Thank you very much!sql

Flawed SQL Procedure

I am using the below procedure to set the field "Completed" to "True" in the table "Orders" only when the customer have paid and received or downloaded all his produts.

~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDURESetOrderToCompleted

(@.UserNameVARCHAR(50))

AS

UPDATEOrders

SETCompleted = 1

WHEREUserName = @.UserName

ANDCompleted = 0

~~~~~~~~~~~~~~~~~~~~~~~~~

Which is obviously flawed because I predict a situation where thesame customer ( user1 ) could havetwo different orders, like in the below example, when this procedurewill set incorrectly both fields "Completed" to "True" ( in tableOrders ) forOrderID = 1 and OrderID = 2 when actually thenot-downloadable product "gadget105" wasnot received yet by the customer (Received=False in table "OrderDetails" ).

Observations:

1)Downloadable products likesoftware have their field "Received" set toNULL because theydo not need to be shipped and therefore completing this field is irrelevant.

2) Both orders (OrderID = 1 and 2 ) were made by thesame customer withUserName = "user1".

3) The above procedure is only executed after all the downloadable products of the order have being downloaded by the customer.

Table OrderDetails

_______________________________________________________________

OrderID ProductID ProductName Downloadable Quantity Received UnitCost

1 10 software10 True 1 NULL 15.00

1 101 gadget101 False 1 True 20.00

2 12 software12 True 1 NULL 16.00

2 105 gadget105 False 1 False 22.00

2 13 software13 True 1 NULL 22.00

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Table Orders

_______________________________________________________________

OrderID UserName PaymentConfirmed Completed

1 user1 True False

2 user1 True False

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

How to solve the problem ?

You woul dprobably need to use the OrderId also in the WHERE clause.. so only the specific orders get "completed"|||

Hi ndinakar

But that is the problem, the procedure itself has to be capable to find out which OrderIDs must set the field Completed to True in the table .

|||

I can obtain the information that all downloadable items were downloaded by the client by verifing the field "RemoveRole" in theCustomerDownload table ( not shown here ) set to 'yes'.

Based on that, I devised this new procedure but since I am not good with "INNER JOINs", can somebody tell me if it is correct ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDURESetOrderToCompleted

(@.UserNameVARCHAR(50))

AS

UPDATEOrders

SETCompleted = 1

WHEREOrderID = (SELECT OrderID

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID

INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID

WHERE Orders.UserName = @.UserName

ANDCustomerDownload.RemoveRole='yes'

AND OrderDetails.Received = 1

AND Orders.Completed = 0)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In this procedure I aim to set the field Completed to True for all the customer's orders that satisfy the above conditions.

|||

If your subquery returs multiple records your query could fail. Perhaps you might want to do an IN instead of "=".

UPDATEOrders

SETCompleted = 1

WHEREOrderID IN (SELECT OrderID

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID

INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID

WHERE Orders.UserName = @.UserName

ANDCustomerDownload.RemoveRole='yes'

AND OrderDetails.Received = 1

AND Orders.Completed = 0)

Flat Files Containing Dates

Hi everyone.
I'm trying to use a Flat File Connector to read in a fixed field width file that contains some date columns.
The problem is that the date column is in a CCYYMMDD format (with no delimiters) so that todays date, as an example, would be 20050711.
When it attempts to import the file it fails due to a "Data Conversion Failed" error. I can't find any way to specify the format of the column in the FFC dialog so my only option appears to be read in the column as a string and transform it later.
Is that correct?
Steve
Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie|||Thanks Jamie, that's just what I was expecting.
Steve
|||

Jamie Thomson wrote:

Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie

Or you could also use a script component as a source. Again, it may be overkill!

-Jamie|||Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

|||Thanks for those replies guys.
I'd like to create a derived column transform programmatically using the SSIS object model. I can't find any help in BOL regarding this - but I've managed to get this so far, which creates the derived column transformation object (the dataFlow object is a MainPipe object created elsewhere):

DTSComponentMetaData90 DerivedColumn;
DerivedColumn = dataFlow.ComponentMetaDataCollection.New();
DerivedColumn.Name = "DateTransform";
DerivedColumn.ComponentClassID = "DTSTransform.DerivedColumn.1";
CManagedComponentWrapper instance = DerivedColumn.Instantiate();
instance.ProvideComponentProperties();
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

The problem I have now is that I don't know how to create new columns from old columns ( as I will need to do in my case ). I have used other components which have mapped the virtual columns from the input to the output, so I'm assuming it's something similar, but I can't get it to work.
I've even tried creating a transform in the BIDS and then opening the package in code to see what the object looks like, but some of the properties were read-only and must be set another way. I'm really stuck now so any help would be really appreciated.
Thanks.
Steve
|||Steve,

To create a new column from an existing column you need to add an output column to the derived column transform (InsertOutputColumAt) and then set the FriendlyExpression (or Expression) custom property on that column (SetOutputColumnProperty). The FriendlyExpression would be something like LEFT([oldcolname], 5) to take the left 5 chars of the [oldcolname] column (assuming the oldcolname column was a string or wstring). You could use the expression property but it isn't as obvious and you need to get the existing column's lineage id (e.g. LEFT(#27, 5) if 27 was oldcolname's lineageid). Additionally, you have to set the virtual input column's usage type (IDTSDesigntimeComponent90::SetUsageType) to read only to tell the dataflow that this component needs to use this column for reading.

HTH,|||I tried this but got following error:

Derived Column [2497]: An error occurred while attempting to perform a type cast.
thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com

|||

KirkHaselden wrote:

Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

To be more specific, I used the above idea and wrote this expression:
(DT_DATE)(SUBSTRING((YYYYMM + "01"),6,2) + "-" + SUBSTRING((YYYYMM + "01"),8,2) + "-" + SUBSTRING((YYYYMM + "01"),1,5))

This throws a cast exception.
Any suggestions?

thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com|||May be the cast error is due to the fact that input YYYYMM can be null or empty string. Can someone suggest a better expression? Or I have to write a script?|||

What do you mean when you say it "throws a cast exception"?

Have you tried entering this expression in the derived column UI to see if it gives an error message?

If you think the input column might be null or empty, you could check that with ISNULL() or LEN() calls first using a conditional operator.

sql

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

You can use the Microsoft Visual Basic .NET RTrim function in a script run from the Script Component (configured as a transformation), to remove white space characters such as line feed and carriage return characters.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

For information about the VB function, see "LTrim; RTrim; and Trim functions" at http://msdn2.microsoft.com/en-us/library/h9wz3dez(VS.71).aspx. For information about the Script Component, see "Extending the Data Flow with the Script Component" at http://msdn2.microsoft.com/en-us/library/ms136118.aspx.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

\n

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

If you enclose the escape character in quotes ("\n"), the expression will parse. For more information about using characters that require escape sequences in string literals, see "Literals (SSIS)" at http://msdn2.microsoft.com/en-us/library/ms141001.aspx.

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

You can use the Microsoft Visual Basic .NET RTrim function in a script run from the Script Component (configured as a transformation), to remove white space characters such as line feed and carriage return characters.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

For information about the VB function, see "LTrim; RTrim; and Trim functions" at http://msdn2.microsoft.com/en-us/library/h9wz3dez(VS.71).aspx. For information about the Script Component, see "Extending the Data Flow with the Script Component" at http://msdn2.microsoft.com/en-us/library/ms136118.aspx.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.

|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

\n

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

If you enclose the escape character in quotes ("\n"), the expression will parse. For more information about using characters that require escape sequences in string literals, see "Literals (SSIS)" at http://msdn2.microsoft.com/en-us/library/ms141001.aspx.

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

You can use the Microsoft Visual Basic .NET RTrim function in a script run from the Script Component (configured as a transformation), to remove white space characters such as line feed and carriage return characters.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

For information about the VB function, see "LTrim; RTrim; and Trim functions" at http://msdn2.microsoft.com/en-us/library/h9wz3dez(VS.71).aspx. For information about the Script Component, see "Extending the Data Flow with the Script Component" at http://msdn2.microsoft.com/en-us/library/ms136118.aspx.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

\n

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

If you enclose the escape character in quotes ("\n"), the expression will parse. For more information about using characters that require escape sequences in string literals, see "Literals (SSIS)" at http://msdn2.microsoft.com/en-us/library/ms141001.aspx.

Flat File to SQL table

I am looking to evaluate a text field in the row and change it to an integer in the sql table based on the text.

What is the best data flow tranformation for me to use inbetween the flat file source and the ole db destination?

it depends on what logic you are using for your evaluation but Derived Column will probably do it. If not, the script component.

-Jamie

|||Can you help with an example If then expression?|||

With the information you have provided, no. What evaluation do you want to do?

-Jamie

|||

Something like:

If [Treatment] = "No Deposit Required" then 1 else 0

I'm not sure how to write this in an expression.

|||

OK

[Treatment] == "No Deposit Required" ? (DT_I4)1 : (DT_I4)0

-Jamie

|||Thanks. your great..

Flat File Source - Add Output Field

I am moving data from a flat file source to a SQL Server table. But I want to add a columm that IS in the destination table, but NOT in the source file. Say the table column name is XXX in destination table, and there will be a global variable called @.[User::XXX] that remains constant throughout the package. I would like to put the variable value into the destination column, even though the source file does not contain the field. Is there an easy way to do this?Add a derived column transformation between your source and destination. Then simply drop the User:XXX variable into it. That will add a new column to the data flow.|||Thanks! This was the solution. I actually came across that while trying to solve a problem or recalculating and input field while passing through.

FK Position

Does it make any difference where we define the FKs in a table? I mean, do I speed up the query if I define it as the second field or the last one? What about the other fields, the ones that are not FKs, but are used as filters in a query?

Raul:

In general, none of this makes much practical difference to speed of execution. The two things that do matter are (1) do you have indexes on the foreign table in place that correspond to your foreign key and (2) does use of that index with a specific query also require a bookmark lookup for that specific query. If the foreign key has a correspondence to the clustered index of the other table -- and that is often the case -- then no bookmark lookup is necessary. If the foreign key has correspondence to a non-clustered index of the other table but there are fields in the other table that are referenced and are not part of the non-clustered index then a bookmark lookup will be necessary.

If a query references only a short list of records then the non-clustered index will often get used to optimize the query. However, if the query references a very large number of records in the foreign table the optimizer may "decide" that the cost of performing the random reads necessary to support bookmark lookups is too high. In these circumstances the optimizer will often opt to perform a table scan of the foreign table instead of of an index seek.

Bleah. Somebody please say this in a better way.

|||

Column position is virtually meaningless in defining PK's.

Column position is virtually menaingless except:

1. In UNIQUE and PRIMARY key constraints, and all indexes, order of columns has meaning.

2. In relatively rare cases, order of column conditions in a WHERE clause is involved (and only when the criteria is so large as to make it impossible for SQL Server to check all possible uses in a timely manner)

The position of a column in a table has no little if any bearing on performance, as it is just a representation of what is physically implemented in bits and bytes down in the physical table. If it were advantageous to reorganize the data on the page, the data could be reorganized by the storage engine without you knowing. So rest easy, it should make no difference at all.

First 5 of a each group

I have a field that has different entries. Is there a way I can pull the first 5 of each one?

I can get the count of each different one, I just don't see how to get the first 5 of each.

SELECT PostType, COUNT(*) AS NumberOfEntires
FROM ECNADetail GROUP BY PostType ORDER BY PostType

That returns

PostType

NumberOfEntires

1

4924

2

181

3

3621

4

695

How many distinct post types are there? You could do:

Select Top 5 * from ECNADetail Where PostType=1

Union

Select Top 5 * from ECNADetail Where PostType=2

Union

....

|||

Hi Tealc,

Try this script:

select * from ECNADetail as t
where (select count(*) from ECNADetail where PostType=t.PostType and entries>t.entries)<5 order by PostType,entries desc

|||

Your definition of first 5 entries of PostType is not clear.

The following solution is based a datetime column (if you have one)

For Sql Server 2005, you can try:

SELECT
*FROM(SELECT*, Row_Number()OVER(PartitionBy PostTypeOrderBy PostType, YourdatetimeColumnDESC)as seq
FROM
ECNADetail)t1
WHERE
 t1.seq<6
 
If you don't have the datetimecolumn, you can choose other column to decide the order to choose the top 5 for each group.

|||I ended up doing the union with each type selected as the top 5. This was a one time run thing.