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