Re: Select not returning value some times

<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;
> ==========================================================
>
> 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 Received on Tue Feb 19 2008 - 02:25:02 CST