Re: Select not returning value some times
From: shakespeare <whatsin_at_xs4all.nl>
Date: Sun, 24 Feb 2008 10:40:13 +0100
Message-ID: <47c13b89$0$14358$e4fe514c@news.xs4all.nl>
>>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.
>>
Date: Sun, 24 Feb 2008 10:40:13 +0100
Message-ID: <47c13b89$0$14358$e4fe514c@news.xs4all.nl>
"Doug Miller" <spambait_at_milmac.com> schreef in bericht news:J3Mvj.2532$fX7.1157_at_nlpi061.nbdc.sbc.com...
> 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.
The problem was in my misreading, though caused by the (re)assingment of
numeric and isocodes. Apologies for that. Thank you for your patience and
guideance.
The actual cause of getting numeric values back from this code is that when
no exact match is found in the table, the original code will be returned
without any indication that no value was found, which could be caused by
differences in length (leading/trailing spaces), lower/upercase or
misspelled codes, or missing values.
Shakespeare Received on Sun Feb 24 2008 - 03:40:13 CST