- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
CREATE OR REPLACE VIEW my_schema.my_view AS
SELECT
TO_NUMBER (c.payment_term_code) ID,
c.payment_term_code sap_code, c.payment_term_name longname,
c.short_name shortname, c.comment_line comments, c.cust_relevant, c.vend_relevant, c.active active,
c.updated_by created_by, c.updated_by updated_by, c.updated_on created_on, c.updated_on updated_on, 1 txn_ver
FROM my_schema.my_table c
WHERE ( LENGTH (c.payment_term_code) = 1
AND SUBSTR (c.payment_term_code, 1, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
)
OR ( LENGTH (c.payment_term_code) = 2
AND SUBSTR (c.payment_term_code, 1, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND SUBSTR (c.payment_term_code, 2, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
)
OR ( LENGTH (c.payment_term_code) = 3
AND SUBSTR (c.payment_term_code, 1, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND SUBSTR (c.payment_term_code, 2, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND SUBSTR (c.payment_term_code, 3, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
)
OR ( LENGTH (c.payment_term_code) = 4
AND SUBSTR (c.payment_term_code, 1, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND SUBSTR (c.payment_term_code, 2, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND SUBSTR (c.payment_term_code, 3, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND SUBSTR (c.payment_term_code, 4, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
)
OR ( LENGTH (c.payment_term_code) = 5
AND SUBSTR (c.payment_term_code, 1, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND SUBSTR (c.payment_term_code, 2, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND SUBSTR (c.payment_term_code, 3, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND SUBSTR (c.payment_term_code, 4, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND SUBSTR (c.payment_term_code, 5, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
)
UNION
SELECT ID, sap_code, longname, shortname, comments, 'Y', 'Y', active, created_by, updated_by, created_on, updated_on, 1
FROM my_schema.my_other_table) tt
Вот так в 30 строк пиндосы защищаются от проникновения буковков в TO_NUMBER()
guest 12.03.2009 22:47 # +2
не верю (с)
guest 13.03.2009 06:29 # 0
а я верю
guest 16.03.2009 11:38 # 0
не пиндосы, а индусы
guest 16.03.2009 14:45 # 0
Сами вы индусы, типичный пример "китайского кода"
guest 21.10.2009 10:00 # 0
Uhehesh 10.08.2014 14:51 # 0