 | |
Select not returning value some times
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#61
 
| |||
| |||
|
Re: Select not returning value some times -
02-22-2008
, 09:05 PM
|
On 20 feb, 22:12, spamb... (AT) milmac (DOT) com (Doug Miller) wrote: In article <47bbefb1$0$14355$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" <what= .... (AT) xs4all (DOT) nl> wrote: "Doug Miller" <spamb... (AT) milmac (DOT) com> schreef in bericht news:dpMuj.6977$Ru4.2760 (AT) newssvr19 (DOT) news.prodigy.net... In article <47bb32ec$0$14357$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what... (AT) xs4all (DOT) nl> wrote: "Doug Miller" <spamb... (AT) milmac (DOT) com> schreef in bericht news:mrDuj.12400$R84.8493 (AT) newssvr25 (DOT) news.prodigy.net... In article <47ba9261$0$14352$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what... (AT) xs4all (DOT) nl> wrote: joshian... (AT) gmail (DOT) com> schreef in bericht news:413d4cbe-4ea0-42da-9d1e-665db1d0e666 (AT) s8g2000prg (DOT) 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=3 D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D /* 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.
#62
| |||
| |||
|
|
In article 13f746a9-2e6d-40ef-8179-cbf02669480c...oglegroups.com>, whatsin (AT) xs4all (DOT) nl wrote: On 20 feb, 22:12, spamb... (AT) milmac (DOT) com (Doug Miller) wrote: In article <47bbefb1$0$14355$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what= .... (AT) xs4all (DOT) nl> wrote: "Doug Miller" <spamb... (AT) milmac (DOT) com> schreef in bericht news:dpMuj.6977$Ru4.2760 (AT) newssvr19 (DOT) news.prodigy.net... In article <47bb32ec$0$14357$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what... (AT) xs4all (DOT) nl> wrote: "Doug Miller" <spamb... (AT) milmac (DOT) com> schreef in bericht news:mrDuj.12400$R84.8493 (AT) newssvr25 (DOT) news.prodigy.net... In article <47ba9261$0$14352$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what... (AT) xs4all (DOT) nl> wrote: joshian... (AT) gmail (DOT) com> schreef in bericht news:413d4cbe-4ea0-42da-9d1e-665db1d0e666 (AT) s8g2000prg (DOT) 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=3 D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D /* 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. |
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
#63
| |||
| |||
|
|
In article 13f746a9-2e6d-40ef-8179-cbf02669480c...oglegroups.com>, whatsin (AT) xs4all (DOT) nl wrote: On 20 feb, 22:12, spamb... (AT) milmac (DOT) com (Doug Miller) wrote: In article <47bbefb1$0$14355$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what= .... (AT) xs4all (DOT) nl> wrote: "Doug Miller" <spamb... (AT) milmac (DOT) com> schreef in bericht news:dpMuj.6977$Ru4.2760 (AT) newssvr19 (DOT) news.prodigy.net... In article <47bb32ec$0$14357$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what... (AT) xs4all (DOT) nl> wrote: "Doug Miller" <spamb... (AT) milmac (DOT) com> schreef in bericht news:mrDuj.12400$R84.8493 (AT) newssvr25 (DOT) news.prodigy.net... In article <47ba9261$0$14352$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what... (AT) xs4all (DOT) nl> wrote: joshian... (AT) gmail (DOT) com> schreef in bericht news:413d4cbe-4ea0-42da-9d1e-665db1d0e666 (AT) s8g2000prg (DOT) 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=3 D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D /* 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. |
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
#64
| |||
| |||
|
|
In article 13f746a9-2e6d-40ef-8179-cbf02669480c...oglegroups.com>, whatsin (AT) xs4all (DOT) nl wrote: On 20 feb, 22:12, spamb... (AT) milmac (DOT) com (Doug Miller) wrote: In article <47bbefb1$0$14355$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what= .... (AT) xs4all (DOT) nl> wrote: "Doug Miller" <spamb... (AT) milmac (DOT) com> schreef in bericht news:dpMuj.6977$Ru4.2760 (AT) newssvr19 (DOT) news.prodigy.net... In article <47bb32ec$0$14357$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what... (AT) xs4all (DOT) nl> wrote: "Doug Miller" <spamb... (AT) milmac (DOT) com> schreef in bericht news:mrDuj.12400$R84.8493 (AT) newssvr25 (DOT) news.prodigy.net... In article <47ba9261$0$14352$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what... (AT) xs4all (DOT) nl> wrote: joshian... (AT) gmail (DOT) com> schreef in bericht news:413d4cbe-4ea0-42da-9d1e-665db1d0e666 (AT) s8g2000prg (DOT) 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=3 D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D /* 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. |
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
#65
| |||
| |||
|
|
In article 13f746a9-2e6d-40ef-8179-cbf02669480c...oglegroups.com>, whatsin (AT) xs4all (DOT) nl wrote: On 20 feb, 22:12, spamb... (AT) milmac (DOT) com (Doug Miller) wrote: In article <47bbefb1$0$14355$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what= .... (AT) xs4all (DOT) nl> wrote: "Doug Miller" <spamb... (AT) milmac (DOT) com> schreef in bericht news:dpMuj.6977$Ru4.2760 (AT) newssvr19 (DOT) news.prodigy.net... In article <47bb32ec$0$14357$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what... (AT) xs4all (DOT) nl> wrote: "Doug Miller" <spamb... (AT) milmac (DOT) com> schreef in bericht news:mrDuj.12400$R84.8493 (AT) newssvr25 (DOT) news.prodigy.net... In article <47ba9261$0$14352$e4fe5... (AT) news (DOT) xs4all.nl>, "shakespeare" what... (AT) xs4all (DOT) nl> wrote: joshian... (AT) gmail (DOT) com> schreef in bericht news:413d4cbe-4ea0-42da-9d1e-665db1d0e666 (AT) s8g2000prg (DOT) 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=3 D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D /* 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. |
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.