Collation error

  • I get a 'collation conflict between "Latin_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS": in the equal to operation error.

    When I run this :

    DECLARE @DATE AS DATETIME

    DECLARE @DATA_DATE AS DATETIME

    DECLARE @PEOM_DATE AS DATETIME

    SET @DATE = '03/04/2008'

    SET @DATA_DATE = --@DATE

    (

    SELECT DISTINCT

    (CASEWHEN IS_HOLIDAY = 1 THEN BUSINESS_DAY_NEXT

    WHEN IS_BUSINESS_DAY = 1 THEN DATE

    WHEN IS_WEEKDAY = 0 THEN BUSINESS_DAY_NEXT

    END)

    FROM

    LPB_REPORTINGSERVICES.DBO.DIM_DATE

    WHERE

    DAY_IN_MONTH = 1

    AND MONTH(DATE) = MONTH(@DATE)

    AND YEAR(DATE) = YEAR(@DATE)

    )

    SET @PEOM_DATE = DATEADD(M,-1,@DATA_DATE)

    SET @PEOM_DATE =

    (

    SELECT DISTINCT

    (CASEWHEN IS_HOLIDAY = 1 THEN BUSINESS_DAY_NEXT

    WHEN IS_BUSINESS_DAY = 1 THEN DATE

    WHEN IS_WEEKDAY = 0 THEN BUSINESS_DAY_NEXT

    END)

    FROM

    LPB_REPORTINGSERVICES.DBO.DIM_DATE

    WHERE

    DAY_IN_MONTH = 1

    AND MONTH(DATE) = MONTH(@PEOM_DATE)

    AND YEAR(DATE) = YEAR(@PEOM_DATE)

    )

    --SELECT @DATE, @DATA_DATE,DATEADD(M,-1,@DATA_DATE),@PEOM_DATE

    ------------------------------------------------------

    create table ##Cash_Flow1

    (

    loannumbernvarchar(10),

    PmtTransDatedatetime,

    pmtprinamtmoney,

    pmtsuspenseamtmoney,

    pmtintamtmoney,

    pmtescrowamtmoney,

    PmtFeeAmountmoney,

    pmtprinamt_1money,

    PmtTotalAmtmoney

    )

    insert into ##Cash_Flow1

    select distinct

    loannumber,

    pmttransdate,

    sum((casewhen pmtprinamt < 0 then 0 else pmtprinamt end)) as 'Principal Pmt',
    sum(pmtsuspenseamt) as 'Pmt Suspense',
    sum(pmtintamt) as 'Interest Pmt',
    sum(pmtescrowamt) as 'Escrow Pmt',
    sum(PmtFeeAmount) as 'Pmt Fee Amount',
    sum(pmtprinamt) as 'PPA 1',
    sum(PmtTotalAmt) as 'Pmt Total Amount'

    from
    lpb_loanservicing.dbo.PaymentTransaction pt

    where
    pmttranscode in (171,172,173,174,175,181,182)
    and
    pmttransdate = (select max(pmttransdate) from lpb_loanservicing.dbo.PaymentTransaction where loannumber = pt.loannumber and pmttransdate <= @DATA_DATE and pmttranscode in (171,172,173,174,175,181,182)) group by
    loannumber,
    pmttransdate

    create table ##Final_CashFlow1
    (
    loan nvarchar(10),
    PmtTransDatedatetime,
    pmtprinamtmoney,
    pmtintamtmoney,
    pmtescrowamtmoney,
    PmtFeeAmountmoney,
    pmtprinamt_1money,
    PmtTotalAmtmoney,
    pmtsuspenseamtmoney,
    Cal_TotalPmtAmt money,
    Less_Than_MPvarchar(10),
    Greater_Than_30 varchar(10)
    )
    insert into ##Final_CashFlow1

    select distinct
    l.loan,
    PmtTransDate,
    pmtprinamt,
    pmtintamt,
    pmtescrowamt,
    PmtFeeAmount,
    pmtprinamt_1,
    PmtTotalAmt,
    pmtsuspenseamt,
    isnull(pmtprinamt,0) + isnull(pmtintamt,0) +
    isnull((casewhen isnull(pmtprinamt_1,0) > 0 then 0

    else pmtprinamt_1

    end),0),

    (casewhen

    isnull(pmtprinamt,0) + isnull(pmtintamt,0) +

    isnull((casewhen isnull(pmtprinamt_1,0) > 0 then 0

    else pmtprinamt_1

    end),0) < (ISNULL(TotalMnthlyPmt,0) - ISNULL(TAndIMnthlyAmt,0)) then 'Review'
    else ''
    end) as 'Less Than Min Payment',
    (casewhen
    isnull(pmtprinamt,0) + isnull(pmtintamt,0) +
    isnull((casewhen isnull(pmtprinamt_1,0) > 0 then 0

    else pmtprinamt_1

    end),0) > FULLAMORTIZEDPIAMT and FULLAMORTIZEDPIAMT <> 0 then 'Review'

    else ''

    end) as 'Greater than 30'

    from

    lpb_loanservicing.dbo.loandata l

    inner join ##Cash_Flow1 cf

    on l.loan = cf.loannumber

    where

    zone not in ('W9','WI','W8')

    and

    investor not in ('J61', 'L60', 'S61', 'D81', 'R81', 'U81', 'W81','K61','W35','V81','R61')

    AND

    isnull(payoffeffdate,'1900-01-01 00:00:00.000') = '1900-01-01 00:00:00.000'

    AND

    CONVERT(CHAR(10),DATA_DATE,101) = CONVERT(CHAR(10),@DATA_DATE,101)

    and

    principalbalance > 0

    and

    category not in ('012')

    DECLARE @BALANCES TABLE

    (

    LOAN VARCHAR(10),

    PRINCIPALBALANCE FLOAT

    )

    INSERT INTO @BALANCES

    SELECT DISTINCT

    LOAN,

    PRINCIPALBALANCE

    FROM

    LPB_LOANSERVICING.DBO.LOANDATA

    WHERE

    CONVERT(CHAR(10),DATA_DATE,101) = @PEOM_DATE --CONVERT(CHAR(10),DATEADD(M,-1,@DATA_DATE),101)

    AND CATEGORY IN ('001','011','005','014','015','016','017')

    AND ZONE NOT IN ('WI','W9')

    ANDINVESTOR NOT IN ('J61', 'L60', 'S61', 'D81', 'R81', 'U81', 'W81','K61','W35','V81','R61')

    SELECT DISTINCT

    @DATA_DATE AS 'DATA DATE',

    L.LOAN,

    L.PRINCIPALBALANCE 'Current EOM BALANCE',

    B.PRINCIPALBALANCE as 'Previous EOM BALANCE',

    OTHERFUNDSDUE + ACCRLCAMOUNT AS 'FEES',

    ACCRUEDINTEREST,

    ISNULL(TotalMnthlyPmt,0) - ISNULL(TAndIMnthlyAmt,0) AS 'Minimum Payment',

    LOANTERM,

    NEXTDUEDATE as 'Next Due Date',

    lastfullpmtdate as 'Last Full PMT Date',

    PmtTransDate as 'Pmt Trans Date',

    pmtprinamt as 'Pmt Prin Amt',

    pmtintamt as 'Pmt Int Amt',

    pmtescrowamt as 'Pmt Escrow Amt',

    PmtFeeAmount as 'Pmt Fee Amt',

    pmtprinamt_1 as 'Pmt Prin Amt 1',

    PmtTotalAmt as 'Pmt Total Amt',

    pmtsuspenseamt as 'Pmt Suspsense Amt',

    Cal_TotalPmtAmt as 'Cal Total Pmt Amt',

    (CASE

    WHEN INVESTOR = 'T61' AND CATEGORY = '001' THEN 'HELOC PURCHASED'

    WHEN INVESTOR = 'T61' AND CATEGORY = '011' AND BRANCHOFFICECODE IN ('0120') AND PRODUCTLINECODE = '601'THEN 'HELOC PURCHASED'

    WHEN INVESTOR = 'T61' AND CATEGORY = '002' THEN 'CHOICE'

    WHEN INVESTOR = 'T61' AND CATEGORY = '003' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'T61' AND CATEGORY = '004' THEN 'FIXED'

    WHEN INVESTOR = 'T61' AND CATEGORY = '005' THEN 'FIXED RATE SECONDS'

    WHEN INVESTOR = 'T61' AND CATEGORY = '006' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'T61' AND CATEGORY = '007' THEN 'FIXED'

    WHEN INVESTOR = 'T61' AND CATEGORY = '008' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'T61' AND CATEGORY = '009' AND BRANCHOFFICECODE IN ('27','28','29','39','44') THEN 'BAC'

    WHEN INVESTOR = 'T61' AND CATEGORY = '009' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'T61' AND CATEGORY = '010' THEN 'FIXED'

    WHEN INVESTOR = 'T61' AND CATEGORY = '011' AND BRANCHOFFICECODE IN ('01','001','0001','1') THEN 'HELOC RETAIL'

    WHEN INVESTOR = 'T61' AND CATEGORY = '011' AND BRANCHOFFICECODE IN ('0120') AND PRODUCTLINECODE = '600' THEN 'HELOC RETAIL'

    WHEN INVESTOR = 'T61' AND CATEGORY = '011' AND BRANCHOFFICECODE IN ('02','002','0002','14','2') THEN

    (CASEWHEN PROPERTYSTATE = 'CA' THEN 'CALIFORNIA - WHOLESALE HELOC'

    WHEN PROPERTYSTATE = 'FL' THEN 'FLORIDA - WHOLESALE HELOC'

    WHEN PROPERTYSTATE = 'AZ' THEN 'ARIZONA - WHOLESALE HELOC'

    ELSE 'ALL OTHER STATES - WHOLESALE HELOC'

    END)

    WHEN INVESTOR = 'T61' AND CATEGORY = '011' AND BRANCHOFFICECODE IN ('0120') AND PRODUCTLINECODE = '603' THEN

    (CASEWHEN PROPERTYSTATE = 'CA' THEN 'CALIFORNIA - WHOLESALE HELOC'

    WHEN PROPERTYSTATE = 'FL' THEN 'FLORIDA - WHOLESALE HELOC'

    WHEN PROPERTYSTATE = 'AZ' THEN 'ARIZONA - WHOLESALE HELOC'

    ELSE 'ALL OTHER STATES - WHOLESALE HELOC'

    END)

    WHEN INVESTOR = 'T61' AND CATEGORY = '014' THEN 'HELOC FIRST'

    WHEN INVESTOR = 'T61' AND CATEGORY = '015' THEN 'HELOC FIRST'

    WHEN INVESTOR = 'T61' AND CATEGORY = '016' THEN 'HELOC FIRST'

    WHEN INVESTOR = 'T61' AND CATEGORY = '017' THEN 'HELOC FIRST'

    WHEN INVESTOR = 'V61' AND CATEGORY = '002' THEN 'CHOICE OTHER'

    WHEN INVESTOR = 'V61' AND CATEGORY = '006' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'W61' AND CATEGORY = '002' THEN 'CHOICE OTHER'

    WHEN INVESTOR = 'W61' AND CATEGORY = '006' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'X61' AND CATEGORY = '006' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'X61' AND CATEGORY = '008' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'Y60' AND CATEGORY = '001' THEN 'AUTO'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '002' THEN 'CHOICE OTHER'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '003' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '004' THEN 'FIXED'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '005' THEN 'FIXED RATE SECONDS'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '006' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '007' THEN 'FIXED'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '008' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '009' THEN 'TRADITIONAL'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '010' THEN 'FIXED'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '011' THEN 'HELOC SECOND'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '014' THEN 'HELOC FIRST'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '015' THEN 'HELOC FIRST'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '016' THEN 'HELOC FIRST'

    WHEN INVESTOR = 'Z61' AND CATEGORY = '017' THEN 'HELOC FIRST'

    END) AS 'GROUPING ACCOUNTING',

    discountamount,

    firstsrvfeerate,

    firstunamortizedfeeamt,

    firstfasbfee1,

    firstfasbfee2,

    firstfasbfee3,

    firstfasbfee4,

    ANNUALINTRATE AS 'ANNUALINTRATE',

    (casewhen (isnull(L.PRINCIPALBALANCE,0) - (isnull(B.PRINCIPALBALANCE,0) - isnull(pmtprinamt,0))) > 0 then (isnull(L.PRINCIPALBALANCE,0) - (isnull(B.PRINCIPALBALANCE,0) - isnull(pmtprinamt,0))) end) as 'Draws',

    (CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN

    (CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) <= 4 THEN L.PRINCIPALBALANCE
    else 0 END)
    else 0
    END) 'UP TO DATE BALANCE',
    (CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
    (CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 5 AND 29 THEN L.PRINCIPALBALANCE
    else 0 END)
    else 0
    END) 'Cycle 1 Balances' ,
    (CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
    (CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 30 AND 59 THEN L.PRINCIPALBALANCE
    else 0 END)
    else 0
    END) 'Cycle 2 Balances',
    (CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
    (CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 60 AND 89 THEN L.PRINCIPALBALANCE
    else 0 END)
    else 0
    END) 'Cycle 3 Balances',
    (CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
    (CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 90 AND 119 THEN L.PRINCIPALBALANCE
    else 0 END)
    else 0
    END) 'Cycle 4 Balances',
    (CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
    (CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 120 AND 149 THEN L.PRINCIPALBALANCE
    else 0 END)
    else 0
    END) 'Cycle 5 Balances',
    (CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
    (CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 150 AND 179 THEN L.PRINCIPALBALANCE
    else 0 END)
    else 0
    END) 'Cycle 6 Balances',
    (CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
    (CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) >= 180 THEN L.PRINCIPALBALANCE

    else 0 END)

    else 0

    END) 'Chargeoff balances',

    (CASEWHEN DATEDIFF(DD,@DATA_DATE,NEXTDUEDATE) > 0 THEN 0

    ELSE DATEDIFF(DD,@DATA_DATE,NEXTDUEDATE)

    END) AS 'DAYS PAST DUE',

    (CASEWHEN PAYOFFEFFDATE <> '01/01/1900' THEN PAYOFFEFFDATE END) PAYOFFEFFDATE

    FROM

    LPB_LOANSERVICING.DBO.LOANDATA L

    LEFT JOIN @BALANCES B

    ON L.LOAN = B.LOAN

    LEFT JOIN ##Final_CashFlow1 FC

    ON L.LOAN = FC.LOAN

    WHERE

    convert(char(10),DATA_DATE,101) = @DATA_DATE

    AND CATEGORY IN ('001','011','005','014','015','016','017')

    AND ZONE NOT IN ('WI','W9')

    ANDINVESTOR NOT IN ('J61', 'L60', 'S61', 'D81', 'R81', 'U81', 'W81','K61','W35','V81','R61')

    ORDER BY 1

    drop table ##Cash_Flow1

    drop table ##Final_CashFlow1

  • Without the DDL (including the collation for character fields) a little hard to determine where the problem is with the code.

Viewing 2 posts - 1 through 1 (of 1 total)

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