Re: Select not returning value some times

In article <13f746a9-2e6d-40ef-8179-cbf02669480c_at_e10g2000prf.googlegroups.com>, whatsin_at_xs4all.nl wrote:
>On 20 feb, 22:12, spamb..._at_milmac.com (Doug Miller) wrote:
>> In article <47bbefb1$0$14355$e4fe5..._at_news.xs4all.nl>, "shakespeare" <what=
>...._at_xs4all.nl> wrote:
>>
>> >"Doug Miller" <spamb..._at_milmac.com> schreef in bericht
>> >news:dpMuj.6977$Ru4.2760_at_newssvr19.news.prodigy.net...
>> >> In article <47bb32ec$0$14357$e4fe5..._at_news.xs4all.nl>, "shakespeare"
>> >> <what..._at_xs4all.nl> wrote:
>>
>> >>>"Doug Miller" <spamb..._at_milmac.com> schreef in bericht
>> >>>news:mrDuj.12400$R84.8493_at_newssvr25.news.prodigy.net...
>> >>>> In article <47ba9261$0$14352$e4fe5..._at_news.xs4all.nl>, "shakespeare"
>> >>>> <what..._at_xs4all.nl> wrote:
>>
>> >>>>><joshian..._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 proced=
>ure
>> >>>>>> 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:
>>
>> >>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>> >>>>>> /* Get the ISO Currency Code if Currency code came in the file is
>> >>>>>> number */
>> >>>>>> v_n_currcd :=3D vsd_currency; /* This is the numeric currency code =
>*/
>> >>>>>> BEGIN
>> >>>>>> SELECT ISO_CCY_CD into vsd_currency FROM CD_MAP_TBL WHERE CURRENCY_=
>CD
>> >>>>>> =3D v_n_currcd;
>> >>>>>> EXCEPTION
>> >>>>>> WHEN NO_DATA_FOUND THEN
>> >>>>>> vsd_currency :=3D v_n_currcd;
>> >>>>>> END;
>>
>> >>>> [snip]
>>
>> >>>>>Could you pleas explain this piece of code? First you assign
>> >>>>>v_n_currcd :=3D vsd_currency; =A0 /* This is the numeric currency cod=
>e */
>>
>> >>>>>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 co=
>de?
>>
>> >>>>>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
>> >>>>>> =3D v_n_currcd;
>>
>> >>>Yes, but they are equal....
>>
>> >> Look yet again.
>>
>> >> Specifically, look at which column in the table v_n_currcd is being com=
>pared
>> >> 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 colu=
>mn 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 =3D v_n_currcd" is comparing *anything* to=
> the
>> ISO code?
>
>NONE!
>It is SELECTING (aka GET) the iso code by looking up a row with the
>given numeric code. That's what I meant to say.
>

Thank you. Now please explain where the problem is.

-- 
Regards,
        Doug Miller (alphageek at milmac dot com)

It's time to throw all their damned tea in the harbor again.