Re: Select not returning value some times

In article <47bbefb1$0$14355$e4fe514c_at_news.xs4all.nl>, "shakespeare" <whatsin_at_xs4all.nl> wrote:
>
>"Doug Miller" <spambait_at_milmac.com> schreef in bericht
>news:dpMuj.6977$Ru4.2760_at_newssvr19.news.prodigy.net...
>> In article <47bb32ec$0$14357$e4fe514c_at_news.xs4all.nl>, "shakespeare"
>> <whatsin_at_xs4all.nl> wrote:
>>>
>>>"Doug Miller" <spambait_at_milmac.com> schreef in bericht
>>>news:mrDuj.12400$R84.8493_at_newssvr25.news.prodigy.net...
>>>> In article <47ba9261$0$14352$e4fe514c_at_news.xs4all.nl>, "shakespeare"
>>>> <whatsin_at_xs4all.nl> wrote:
>>>>>
>>>>><joshianant_at_gmail.com> schreef in bericht
>>>>>news:413d4cbe-4ea0-42da-9d1e-665db1d0e666_at_s8g2000prg.googlegroups.com...
>>>>>> Hi,
>>>>>>
>>>>>> We have a stored procedure in which we replace numeric internal
>>>>>> currency code to ISO currency code using a lookup table. The procedure
>>>>>> handles about 5K records and for few of them the currency remains
>>>>>> numeric when inserted to target table. This causes exceptions in
>>>>>> downstream system as it expects ISO codes.
>>>>>>
>>>>>> Here's section of the code:
>>>>>>
>>>>>> =======================================
>>>>>> /* Get the ISO Currency Code if Currency code came in the file is
>>>>>> number */
>>>>>> v_n_currcd := vsd_currency; /* This is the numeric currency code */
>>>>>> BEGIN
>>>>>> SELECT ISO_CCY_CD into vsd_currency FROM CD_MAP_TBL WHERE CURRENCY_CD
>>>>>> = v_n_currcd;
>>>>>> EXCEPTION
>>>>>> WHEN NO_DATA_FOUND THEN
>>>>>> vsd_currency := v_n_currcd;
>>>>>> END;
>>>>
>>>> [snip]
>>>>>
>>>>>Could you pleas explain this piece of code? First you assign
>>>>>v_n_currcd := vsd_currency; /* This is the numeric currency code */
>>>>>
>>>>>So both values are now the same, and appearantly either vsd_currrency
>>>>>was
>>>>>holding the numeric value, or the numeric value is assigned an ISO code?
>>>>>
>>>>>Then you try to find a record with the isocode equal to vsd_currency,
>>>>
>>>> That is incorrect; examine the SELECT again:
>>>>
>>>>>> SELECT ISO_CCY_CD into vsd_currency FROM CD_MAP_TBL WHERE CURRENCY_CD
>>>>>> = v_n_currcd;
>>>
>>>Yes, but they are equal....
>>
>> Look yet again.
>>
>> Specifically, look at which column in the table v_n_currcd is being compared
>> to. Hint: it's *not* the one with the ISO code.
>>
>>>Still it makes no sense.
>>
>> Makes sense once you realize that he's *not* comparing against the column you
>> seem to think he is.
>>
>
>Ok, it should read: get the iso code corresponding to the numeric code. I
>stand corrected.

What part of "WHERE CURRENCY_CD = v_n_currcd" is comparing *anything* to the ISO code? Received on Wed Feb 20 2008 - 15:12:06 CST