finding a whole number

Question

Post reply

finding a whole number

Anamika

SSCertifiable

Points: 6202
More actions
April 15, 2009 at 9:43 pm

#202081

Hi all,
I would like to find whether a paritcular value is a whole number or not in SSIS. if it is so then i'll be throwing that to a error file.
how can i do this.
Early help is highly appreciated.
Thanks,
Regards,
Viji

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

Are you looking for the function to do this?

Eg

if a <> int(a) then ----> error file

or more detailed instructions? I'm assuming that you are not operating within SQL - where you would use something else, eg

declare @a real, @b-2 real, @C real

declare @aw varchar(5), @BW varchar(5), @cw varchar(5)

Set @a = 1

Set @b-2 = 1.00

Set @C = 1.0001

Set @aw = 'False'

Set @BW = 'False'

Set @cw = 'False'

If @a = floor(@a) Set @aw = 'True'

If @b-2 = floor(@b) Set @BW = 'True'

If @C = floor(@c) Set @cw = 'True'

Select @a 'Number', @aw 'Whole'

union all

Select @b-2, @BW

union all

Select @C, @cw

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lowell SSC Guru Points: 323518 More actions · Answer 2

i did it the same way as Phil, just too slow to post.... i compared the value to the floor of itself:

select

mynum,

case

when mynum = floor(mynum)

then 'WholeNumber'

else 'decimal'

end as IsWholeOrNot

from

(select 4.2 as mynum union all

select 5 as mynum union all

select 32.00 as mynum union all

select 32.01 as mynum

) x

--results

mynum IsWholeOrNot

4.20 decimal

5.00 WholeNumber

32.00 WholeNumber

32.01 decimal

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Anamika SSCertifiable Points: 6202 More actions · Answer 3

Thanks Phil and Lowell it works in SQL Function,

but i would like to know is there a way to do this without calling sql function in SSIS?

once again thanks for your suggestions.

regards

viji

Phil Parkin SSC Guru Points: 246961 More actions · Answer 4

Using what - VB.NET?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Anamika SSCertifiable Points: 6202 More actions · Answer 5

using any of the transformations or data conversions or mathematical functions in SSIS

Phil Parkin SSC Guru Points: 246961 More actions · Answer 6

Use the FLOOR function - it exists in SSIS as well:

if number = floor(number) then

--number is whole

else

--number is not whole

end if

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Anamika SSCertifiable Points: 6202 More actions · Answer 7

hi

sorry for the delayed reply

i'm checking like this in conditional split

FLOOR((DT_NUMERIC,7,2)[stock onhand qty]) != (DT_NUMERIC,7,2)[stock onhand qty]

but it redirects corrctly some times but not always.

some times it rounds up the value and send it to whole number out put.

how to resolve

thanks

regards

viji

Anamika SSCertifiable Points: 6202 More actions · Answer 8

especially if it is 8.01, 234.01 then it takes this as a whole number

if it is 8.06 then it is rounding it to 8.05 and sends to error output which is wrong.

please guide me in this

regards

viji

Phil Parkin SSC Guru Points: 246961 More actions · Answer 9

I added a derived column called 'Match' to a test transformation - expression as follows:

price == floor(price)

where price is a numeric(17,4) field and it worked perfectly, using the examples you provided (ie returns true for whole numbers, false otherwise)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Anamika SSCertifiable Points: 6202 More actions · Answer 10

the problem is there with the excel.

while coming from my excel source it is coming as 23.000000001 instead of 23.01 and sometimes it comes as 23 instead of 23.01. when we check with multiple files it behaves differently.

the user will down load a template or use his own file to upload the data. so i cannot enforce anything on excel file.

is there a way to correct this.

i'm using IMEX =1 in extented properties of the connection string so that i'll get text, date, numeric data.

waiting for the solution

viji