Performing Division Operation and inserting into money datatype Expression RRS feed

  • Question

  • hi all,

    i have been struggling very hard for this with extensive searching and so on. I finally gave up and here i am posting the query.

    I have a column known as CashChange from csv which i need to divide by hundred and insert into the table which is of money data type. This i am doing in the SSIS Derived transform.

    Here is the expressions I have tried but to know avail. Could someone please tell me how to cast expression as decimal and then insert into db. Do i need ot use any data conversion Data flow task?

    LEN(LTRIM(RTRIM(CashChange))) == (DT_DECIMAL,2)0 ? 0 : (DT_DECIMAL,2)((DT_I8)CashChange / 100)

    LEN(LTRIM(RTRIM(CashChange))) == (DT_CY)0 ? 0 : (DT_CY)((DT_DECIMAL,2)CashChange / 100)

    LEN(LTRIM(RTRIM(CashChange))) == (DT_DECIMAL,2)0 ? 0 : (DT_DECIMAL,2)((DT_CY)CashChange / 100)

    With any of the above I am still ending up only 00. i.e. if the input is 12345 I am getting values as 12345.00 and not 123.45 which is expected. 

    Any help please? 

    Thursday, June 3, 2010 5:04 PM

All replies

  • The problem is that you're casting the result of your division to a decimal, but the calculation is being done using integer math.  Try this:

    (DT_DECIMAL, 2)((DT_I4)CashChange / 100.0)

    The above will convert CashChange to an integer - which is what SSIS was implicitly doing for you (you must be using 2005, because I don't think 2008 will do that for you anymore).  The division operation will result in a numeric or float with tons of decimal places, which you then explicitly convert to your required type.

    (I'm not too sure why you're testing CashChange for zero - there isn't a potential for a division by zero error when you're specifying a divisor of 100.)

    Todd McDermid's Blog Talk to me now on
    Thursday, June 3, 2010 5:22 PM
  • I am still finding the same values coming. 12345.00 :( . we use ssis 2008.
    Thursday, June 3, 2010 5:36 PM
  • What data type is CashChange, and can you provide some sample values?
    Todd McDermid's Blog Talk to me now on
    Thursday, June 3, 2010 6:00 PM
  • well as I said, The value for CashChange is coming from CSV as a whole number i.e. 12345 . The data type for the column is Money.
    Thursday, June 3, 2010 6:06 PM
  • Unfortunately, that can't be the case.  There's no SSIS data type of "money".  Do you mean the data type for the column is DT_CURRENCY?  I am referring to the data type of the column "CashChange" as it exits the Flat File Source - you can double-click on the green arrow exiting the Flat File source to check this.
    Todd McDermid's Blog Talk to me now on
    Thursday, June 3, 2010 6:14 PM
  • Ok. Currently it is displayed as DT_STR. Well our requirement is to get the values in whole numbers and then divide by 100 and stored it as money in the database.

    There are around 50 other columns based on teh same computation. I haven't yet modified this column type but is there no other way through which i can accomplish this task like conversion/type casting or something?

    Thursday, June 3, 2010 6:45 PM
  • just bumping the thread..anybody. please help :(
    Thursday, June 3, 2010 7:16 PM
  • I have tested your scenario and the following expression works for me:

    (DT_DECIMAL,2)((DT_I4)Column / 100.0)

    Unless you are replacing the value in an existing column, that is.  Are you doing that, or are you creating a new column in your Derived Column?

    Todd McDermid's Blog Talk to me now on
    Thursday, June 3, 2010 8:54 PM
  • Well, actually I am replacing the existing column. Should I be creating a new column then? I don't understand. Eitherways, should it not work? But thanks a lot , i will give it a try and let me see. Fingers fully crossed.

    Friday, June 4, 2010 3:16 AM
  • It makes no sense to "cast" or "convert" something... and not really do that by stuffing it back into the same data type.  Yes, create a new column of the correct type - although even if you didn't, it should still function.
    Todd McDermid's Blog Talk to me now on
    Friday, June 4, 2010 3:53 AM