oracle某一字段使用to_number报无效数字的错
排查:字段中存在字符(如空格等)
SELECT pla.po_line_id, pla.attribute2 FROM po_lines_all pla WHERE pla.attribute2 IS NOT NULL AND length( translate ( pla.attribute2, '-.0123456789' || pla.attribute2, '-.0123456789' ) ) <> length( pla.attribute2 )
解决:去掉字符,只留数字(如123 .75 0 ——> 123.750)
SELECT pla.po_line_id, to_number ( translate ( pla.attribute2, '-.0123456789' || pla.attribute2, '-.0123456789' ) ) attribute2 FROM po_lines_all pla WHERE pla.last_update_date > to_date ( '2020-05-10', 'yyyy-MM-dd' ) AND pla.po_line_id IN ( 11, 12, 13, 14, 15 ) ORDER BY pla.po_line_id DESC