Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: UPDATE problem & temporary tables

    Hi everyone, I have had some problems with the use of the temporary table and the update statement.

    I created a temporary table and I inserted data without problems, but when I try to update a field in the table I get the message:

    Server: Msg 446, Level 16, State 9, Line 45
    Cannot resolve collation conflict for equal to operation.

    This is the source code:

    create table #tmp2003(
    atc_iata_num varchar(30) ,
    ranal2002 int,
    pronal2002 money,
    pro2003 money,
    pro2002 money)

    insert into #tmp2003 (atc_iata_num, pronal2002, pro2003, pro2002)
    select p.atc_iata_num,
    (select sum(tk2003)
    from prod2002
    where prod2002.atc_iata_num = p.atc_iata_num
    and prod2002.chkIn_date >= @FechaUno02
    and prod2002.ChkIn_date < @FechaDos02
    and prod2002.rental_country_desc = 'MX'
    ),
    sum(p.us_dollar_amt_due),
    (
    select sum(tk2003)
    from prod2002
    where prod2002.atc_iata_num = p.atc_iata_num
    and prod2002.chkIn_date >= @FechaUno02
    and prod2002.ChkIn_date < @FechaDos02
    )
    from prod2003 as p
    where p.chkIn_date >= @FechaUno03
    and p.ChkIn_date < @FechaDos03
    and p.Booking_date >= @FechaReserva
    group by p.atc_iata_num

    /**** HERE IS THE PROBLEM *****/
    /* in this line a get the message error */
    update t
    set ranal2002 =
    (
    select count(*)
    from prod2002
    where prod2002.atc_iata_num = t.atc_iata_num

    )
    from #tmp2003 t

    Thank you for any kind of help.
    Regards.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Check collation for main table and set the same collation for temporary table (collation are not equal for these tables).

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Yeah, look especially for these two fields: prod2002.atc_iata_num = p.atc_iata_num

    I solution could be to cast one of the two VARCHAR(30) to the collation order of the other field.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    just add the COLLATE statement:

    update t
    set ranal2002 =
    (
    select count(*)
    from prod2002
    where prod2002.atc_iata_num = t.atc_iata_num COLLATE Hungarian_BIN

    )
    from #tmp2003 t

    It really does not matter what collation you specify at the time of comparison if the values are identical, because = will be evaluated to true.

  5. #5
    Join Date
    Sep 2003
    Posts
    2

    Thumbs up

    Thank you everyone by your quick answer, your solutions have worked perfectly.

    I will go deeper in this matter.

    thank you everyone again.


    Regards.

Posting Permissions

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