I'm trying to implement the equivalent of the following sybase query in
sqlite, but I suspect I've got it wrong. Any help much appreciated,
jim
update tmpRR_ML_final
set id = case when a.CDRefSNP <> 'NR' then b.id
when a.CDRefSNP = 'NR'
and a.CDRefMoody <> 'NR' then c.id
else 8
end
from TMP_credDerivOrig a,
crRefRatingMap b,
crRefRatingMap c,
tmpRR_ML_final e
where a.CDRefSNP *= b.rating
and a.CDRefMoody *= c.rating
and b.isPrimary = 2
and c.isPrimary = 3
and e.externalEquiv is null
and a.CDId = e.CDId
and a.CDSellBuy = e.CDSellBuy
My attempt at an sqlite "translation":
update tmpRR_ML_final
set id = ifnull(
(select case
when a.CDRefSNP <> 'NR' then b.id
when a.CDRefMoody <> 'NR' then c.id
else 8
end
from TMP_credDerivOrig a
LEFT JOIN crRefRatingMap b ON a.CDRefSNP = b.rating
LEFT JOIN crRefRatingMap c ON a.CDRefMoody = c.rating
where tmpRR_ML_final.CDId = a.CDId
and tmpRR_ML_final.CDSellBuy = a.CDSellBuy
and tmpRR_ML_final.externalEquiv is null
and b.isPrimary = 2
and c.isPrimary = 3),
id)
or, alternatively
update tmpRR_ML_final
set id =
(select case
when a.CDRefSNP <> 'NR' then b.id
when a.CDRefMoody <> 'NR' then c.id
else 8
end
from TMP_credDerivOrig a
LEFT JOIN crRefRatingMap b ON a.CDRefSNP = b.rating
LEFT JOIN crRefRatingMap c ON a.CDRefMoody = c.rating
where tmpRR_ML_final.CDId = a.CDId
and tmpRR_ML_final.CDSellBuy = a.CDSellBuy
and tmpRR_ML_final.externalEquiv is null
and b.isPrimary = 2
and c.isPrimary = 3)
where exists (
select 1
from TMP_credDerivOrig a
LEFT JOIN crRefRatingMap b ON a.CDRefSNP = b.rating
LEFT JOIN crRefRatingMap c ON a.CDRefMoody = c.rating
where tmpRR_ML_final.CDId = a.CDId
and tmpRR_ML_final.CDSellBuy = a.CDSellBuy
and tmpRR_ML_final.externalEquiv is null
and b.isPrimary = 2
and c.isPrimary = 3
);
--------------------------------------------------------
NOTICE: If received in error, please destroy and notify sender. Sender does not
intend to waive confidentiality or privilege. Use of this email is prohibited
when received in error.
