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

    −407.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

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

  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

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