Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Question Unanswered: union changing column results?

    I have a problem with a union all that seems to be changing the results of a column based on using the union, when I run the querys separatly I get the expected results in the field!

    Basically 3 queries pulled together for reporting purposes.

    Orders (which can include a gift so the gifts are separated in the second query).
    OrderGifts (pulled out of the orders).
    Gifts (straight gifts with no associated orders).

    The problem is showing up in the first ORDERS query. I can pull a specific order and the calculation for the AMOUNT(eg:$15.20) is correct, but when I combine the tables I get a different AMOUNT(eg:$15.199999999999999).

    Here are the values for the fields in the amount calc:
    totitems=12.9500
    totcredits=.0000
    discount=.0000
    tax=.0000
    refund=.0000
    convrate=1.0
    postage=2.2500
    giftamt=15.0000


    I tried converting each of the fields to an decimal befre the calc and same results

    I tried ROUND and same thing!

    I have been trying to chase this down all day, cant figure out what the problem is or how to get around it...whats going on here I am missing?


    Here is the query, if you need any other info just let me know, I would GREATLY appreciate ANY direction of figuring this out!


    select * from
    (
    --orders
    select
    acctnbr as PartnerId,
    'O' as TrnType,
    ordnbr as TrnId,
    0 as FundId,
    odate as WorkDate,
    adate as PostDate,
    sdate as ShipDate,
    cast(
    (
    IsNull(totitems, 0) -
    IsNull(totcredits, 0) -
    IsNull(discount, 0) -
    IsNull(tax, 0) -
    IsNull(refund, 0) +
    IsNull(postage, 0)
    ) * IsNull(convrate, 1)
    as Decimal(10,2))
    as Amount,
    batchnbr as BatchId,
    motvcode as MotivationCode,
    currcode as CurrencyType,
    Status,
    paycode as PaymentType,
    paytype as CardType,
    paynbr as CardNbr,
    expire as CardExpire,
    ChargeAuth,
    ConvRate,
    MediaCode,
    hcf.dbo.KmaiProjMotvTest(0,motvcode) as ProjMotvName
    from kmai.dbo.o01omst

    UNION all

    --orders gifts
    select
    acctnbr as PartnerId,
    'G' as TrnType,
    giftref as TrnId,
    giftfundid as FundId,
    odate as WorkDate,
    null as PostDate,
    null as ShipDate,
    ROUND((giftamt * IsNull(convrate, 1)),2) as Amount,
    batchnbr as BatchId,
    giftmotvcode as MotivationCode,
    currcode as CurrencyType,
    Status,
    paycode as PaymentType,
    paytype as CardType,
    paynbr as CardNbr,
    expire as CardExpire,
    ChargeAuth,
    ConvRate,
    MediaCode,
    hcf.dbo.KmaiProjMotvTest(giftfundid,giftmotvcode) as ProjMotvName
    from kmai.dbo.o01omst
    where adate is null
    and giftfundid <> ''

    UNION ALL
    --gifts
    select
    acctnbr as PartnerId,
    'G' as TrnType,
    gh.giftref as TrnId,
    convert(int, fundid) as FundId,
    gdate as WorkDate,
    applydate as PostDate,
    null as ShipDate,
    ROUND((IsNull(gd.amt, 0) * IsNull(convrate, 1)),2) AS Amount,
    batchnbr as BatchId,
    motvcode as MotivationCode,
    currcode as CurrencyType,
    Status,
    paycode as PaymentType,
    paytype as CardType,
    paynbr as CardNbr,
    expire as CardExpire,
    ChargeAuth,
    ConvRate,
    MediaCode,
    hcf.dbo.KmaiProjMotvTest(fundid,motvcode) as ProjMotvName
    from kmai.dbo.g03ghdr gh
    inner join kmai..g04gdtl gd
    on gh.giftref = gd.giftref

    )
    UnionOfGiftsAndOrders

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would try casting each of the amounts across the unions to deciaml(10,2)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Thumbs up Thanks!

    That was it, I cast all the results to money and it workes exactly as expected!

    I understand why this works (all the same results now), but I still dont understand why it was coming up with the goofy result!

    But thanks a million a solution to the problem!

    -R

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It will have to accomodate each of the data sizes...

    So if you had a column in each of the unions that is , CHAR(10), CHAR(20), and CHAR(1000)...it will need to make the rs each CHAR(1000)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Smile Got it! Thanks!

    So it was truncating the smaller value, thats why it was intermittent...Thanks again for the explanation!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •