mercredi 13 mai 2015

How to reduce code redundancy in this query?

I've got these two calculated fields in my query:

CASE
    WHEN TRN.TOTAL_VALUE_T = 0 THEN '00000000000'
    ELSE
        CASE
            WHEN CC.PORT_CURRENCY1 = 'CAD' THEN RIGHT('00000000000' + REPLACE(LTRIM(STR(TRN.TOTAL_VALUE_T + TRN.TAXES, 11, 2)), '.', ''), 11) 
            WHEN CC.PORT_CURRENCY1 = 'USD' THEN RIGHT('00000000000' + REPLACE(LTRIM(STR((TRN.TOTAL_VALUE_T + TRN.TAXES) * DBO.fnGetExchangeRate(TRN.TRADE_DATE, 'USD', 'CAD'), 11, 2)), '.', ''), 11)
    END
END AS TX_PRIX_CAD,

CASE 
    WHEN T.TRANS_TYPE in ('ADD','DELIV') THEN
        CASE
            WHEN (SELECT currencyTitle FROM DBO.GetPrice(TRN.SEC_NO, TRN.TRADE_DATE)) = '' THEN TX_PRIX_CAD 
    END
END AS TX_MNT_BRUT

You see, the second field (TX_MNT_BRUT) must equal the first one (TX_PRIX_CAD) under certain conditions.

Problem when doing this is SQL is telling me that TX_PRIX_CAD is not a valid column name, I guess because the column name does not exist physically in the table.

How could I achieve this without repeating the code in TX_PRIX_CAD in TX_MNT_BRUT ?

Thanks.

Aucun commentaire:

Enregistrer un commentaire