Re: Select not returning value some times

On Feb 19, 3:25 am, "shakespeare" <what..._at_xs4all.nl> wrote:
> <joshian..._at_gmail.com> schreef in berichtnews: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;
> > ==========================================================
>
> > We were unable to duplicate this issue in test environment even while
> > processing 100K records.
>
> > Table CD_MAP_TBL is defined as:
>
> > SQL> desc CD_MAP_TBL;
> > Name Null? Type
> > ----------------------------------------- --------
> > ----------------------------
> > CURRENCY_CD NOT NULL VARCHAR2(3)
> > EFFDT NOT NULL DATE
> > EFF_STATUS NOT NULL VARCHAR2(1)
> > ISO_CCY_CD NOT NULL VARCHAR2(3)
> > LOAD_DTTM DATE
> > LASTUPDDTTM DATE
> > ERROR_FLAG NOT NULL VARCHAR2(1)
>
> > Variables are declared as:
>
> > v_n_currcd VARCHAR2(3);
> > vsd_currency ST_DET.CURRENCY%Type; (This is VARCHAR2(30))
>
> > Any ideas ?
>
> > Thanks in advance
>
> 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, which
> leads to the conclusion that vsd_currency WAS holding the iso-code, and you
> HOPE to find a corresponding numeric value. If you don't find it (so no
> values have changed in the select) you assign
> vsd_currency := v_n_currcd;
> but both values were already the same...... furthermore, you assign a
> varchar2(30) to a varchar2(3)..... but fortunately it will never be longer
> than 3, because of the previous assignment.
>
> I don't get it.
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

vsd_currency holds numeric currency code obtained from the source table.

With the select statement, it is expected that ISO currency code (column ISO_CCY_CD) gets overwritten
on previous numeric value of vsd_currency.

"WHERE CURRENCY_CD = v_n_currcd;" will fetch a row that has numeric CURRENCY_CD equal to value of "v_n_currcd"

The numeric code is never more than 3 characters even though the variable is varchar(30).

Thanks for taking time. Received on Tue Feb 19 2008 - 08:38:33 CST