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

    −408.3

    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
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    64. 64
    65. 65
    66. 66
    67. 67
    68. 68
    69. 69
    70. 70
    71. 71
    72. 72
    73. 73
    74. 74
    75. 75
    76. 76
    77. 77
    78. 78
    79. 79
    80. 80
    81. 81
    82. 82
    83. 83
    84. 84
    85. 85
    86. 86
    87. 87
    88. 88
    89. 89
    90. 90
    91. 91
    92. 92
    93. 93
    94. 94
    SELECT   region_key AS region
            ,NAME(containedobjectsbasement_key) AS building
            ,container_node_id
            ,containedobjectsexchange_key
            ,object_name AS ats
            ,number_ranges AS diapazon
            ,exchange_type_name AS tip_ats
            ,exchange_mount_capacity AS emkost
            ,operator_licence_number AS license
            ,trace_line_relay_type_name AS tip_sl
            ,COUNT(trace_line_id) AS count5
            ,otkuda_nax AS otkuda_ats
            ,kuda_blya AS kuda_ats
            ,seven_nation_army
            ,ROWNUM
            ,cable_type_name
            ,logical_cable_length
            ,ATS_NAME
        FROM (SELECT DISTINCT r.object_id AS region_key
                             ,n.node_id AS containedobjectsbasement_key
                             ,n1.container_node_id AS container_node_id
                             ,e.node_id AS containedobjectsexchange_key
                             ,CAST(name2(e.node_id, e.exchange_class_id) AS VARCHAR2(128) ) AS object_name
                             ,e.exchange_mount_capacity AS exchange_mount_capacity
                             ,CAST( (SELECT stragg(first_number || '-' || last_number || '
                             '              )
                                       FROM number_interval
                                      WHERE exchange_id = e.node_id) AS VARCHAR2(1024) ) AS number_ranges
                             ,(SELECT e1.exchange_type_name
                                 FROM exchange_tl e1
                                WHERE e1.exchange_type_id = e.exchange_type_id) AS exchange_type_name
                             ,CAST( (SELECT s3.licence_number
                                       FROM service_operator_type s3
                                      WHERE s3.service_operator_type_id = e.operator_id) AS VARCHAR2(64) ) AS operator_licence_number
                             ,(SELECT t.trace_line_relay_type_name
                                 FROM trace_line_relay_type t
                                WHERE t.trace_line_relay_type_id = s.trace_line_relay_type_id) AS trace_line_relay_type_name
                             ,s.object_id trace_line_id
                             ,NAME(s1.exchange_id) otkuda_nax
                             ,NAME(trace_line.exchange_id) kuda_blya
                             ,(SELECT SUM(TO_NUMBER(n.last_number) - TO_NUMBER(n.first_number) + 1) AS s
                                 FROM number_interval n, region_l r
                                WHERE LENGTH(r.region_add_code || n.last_number) = 10
                                  AND LENGTH(r.region_add_code || n.first_number) = 10
                                  AND n.region_id = r.object_id
                                  AND n.exchange_id = e.node_id) AS seven_nation_army
                             ,ROWNUM
                         FROM region_l r, node n, exchange_l e, node n1, service_trace_line s, trace_line, service_l s1, node n2, OBJECT o
                        WHERE s.object_id = s1.object_id
                          AND s1.exchange_id = e.node_id
                          AND trace_line.service_id = s1.object_id
                          AND e.node_id = n1.node_id
                          AND n1.container_node_id = n.node_id
                          AND e.exchange_class_id = 100
                          AND n2.container_node_id = n.node_id
                          AND n2.entity_id = 108
                          AND n.region_id = r.object_id
                          AND n.node_type_id = 115
                          AND r.object_id = :region_key
                          AND n1.node_id = o.object_id
                          AND o.object_owner_type_id = 3) sel
            ,(SELECT r.object_id AS region_key2
                    ,c.node_id AS containedcoppercross_key
                    ,s2.linkedobjectsinterexchange_key AS linkedobjectsinterexchange_key
                    ,s2.logical_cable_length AS logical_cable_length
                    ,s2.cable_type_name AS cable_type_name
                    ,NAME(sl.exchange_id) AS ATS_NAME
                    ,NAME(el.node_id) AS el
                FROM region_l r
                    ,CROSS c
                    ,exchange_l el
                    ,node n2
                    ,service_l sl
                    ,trace_line tl
                    ,(SELECT l.node_id AS node_id
                            ,l.logical_cable_id AS linkedobjectsinterexchange_key
                            ,l.logical_cable_length AS logical_cable_length
                            ,(SELECT c.type_name
                                FROM cable_type_name c
                               WHERE c.cable_type_name_id = l.cable_type_id) AS cable_type_name
                        FROM logical_cable l
                       WHERE l.logical_cable_type_id = 455) s2
               WHERE s2.node_id(+) = c.node_id
                 AND c.node_id = n2.node_id
                 AND n2.entity_id = 108
                 AND c.node_id = el.cross_id
                 AND sl.exchange_id = el.node_id
                 AND tl.exchange_id = el.node_id
                 AND sl.entity_id = 156
                 AND r.object_id = :region_key) sel2
       WHERE NAME(kuda_blya) = ATS_NAME
    GROUP BY region_key, containedobjectsbasement_key, container_node_id, containedobjectsexchange_key, number_ranges, object_name, exchange_type_name, operator_licence_number, trace_line_relay_type_name, otkuda_nax, kuda_blya, exchange_mount_capacity
            ,operator_licence_number, seven_nation_army, ROWNUM, cable_type_name, logical_cable_length,ATS_NAME
    ORDER BY otkuda_ats

    Вызывает в Oracle10g внутреннюю ошибку
    ORA-00600: internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string]
    Cause: This is the generic internal error number for Oracle progra

    guest, 27 Ноября 2008

    Комментарии (9)
  2. SQL / Говнокод #22

    −1094

    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
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    SELECT A.Id AS AppraiserId, 
    	(SELECT A2.FieldValueInt AS SubcontractorId
    	FROM sm_forms_FieldValues A2
    	WHERE 
    		A2.InstanceId = (
    			SELECT 	FormInstanceId
    			FROM	sm_genericdatabase_Instances
    			WHERE	ModuleId=1736 AND Title='Appraisers'
    			)
    		AND 
    		A2.FieldId = (
    			SELECT Id FROM sm_forms_Fields 
    			WHERE InstanceId= A2.InstanceId 
    			AND Title = 'Subcontractor'	
    			)
    		AND
    		A2.ValueId=A.Id
    	) AS SubcontractorId,
    	
    	(SELECT A3.FieldValueInt
    	FROM sm_forms_FieldValues A3
    	WHERE 
    		InstanceId = (
    			SELECT 	FormInstanceId
    			FROM	sm_genericdatabase_Instances
    			WHERE	ModuleId=1736 AND Title='Appraisers'
    			)
    		AND 
    		FieldId = (
    			SELECT Id FROM sm_forms_Fields 
    			WHERE InstanceId= A3.InstanceId 
    			AND Title = 'Heavy Equipment'	
    			)
    		AND
    		A3.ValueId= A.Id
    	) AS HeavyEquipment,
    	
    	(SELECT A3.FieldValueInt
    	FROM sm_forms_FieldValues A3
    	WHERE 
    		InstanceId = (
    			SELECT 	FormInstanceId
    			FROM	sm_genericdatabase_Instances
    			WHERE	ModuleId=1736 AND Title='Appraisers'
    			)
    		AND 
    		FieldId = (
    			SELECT Id FROM sm_forms_Fields 
    			WHERE InstanceId= A3.InstanceId 
    			AND Title = 'Name'	
    			)
    		AND
    		A3.ValueId= A.Id
    	) AS Name
    	
    	
    	FROM sm_forms_Values A
    	where InstanceId = (
    			SELECT 	FormInstanceId
    			FROM	sm_genericdatabase_Instances
    			WHERE	ModuleId=1736 AND Title='Appraisers'
    			)
    	ORDER BY 2

    выборка из большой базы... И нихрена не понятной.

    guest, 27 Ноября 2008

    Комментарии (1)