Re: Select not returning value some times

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 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?- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

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. Received on Thu Feb 21 2008 - 06:24:36 CST