Re: Select not returning value some times
From: shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 20 Feb 2008 10:15:23 +0100
Message-ID: <47bbefb1$0$14355$e4fe514c@news.xs4all.nl>
>>
>>"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....
>>Still it makes no sense.
Date: Wed, 20 Feb 2008 10:15:23 +0100
Message-ID: <47bbefb1$0$14355$e4fe514c@news.xs4all.nl>
"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. > > > > > -- > Regards, > Doug Miller (alphageek at milmac dot com) > > It's time to throw all their damned tea in the harbor again.
Ok, it should read: get the iso code corresponding to the numeric code. I stand corrected.
Shamespeare Received on Wed Feb 20 2008 - 03:15:23 CST