1. SQL / Говнокод #701

    −868

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 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 Марта 2009

    Комментарии (6) RSS

    Добавить комментарий