Home » SQL & PL/SQL » SQL & PL/SQL » to_number and ORA-01722 (really interesting)
to_number and ORA-01722 (really interesting) [message #251918] Tue, 17 July 2007 02:42 Go to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
Hi guys,


on ORACLE 10g R1


app_no (application number) : number(4)
exam_result (exam result) : varchar2(20)
order_no (order number) : number(4)


select app_no, order_no, exam_result from test_b
where order_no is NOT NULL
-- and to_number(exam_result) > 70
order by  to_number(exam_result)


app_no order_no exam_result
---------- ---------- --------------------
1008 393 40.493
1323 392 40.575
1297 391 40.916
1186 389 41.761
1206 390 41.761
1127 387 41.902
1190 388 41.902
1386 386 42.043
....
....
....

it works well .. because "and to_number(exam_result) > 70" is in comment ..


and when i remove the comment and run again, it gives me invalid number error ..

select app_no, order_no, exam_result from test_b
where order_no is NOT NULL
and to_number(exam_result) > 70
order by  to_number(exam_result)


app_no order_no exam_result
---------- ---------- --------------------
ORA-01722: invalid number


what's wrong ? is there a bug or where am i ?

thanx ..
Re: to_number and ORA-01722 (really interesting) [message #251921 is a reply to message #251918] Tue, 17 July 2007 03:00 Go to previous messageGo to next message
ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE
Junior Member
exam_result (exam result) : varchar2(20)

as exam_result col. is varchar2 ,It may contain characters for some rows that is why it is giving this error.

Re: to_number and ORA-01722 (really interesting) [message #251926 is a reply to message #251921] Tue, 17 July 2007 03:25 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
come on .. when to_number function is in "order by", it works well ..

by the way, i've already checked data million times .. i think, it's not related data .. so it's interesting Smile if it's not, what is interesting ? Smile
Re: to_number and ORA-01722 (really interesting) [message #251934 is a reply to message #251926] Tue, 17 July 2007 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case that produces your error.
With the data you gave, did you have it? I don't have it.

Regards
Michel
Re: to_number and ORA-01722 (really interesting) [message #251938 is a reply to message #251926] Tue, 17 July 2007 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the result of:
select parameter, name from v$nls_parameters order by 1;

Regards
Michel
Re: to_number and ORA-01722 (really interesting) [message #251942 is a reply to message #251938] Tue, 17 July 2007 03:53 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
here it is ..


PARAMETER               VALUE                        
----------------------------------------------------
NLS_CALENDAR            GREGORIAN                    
NLS_CHARACTERSET        WE8ISO8859P9                 
NLS_COMP                BINARY                       
NLS_CURRENCY            $                            
NLS_DATE_FORMAT         DD-MON-YY                    
NLS_DATE_LANGUAGE       AMERICAN                     
NLS_DUAL_CURRENCY       $                            
NLS_ISO_CURRENCY        AMERICA                      
NLS_LANGUAGE            AMERICAN                     
NLS_LENGTH_SEMANTICS    BYTE                         
NLS_NCHAR_CHARACTERSET  WE8ISO8859P9                 
NLS_NCHAR_CONV_EXCP     FALSE                        
NLS_NUMERIC_CHARACTERS  .,                           
NLS_SORT                TURKISH                      
NLS_TERRITORY           AMERICA                      
NLS_TIME_FORMAT         HH.MI.SSXFF AM               
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM     
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 
NLS_TIME_TZ_FORMAT      HH.MI.SSXFF AM TZR           

Michel Cadot wrote on Tue, 17 July 2007 11:44
Post the result of:
select parameter, name from v$nls_parameters order by 1;

Regards
Michel


[Updated on: Tue, 17 July 2007 03:56]

Report message to a moderator

Re: to_number and ORA-01722 (really interesting) [message #251946 is a reply to message #251942] Tue, 17 July 2007 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about the data that fail?

Regards
Michel
Re: to_number and ORA-01722 (really interesting) [message #252027 is a reply to message #251926] Tue, 17 July 2007 12:45 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
This is a classic example of how ORDER BY work differently than WHERE clauses. I really wished I saved in a bookmark or something Tom Kyte's example (and I think someone here also showed it) of how the WHERE clause will affect whether you get the 1722 error based on which rows it returns.
Maybe someone will come up with a link or an example. I just don't have the time right now to make an example myself.
Re: to_number and ORA-01722 (really interesting) [message #252038 is a reply to message #252027] Tue, 17 July 2007 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think this is the same case.
The point comes from to_number and I don't see any reason that to_number works differently in where and order.
I agree that often comparison is different in order and where and nls_comp, nls_sort... are there to custom this.

This is why having data that raises this should be welcome as I was unable to reproduce it.

Regards
Michel
Re: to_number and ORA-01722 (really interesting) [message #252174 is a reply to message #251918] Wed, 18 July 2007 01:19 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
data

APP_NO ORDER_NO EXAM_RESULT 
---------------------------
1008   393      40.493      
1323   392      40.575      
1297   391      40.916      
1186   389      41.761      
1206   390      41.761      
1127   387      41.902      
1190   388      41.902      
1386   386      42.043      
1224   385      42.184      
1340   383      42.325      
1346   384      42.325      
1101   382      42.406      
1345   381      43.171      
1252   380      43.171      
1304   379      43.312      
1165   378      43.312      
1331   377      43.453      
1247   376      43.594      
1094   373      43.735      
1031   374      43.735      
1193   375      43.735      
1071   372      43.876      
1235   371      44.017      
1104   370      44.158      
1245   369      44.158      
1007   368      44.299      
1289   367      44.439      
1299   366      44.580      
1217   365      44.721      
1198   364      44.862      
1218   363      44.862      
1072   362      45.003      
1243   361      45.144      
1012   359      45.426      
1106   360      45.426      
1137   358      45.426      
1044   357      45.567      
1248   355      45.708      
1188   356      45.708      
1283   354      45.708      
1174   353      45.849      
1294   352      45.849      
1110   351      45.990      
1225   350      45.990      
1097   348      46.131      
1129   349      46.131      
1351   347      46.131      
1319   345      46.272      
1249   346      46.272      
1130   344      46.373      
1180   343      46.413      
1083   342      46.526      
1034   341      46.554      
1204   339      46.554      
1207   340      46.554      
1251   337      46.695      
1342   338      46.695      
1236   336      46.836      
1335   335      46.836      
1135   334      46.977      
1051   333      46.977      
1400   332      46.984      
1150   331      47.118      
1305   329      47.118      
1391   330      47.118      
1146   327      47.400      
1265   328      47.400      
1128   326      47.541      
1381   325      47.822      
1178   324      47.822      
1116   323      47.963      
1306   322      47.963      
1011   321      48.104      
1126   320      48.245      
1309   319      48.245      
1030   318      48.386      
1208   317      48.386      
1045   316      48.527      
1164   315      48.527      
1047   314      48.662      
1065   311      48.668      
1334   310      48.668      
1147   313      48.668      
1144   312      48.668      
1197   309      48.809      
1368   308      48.814      
1202   307      48.950      
1076   305      49.091      
1066   304      49.091      
1336   306      49.091      
1371   303      49.232      
1038   301      49.373      
1099   302      49.373      
1200   300      49.373      
1107   299      49.514      
1084   298      49.514      
1327   297      49.514      
1354   296      49.514      
1157   293      49.655      
1074   295      49.655      
1270   292      49.655      
1241   294      49.655      
1320   291      49.796      
1138   288      50.078      
1028   290      50.078      
1286   289      50.078      
1353   287      50.219      
1308   286      50.340      
1121   281      50.360      
1246   284      50.360      
1169   282      50.360      
1229   283      50.360      
1043   285      50.360      
1317   280      50.493      
1085   279      50.501      
1344   278      50.501      
1125   277      50.642      
1341   276      50.642      
1052   272      50.783      
1088   271      50.783      
1196   273      50.783      
1398   275      50.783      
1057   274      50.783      
1358   270      50.798      
1037   269      50.924      
1356   268      50.924      
1184   267      51.064      
1310   265      51.205      
1332   266      51.205      
1163   264      51.346      
1212   262      51.346      
1365   263      51.346      
1033   260      51.487      
1277   257      51.487      
1256   258      51.487      
1338   261      51.487      
1158   259      51.487      
1181   256      51.561      
1001   255      51.628      
1373   254      51.769      
1124   253      51.910      
1016   252      52.051      
1192   250      52.051      
1244   249      52.051      
1313   251      52.051      
1078   246      52.192      
1111   247      52.192      
1376   248      52.192      
1264   245      52.333      
1068   242      52.474      
1379   244      52.474      
1383   243      52.474      
1148   240      52.756      
1254   241      52.756      
1234   239      53.038      
1054   238      53.086      
1349   237      53.320      
1113   235      53.461      
1062   236      53.461      
1260   233      53.602      
1262   234      53.602      
1325   232      53.602      
1098   230      53.743      
1315   229      53.743      
1077   231      53.743      
1142   228      53.884      
1393   227      53.884      
1119   226      53.884      
1048   224      54.025      
1364   225      54.025      
1375   222      54.166      
1326   221      54.166      
1384   223      54.166      
1082   220      54.306      
1087   219      54.447      
1112   218      54.447      
1156   214      54.588      
1025   216      54.588      
1117   215      54.588      
1171   217      54.588      
1070   211      54.729      
1281   209      54.729      
1205   213      54.729      
1242   212      54.729      
1269   210      54.729      
1120   208      54.729      
1064   205      54.870      
1284   206      54.870      
1288   207      54.870      
1175   204      54.870      
1050   202      55.011      
1377   203      55.011      
1159   201      55.152      
1366   200      55.152      
1279   199      55.293      
1103   198      55.434      
1086   195      55.575      
1222   196      55.575      
1290   197      55.575      
1399   194      55.857      
1108   191      56.280      
1301   193      56.280      
1311   192      56.280      
1210   189      56.421      
1168   190      56.421      
1343   187      56.562      
1258   188      56.562      
1396   186      56.703      
1362   185      56.844      
1131   184      56.985      
1276   183      56.985      
1136   182      57.126      
1177   181      57.267      
1369   179      57.408      
1285   180      57.408      
1080   178      57.549      
1291   177      57.549      
1278   176      57.664      
1046   175      57.689      
1199   174      57.689      
1133   173      57.830      
1333   172      57.830      
1139   171      57.830      
1155   169      57.971      
1176   170      57.971      
1237   168      57.971      
1395   167      58.112      
1330   166      58.253      
1361   164      58.253      
1292   165      58.253      
1394   163      58.535      
1261   162      58.579      
1374   161      58.676      
1318   160      58.676      
1363   158      58.817      
1215   159      58.817      
1189   157      58.817      
1322   156      58.817      
1273   155      58.958      
1152   154      59.099      
1132   153      59.240      
1063   150      59.381      
1392   151      59.381      
1209   149      59.381      
1287   152      59.381      
1061   148      59.495      
1397   147      59.522      
1355   145      59.522      
1239   146      59.522      
1275   144      59.522      
1009   143      59.663      
1402   142      59.804      
1307   140      60.086      
1298   141      60.086      
1296   139      60.227      
1367   138      60.227      
1300   137      60.257      
1226   136      60.257      
1151   134      60.509      
1271   133      60.509      
1014   135      60.509      
1141   132      60.650      
1339   130      60.650      
1359   131      60.650      
1036   129      60.791      
1095   127      60.791      
1179   128      60.791      
1006   125      60.931      
1240   124      60.931      
1238   126      60.931      
1173   121      61.213      
1282   123      61.213      
1257   122      61.213      
1259   120      61.354      
1266   119      61.495      
1040   118      61.636      
1035   116      61.918      
1387   117      61.918      
1091   115      62.059      
1268   113      62.059      
1329   114      62.059      
1370   112      62.059      
1390   111      62.341      
1055   110      62.482      
1182   109      62.482      
1162   108      62.623      
1231   106      62.623      
1143   107      62.623      
1328   104      62.764      
1167   105      62.764      
1109   103      62.905      
1274   101      62.905      
1145   102      62.905      
1118   100      62.905      
1161   97       63.046      
1134   98       63.046      
1089   99       63.046      
1075   96       63.046      
1160   95       63.187      
1059   94       63.328      
1187   93       63.328      
1347   92       63.469      
1023   89       63.610      
1312   91       63.610      
1293   90       63.610      
1049   88       63.751      
1053   87       63.892      
1194   86       64.033      
1233   85       64.033      
1303   84       64.174      
1017   80       64.314      
1223   82       64.314      
1220   79       64.314      
1005   83       64.314      
1170   81       64.314      
1041   78       64.596      
1021   76       64.737      
1201   75       64.737      
1372   77       64.737      
1195   74       64.878      
1032   73       65.160      
1060   71       65.301      
1382   72       65.301      
1272   70       65.583      
1056   69       65.724      
1093   68       65.724      
1324   67       65.865      
1020   65       66.006      
1115   63       66.006      
1337   66       66.006      
1321   64       66.006      
1123   61       66.288      
1022   60       66.288      
1221   62       66.288      
1069   59       66.429      
1401   57       66.429      
1092   58       66.429      
1203   56       66.711      
1010   55       66.852      
1105   53       66.993      
1389   54       66.993      
1348   52       66.993      
1185   51       66.993      
1183   50       66.993      
1073   48       67.134      
1058   47       67.134      
1295   49       67.134      
1250   46       67.134      
1360   45       67.275      
1096   44       67.556      
1122   43       67.697      
1090   41       67.697      
1027   42       67.697      
1378   40       67.838      
1255   39       67.838      
1213   37       67.979      
1350   38       67.979      
1191   35       68.261      
1385   36       68.261      
1081   34       68.402      
1100   33       68.543      
1253   32       68.543      
1211   31       68.684      
1002   30       69.107      
1013   29       69.389      
1267   28       69.671      
1230   27       69.812      
1357   26       69.812      
1019   25       69.953      
1216   24       69.953      
1029   22       70.094      
1003   21       70.094      
1380   23       70.094      
1114   20       70.376      
1263   19       70.480      
1153   17       70.517      
1154   18       70.517      
1302   16       71.221      
1018   14       71.362      
1280   15       71.362      
1015   13       71.362      
1004   11       71.503      
1172   12       71.503      
1026   10       71.700      
1042   8        71.926      
1388   9        71.926      
1039   7        72.208      
1149   6        72.631      
1024   5        72.772      
1314   4        73.336      
1166   3        73.336      
1219   2        74.041      
1232   1        74.041      
Re: to_number and ORA-01722 (really interesting) [message #252184 is a reply to message #252174] Wed, 18 July 2007 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is there any whitespace in "exam_result" field?

Regards
Michel
Re: to_number and ORA-01722 (really interesting) [message #252188 is a reply to message #252174] Wed, 18 July 2007 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> alter session set nls_numeric_characters='.,';

Session altered.

SQL> alter session set nls_sort=turkish;

Session altered.

SQL> select app_no, order_no, exam_result from t
  2  where order_no is NOT NULL
  3  -- and to_number(exam_result) > 70
  4  order by  to_number(exam_result)
  5  /
    APP_NO   ORDER_NO EXAM_RESULT
---------- ---------- --------------------
      1008        393 40.493
      1323        392 40.575
      1297        391 40.916
      1186        389 41.761
      1206        390 41.761
...
      1024          5 72.772
      1166          3 73.336
      1314          4 73.336
      1232          1 74.041
      1219          2 74.041

393 rows selected.

SQL> select app_no, order_no, exam_result from t
  2  where order_no is NOT NULL
  3   and to_number(exam_result) > 70
  4  order by  to_number(exam_result)
  5  /
    APP_NO   ORDER_NO EXAM_RESULT
---------- ---------- --------------------
      1029         22 70.094
      1003         21 70.094
      1380         23 70.094
      1114         20 70.376
      1263         19 70.480
      1153         17 70.517
      1154         18 70.517
      1302         16 71.221
      1018         14 71.362
      1280         15 71.362
      1015         13 71.362
      1004         11 71.503
      1172         12 71.503
      1026         10 71.700
      1042          8 71.926
      1388          9 71.926
      1039          7 72.208
      1149          6 72.631
      1024          5 72.772
      1314          4 73.336
      1166          3 73.336
      1219          2 74.041
      1232          1 74.041

23 rows selected.

I have no problem.
What is your version with 4 decimals.

Regards
Michel
Re: to_number and ORA-01722 (really interesting) [message #252191 is a reply to message #252184] Wed, 18 July 2007 02:04 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
no ..
there is not ..
i also tried to_number(trim(exam_result)) ..
the result is the same ..
Re: to_number and ORA-01722 (really interesting) [message #252196 is a reply to message #252188] Wed, 18 July 2007 02:11 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
hi michel ..

oracle version is 10.1.0.3.0

but it's really interesting .. now, i really wonder what the reason is .. Smile


Michel Cadot wrote on Wed, 18 July 2007 09:58
....
....
....

I have no problem.
What is your version with 4 decimals.

Regards
Michel


Re: to_number and ORA-01722 (really interesting) [message #252251 is a reply to message #252188] Wed, 18 July 2007 06:34 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
michel,

i've to find out why my oracle give this error ..

if it's not too much trouble, can you send me your nls parameters ?

thanks ..

Michel Cadot wrote on Wed, 18 July 2007 09:58

I have no problem.
What is your version with 4 decimals.

Regards
Michel


Re: to_number and ORA-01722 (really interesting) [message #252259 is a reply to message #252251] Wed, 18 July 2007 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The same as yours. I modified in the test I posted the differences to fit yours.

Regards
Michel
Re: to_number and ORA-01722 (really interesting) [message #252278 is a reply to message #252259] Wed, 18 July 2007 08:23 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
Sad

well, any suggestions ?

Michel Cadot wrote on Wed, 18 July 2007 15:10
The same as yours. I modified in the test I posted the differences to fit yours.

Regards
Michel


Re: to_number and ORA-01722 (really interesting) [message #252282 is a reply to message #251918] Wed, 18 July 2007 08:28 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I would put dbms_output or write the records as you process them to try to isolate the data that is causing the grief.
Re: to_number and ORA-01722 (really interesting) [message #252462 is a reply to message #252282] Wed, 18 July 2007 22:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The problem is in the order that Oracle evaluates expressions.

Non-indexed AND clauses are evaluated from the bottom-up.
Non-indexed OR clauses are evaluated from the top down.
ORDER BY expressions are only evaluated for rows that satisfy ALL where predicates.


Condiser what happens when a row has a NULL ORDER_NO, and a non-numeric EXAM_RESULT.
select app_no, order_no, exam_result from test_b
where order_no is NOT NULL
--and to_number(exam_result) > 70
order by  to_number(exam_result)
In this SQL, the WHERE predicate filters the row cleanly, so the ORDER BY does not have the chance to fail.

select app_no, order_no, exam_result from test_b
where order_no is NOT NULL
and to_number(exam_result) > 70
order by  to_number(exam_result)
In this SQL, the AND conditions are executed from the bottom up, so the TO_NUMBER expression is evaluated on the dodgy row BEFORE the NOT NULL expression can filter it out.

You can avoid the error by transposing the predicates:
select app_no, order_no, exam_result from test_b
where to_number(exam_result) > 70
and order_no is NOT NULL
order by  to_number(exam_result)
Now the NOT NULL will run first and filter out the bad row.

Ross Leishman
Re: to_number and ORA-01722 (really interesting) [message #252504 is a reply to message #252462] Thu, 19 July 2007 01:06 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
hi rleishman,

but nothing changes ..

even i change sql as below :

select exam_result from
   (
     select
     app_no,
     order_no,
     to_number(exam_result) as exam_result
     from test_b
     where order_no is NOT NULL
     -- now this cluster contains no bad data for to_number
     -- and it works alone
   )
where exam_result > 70


it did not work ..

i think the problem's source is my oracle settings ..

because, Michel Cadot said he had no problem with same records ..

of course thanks for your explanations ..



rleishman wrote on Thu, 19 July 2007 06:13
The problem is in the order that Oracle evaluates expressions.

Non-indexed AND clauses are evaluated from the bottom-up.
Non-indexed OR clauses are evaluated from the top down.
ORDER BY expressions are only evaluated for rows that satisfy ALL where predicates.


Condiser what happens when a row has a NULL ORDER_NO, and a non-numeric EXAM_RESULT.
select app_no, order_no, exam_result from test_b
where order_no is NOT NULL
--and to_number(exam_result) > 70
order by  to_number(exam_result)
In this SQL, the WHERE predicate filters the row cleanly, so the ORDER BY does not have the chance to fail.

select app_no, order_no, exam_result from test_b
where order_no is NOT NULL
and to_number(exam_result) > 70
order by  to_number(exam_result)
In this SQL, the AND conditions are executed from the bottom up, so the TO_NUMBER expression is evaluated on the dodgy row BEFORE the NOT NULL expression can filter it out.

You can avoid the error by transposing the predicates:
select app_no, order_no, exam_result from test_b
where to_number(exam_result) > 70
and order_no is NOT NULL
order by  to_number(exam_result)
Now the NOT NULL will run first and filter out the bad row.

Ross Leishman

Re: to_number and ORA-01722 (really interesting) [message #252507 is a reply to message #252504] Thu, 19 July 2007 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
because, Michel Cadot said he had no problem with same records ..

But there was no NULL order_no with invalid exam_result in the data you gave.

Search for exam_result where order_no is NULL.

Quote:
even i change sql as below :
select exam_result from
   (
     select
     app_no,
     order_no,
     to_number(exam_result) as exam_result
     from test_b
     where order_no is NOT NULL
     -- now this cluster contains no bad data for to_number
     -- and it works alone
   )
where exam_result > 70

it did not work ..


Oracle merged/unnested the inner query, so there is no difference with the original one.

Regards
Michel
Re: to_number and ORA-01722 (really interesting) [message #252518 is a reply to message #252507] Thu, 19 July 2007 01:42 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
oh, sorry for my ignorance ..


it's just working when i create new table using inner query ..
of course
create table test_b2 as 
     select
     app_no,
     order_no,
     to_number(exam_result) as exam_result
     from test_b
     where order_no is NOT NULL


select * from test_b2
where exam_result > 70


of course it runs .. because, exam_result's data type is being converted float ..

i bet it's connected with my settings

but stupidly .. Smile



Michel Cadot wrote on Thu, 19 July 2007 09:15
Quote:
because, Michel Cadot said he had no problem with same records ..

But there was no NULL order_no with invalid exam_result in the data you gave.

Search for exam_result where order_no is NULL.

Quote:
even i change sql as below :
select exam_result from
   (
     select
     app_no,
     order_no,
     to_number(exam_result) as exam_result
     from test_b
     where order_no is NOT NULL
     -- now this cluster contains no bad data for to_number
     -- and it works alone
   )
where exam_result > 70

it did not work ..


Oracle merged/unnested the inner query, so there is no difference with the original one.

Regards
Michel


Re: to_number and ORA-01722 (really interesting) [message #252546 is a reply to message #252518] Thu, 19 July 2007 02:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This should return an error
SELECT MAX(TO_NUMBER(exam_result))
FROM test_b


This should return an error
SELECT MAX(TO_NUMBER(exam_result))
FROM test_b
WHERE order_no IS NULL


This should NOT return an error
SELECT MAX(TO_NUMBER(exam_result))
FROM test_b
WHERE order_no IS NOT NULL


Try these out. If my predictions are right, go back and read my earlier post again and follow the advice therein.

Ross Leishman
Re: to_number and ORA-01722 (really interesting) [message #252597 is a reply to message #252507] Thu, 19 July 2007 05:57 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
the records which has got null order_no have non numeric exam_result .. so we don't take them .. ok ?

Michel Cadot wrote on Thu, 19 July 2007 09:15

But there was no NULL order_no with invalid exam_result in the data you gave.

Search for exam_result where order_no is NULL.


Re: to_number and ORA-01722 (really interesting) [message #252598 is a reply to message #252546] Thu, 19 July 2007 06:10 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
look, i have already tried your earlier advice .. but did not work ..

yes you're right these are working well .. i mean, results are as you said ..

or i don't understand your advice correctly ..


i changed condition's places in "where" and "and" clause but nothing changed .. Smile


rleishman wrote on Thu, 19 July 2007 10:48
This should return an error
SELECT MAX(TO_NUMBER(exam_result))
FROM test_b


This should return an error
SELECT MAX(TO_NUMBER(exam_result))
FROM test_b
WHERE order_no IS NULL


This should NOT return an error
SELECT MAX(TO_NUMBER(exam_result))
FROM test_b
WHERE order_no IS NOT NULL


Try these out. If my predictions are right, go back and read my earlier post again and follow the advice therein.

Ross Leishman

Re: to_number and ORA-01722 (really interesting) [message #252812 is a reply to message #252598] Fri, 20 July 2007 01:28 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Maybe there is some different setting for NLS on your computer (registry setting)?
Re: to_number and ORA-01722 (really interesting) [message #252821 is a reply to message #252812] Fri, 20 July 2007 02:52 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
it's a good point Smile you says, it may connect with my client settings ..but, i've already tried it on server Smile the result is the same ..

thanks anyway .. what else ?

skooman wrote on Fri, 20 July 2007 09:28
Maybe there is some different setting for NLS on your computer (registry setting)?

Re: to_number and ORA-01722 (really interesting) [message #252825 is a reply to message #251918] Fri, 20 July 2007 03:05 Go to previous messageGo to next message
ozgur.tugrul
Messages: 14
Registered: July 2007
Junior Member
hey .. solved .. but how ?

i created a new table from old table ..

create table test_b2 as select * from test_b


and

i run same sql on this table ..

select * from test_b2
where order_no is not NULL
and to_number(exam_result)>70


and it runned correctly without an error ..

it means,
old table may have inexplicable bad data (may be character) .. and trim or something else does not catch this error ..

may be, when we create new table using old table, oracle fixes broken or bad characters ..

thank you all .. and i'm very sorry to make you busy for stupid situation ..

[Updated on: Fri, 20 July 2007 03:07]

Report message to a moderator

Re: to_number and ORA-01722 (really interesting) [message #252864 is a reply to message #252825] Fri, 20 July 2007 07:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No, this just means the SQL is running a different plan. The old SQL could have been performing an index Fast Full Scan, but the new table has no index.

Try this on the new table:
SELECT MAX(TO_NUMBER(exam_result))
FROM test_b2
If you get an error then the data has not magically self-corrected

Do you have 10g?

Try this:
SELECT *
from test_b
where NOT regexp_like(exam_result,'^( *-?[0-9]+)?(\.[0-9]+)? *$')


If you don't have 10g, try this:
create or replace function test_num (p varchar2) return number is
  r number;
begin
  r := to_number(p);
  return(r);
exception
  when others then return null;
end;
/

select * from test_b
where test_num(exam_result) is null
and exam_result is not null
/

Ross Leishman
Re: to_number and ORA-01722 (really interesting) [message #252879 is a reply to message #251926] Fri, 20 July 2007 08:15 Go to previous messageGo to next message
ravi_palagani
Messages: 1
Registered: July 2007
Junior Member
i think the question is wrong try it onc dude Laughing
Re: to_number and ORA-01722 (really interesting) [message #255418 is a reply to message #252597] Tue, 31 July 2007 15:34 Go to previous messageGo to next message
mark918
Messages: 3
Registered: April 2007
Location: Seattle, WA, US
Junior Member
ozgur.tugrul wrote on Thu, 19 July 2007 03:57
the records which has got null order_no have non numeric exam_result .. so we don't take them .. ok ?

Michel Cadot wrote on Thu, 19 July 2007 09:15

But there was no NULL order_no with invalid exam_result in the data you gave.

Search for exam_result where order_no is NULL.




So the exam_result field, which is varchar2, contains numeric values if the order_no is not null, and non-numeric values if the order_no is null?
You were lucky before that the clause "order_no is NOT NULL" was applied before the "to_number(exam_result)". When you upgraded, you weren't lucky anymore. Now that you've recreated your table, you are lucky again, but you shouldn't depend on luck to get around a design issue. You should code your query to deal with your data, whether you are lucky or not.

This will work:

select app_no, order_no, exam_result 
from   test_b
where  order_no is NOT NULL
and    case when order_no is not null
            then to_number(exam_result) 
            else null end > 70
order by 
       case when order_no is not null 
            then to_number(exam_result) 
            else null end;
Re: to_number and ORA-01722 (really interesting) [message #255513 is a reply to message #255418] Wed, 01 August 2007 01:52 Go to previous messageGo to next message
renegade
Messages: 2
Registered: January 2006
Junior Member
Michel Cadot wrote on Thu, 19 July 2007 09:15

select app_no, order_no, exam_result 
from   test_b
where  order_no is NOT NULL
and    case when order_no is not null
            then to_number(exam_result) 
            else null end > 70
order by 
       case when order_no is not null 
            then to_number(exam_result) 
            else null end;



i'm still on vacation Smile so i didn't try, but using case sounds sensible Wink

thanks michel ..
Re: to_number and ORA-01722 (really interesting) [message #255517 is a reply to message #255513] Wed, 01 August 2007 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey! I didn't say that, this is Mark.
So, thanks Mark! Wink

Regards
Michel
Re: to_number and ORA-01722 (really interesting) [message #255531 is a reply to message #255517] Wed, 01 August 2007 02:21 Go to previous message
renegade
Messages: 2
Registered: January 2006
Junior Member
i'm so sorry ..
i confused with quote Smile
very very thanks mark ..
Previous Topic: update else insert
Next Topic: modifying a view
Goto Forum:
  


Current Time: Thu Dec 08 18:43:03 CST 2016

Total time taken to generate the page: 0.16260 seconds