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

    −87.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
    SELECT DISTINCT BE.ID as ID,BE.NAME as NAME,BE.CODE as CODE,BE.IBLOCK_ID as
    IBLOCK_ID,BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID,B.DETAIL_PAGE_URL as
    DETAIL_PAGE_URL,BE.DETAIL_TEXT as DETAIL_TEXT,BE.DETAIL_TEXT_TYPE as
    DETAIL_TEXT_TYPE,BE.DETAIL_PICTURE as DETAIL_PICTURE,BE.PREVIEW_TEXT as
    PREVIEW_TEXT,BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE,BE.PREVIEW_PICTURE as
    PREVIEW_PICTURE,L.DIR as LANG_DIR,BE.XML_ID as EXTERNAL_ID,B.IBLOCK_TYPE_ID as
    IBLOCK_TYPE_ID,B.CODE as IBLOCK_CODE,B.XML_ID as IBLOCK_EXTERNAL_ID FROM
    b_iblock B INNER JOIN b_lang L ON B.LID=L.LID INNER JOIN b_iblock_element BE
    ON BE.IBLOCK_ID = B.ID INNER JOIN b_iblock_section_element BSE ON
    BSE.IBLOCK_ELEMENT_ID = BE.ID INNER JOIN b_iblock_section BSubS ON
    BSE.IBLOCK_SECTION_ID = BSubS.ID INNER JOIN b_iblock_section BS ON
    (BSubS.IBLOCK_ID=BS.IBLOCK_ID AND BSubS.LEFT_MARGIN>=BS.LEFT_MARGIN AND
    BSubS.RIGHT_MARGIN<=BS.RIGHT_MARGIN) INNER JOIN b_iblock_property FP1 ON
    FP1.IBLOCK_ID=B.ID AND FP1.CODE='code2' INNER JOIN b_iblock_element_property
    FPV1 ON FP1.ID=FPV1.IBLOCK_PROPERTY_ID AND FPV1.IBLOCK_ELEMENT_ID=BE.ID INNER
    JOIN b_iblock_property FP2 ON FP2.IBLOCK_ID=B.ID AND FP2.CODE='code3' INNER
    JOIN b_iblock_element_property FPV2 ON FP2.ID=FPV2.IBLOCK_PROPERTY_ID AND
    FPV2.IBLOCK_ELEMENT_ID=BE.ID INNER JOIN b_iblock_property FP3 ON
    FP3.IBLOCK_ID=B.ID AND FP3.CODE='code4' INNER JOIN b_iblock_element_property
    FPV3 ON FP3.ID=FPV3.IBLOCK_PROPERTY_ID AND FPV3.IBLOCK_ELEMENT_ID=BE.ID INNER
    JOIN b_iblock_property FP4 ON FP4.IBLOCK_ID=B.ID AND FP4.CODE='code5' INNER
    JOIN b_iblock_element_property FPV4 ON FP4.ID=FPV4.IBLOCK_PROPERTY_ID AND
    FPV4.IBLOCK_ELEMENT_ID=BE.ID INNER JOIN b_iblock_property FP5 ON
    FP5.IBLOCK_ID=B.ID AND FP5.CODE='code7' INNER JOIN b_iblock_element_property
    FPV5 ON FP5.ID=FPV5.IBLOCK_PROPERTY_ID AND FPV5.IBLOCK_ELEMENT_ID=BE.ID INNER
    JOIN b_iblock_property FP6 ON FP6.IBLOCK_ID=B.ID AND FP6.CODE='code9' INNER
    JOIN b_iblock_element_property FPV6 ON FP6.ID=FPV6.IBLOCK_PROPERTY_ID AND
    FPV6.IBLOCK_ELEMENT_ID=BE.ID INNER JOIN b_iblock_property FP7 ON
    FP7.IBLOCK_ID=B.ID AND FP7.CODE='code12' INNER JOIN b_iblock_element_property
    FPV7 ON FP7.ID=FPV7.IBLOCK_PROPERTY_ID AND FPV7.IBLOCK_ELEMENT_ID=BE.ID INNER
    JOIN b_iblock_property FP8 ON FP8.IBLOCK_ID=B.ID AND FP8.CODE='code15' INNER
    JOIN b_iblock_element_property FPV8 ON FP8.ID=FPV8.IBLOCK_PROPERTY_ID AND
    FPV8.IBLOCK_ELEMENT_ID=BE.ID INNER JOIN b_iblock_property FP9 ON
    FP9.IBLOCK_ID=B.ID AND FP9.CODE='code16' INNER JOIN b_iblock_element_property
    FPV9 ON FP9.ID=FPV9.IBLOCK_PROPERTY_ID AND FPV9.IBLOCK_ELEMENT_ID=BE.ID INNER
    JOIN b_iblock_property FP10 ON FP10.IBLOCK_ID=B.ID AND FP10.CODE='code18'
    INNER JOIN b_iblock_element_property FPV10 ON FP10.ID=FPV10.IBLOCK_PROPERTY_ID
    AND FPV10.IBLOCK_ELEMENT_ID=BE.ID INNER JOIN b_iblock_property FP11 ON
    FP11.IBLOCK_ID=B.ID AND FP11.CODE='code23' INNER JOIN
    b_iblock_element_property FPV11 ON FP11.ID=FPV11.IBLOCK_PROPERTY_ID AND
    FPV11.IBLOCK_ELEMENT_ID=BE.ID INNER JOIN b_iblock_property FP12 ON
    FP12.IBLOCK_ID=B.ID AND FP12.CODE='code26' INNER JOIN
    b_iblock_element_property FPV12 ON FP12.ID=FPV12.IBLOCK_PROPERTY_ID AND
    FPV12.IBLOCK_ELEMENT_ID=BE.ID INNER JOIN b_iblock_property FP13 ON
    FP13.IBLOCK_ID=B.ID AND FP13.CODE='code27' INNER JOIN
    b_iblock_element_property FPV13 ON FP13.ID=FPV13.IBLOCK_PROPERTY_ID AND
    FPV13.IBLOCK_ELEMENT_ID=BE.ID INNER JOIN b_iblock_property FP14 ON
    FP14.IBLOCK_ID=B.ID AND FP14.CODE='code32' INNER JOIN
    b_iblock_element_property FPV14 ON FP14.ID=FPV14.IBLOCK_PROPERTY_ID AND
    FPV14.IBLOCK_ELEMENT_ID=BE.ID INNER JOIN b_iblock_property FP15 ON
    FP15.IBLOCK_ID=B.ID AND FP15.CODE='code34' INNER JOIN
    b_iblock_element_property FPV15 ON FP15.ID=FPV15.IBLOCK_PROPERTY_ID AND
    FPV15.IBLOCK_ELEMENT_ID=BE.ID WHERE 1=1 AND B.ID IN (0,42) AND (
    (BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL) ) AND ((((
    (upper(BE.NAME) like upper('%acer7730G%') and BE.NAME is not null) )))) AND
    (((( (upper(FPV1.VALUE) like upper('%Intel%') and FPV1.VALUE is not null) AND
    (upper(FPV1.VALUE) like upper('%Core2Duo%') and FPV1.VALUE is not null) AND
    (upper(FPV1.VALUE) like upper('%T5850%') and FPV1.VALUE is not null) AND
    (upper(FPV1.VALUE) like upper('%2.16GHz%') and FPV1.VALUE is not null) ))))
    AND (((( (upper(FPV2.VALUE) like upper('%4096Mb%') and FPV2.VALUE is not null)
    AND ( (upper(FPV2.VALUE) like upper('%4Gb%') and FPV2.VALUE is not null) ) AND
    (upper(FPV2.VALUE) like upper('%DDRII%') and FPV2.VALUE is not null) )))) AND
    (((( (upper(FPV3.VALUE) like upper('%2x320Gb%') and FPV3.VALUE is not null)
    AND (upper(FPV3.VALUE) like upper('%5400rpm%') and FPV3.VALUE is not null) AND
    (upper(FPV3.VALUE) like upper('%SATA%') and FPV3.VALUE is not null) )))) AND
    (((( (upper(FPV4.VALUE) like upper('%17%') and FPV4.VALUE is not null) ))))
    AND (((( (upper(FPV5.VALUE) like upper('%??%') and FPV5.VALUE is not null)
    )))) AND (((( (upper(FPV6.VALUE) like upper('%64Mb%') and FPV6.VALUE is not
    null) AND ( (upper(FPV6.VALUE) like upper('%??%') and FPV6.VALUE is not null)
    AND (upper(FPV6.VALUE) like upper('%958Mb%') and FPV6.VALUE is not null) )))))
    AND (((( (upper(FPV7.VALUE) like upper('%??%') and FPV7.VALUE is not null)
    )))) AND (((( (upper(FPV8.VALUE) like upper('%Bluetooth%') and FPV8.VALUE is
    not null) AND (upper(FPV8.VALUE) like upper('%V2.0%') and FPV8.VALUE is not
    null) AND (upper(FPV8.VALUE) like upper('%EDR%') and FPV8.VALUE is not null)
    )))) AND (((( (upper(FPV9.VALUE) like upper('%4%') and FPV9.VALUE is not null)
    )))) AND (((( (upper(FPV10.VALUE) like upper('%??%') and FPV10.VALUE is not
    null) )))) AND (((( (upper(FPV11.VALUE) like upper('%??%') and FPV11.VALUE is
    not null) )))) AND (((( (upper(FPV12.VALUE) like upper('%??%') and FPV12.VALUE
    is not null) )))) AND (((( (upper(FPV13.VALUE) like upper('%WebCam%') and
    FPV13.VALUE is not null) AND (upper(FPV13.VALUE) like upper('%1,3Mpx%') and
    FPV13.VALUE is not null) )))) AND (((( (upper(FPV14.VALUE) like
    upper('%?????%') and FPV14.VALUE is not null) )))) AND ((((
    (upper(FPV15.VALUE) like upper('%12%') and FPV15.VALUE is not null) AND
    (upper(FPV15.VALUE) like upper('%???????%') and FPV15.VALUE is not null) ))))
    AND ((((BE.IBLOCK_ID = '42')))) AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO
    IS NULL) AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL))) AND
    ((((BE.ACTIVE='Y')))) AND ((BS.ID = 224)) ORDER BY BE.SORT asc , BE.ID desc
    LIMIT 0, 30

    Битрикс (http://dev.1c-bitrix.ru/community/forums/forum6/topic14898/)

    guest, 19 Декабря 2008

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

    −105.9

    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
    CREATE OR REPLACE FUNCTION hex_inc(in_str character, n integer)
    RETURNS character AS
    $BODY$
    DECLARE x varchar;
    BEGIN
    x=in_str;
    if (substring(in_str from n for n+1)='0') or (substring(in_str from n for n+1)='1') or (substring(in_str from n for n+1)='2')
    or (substring(in_str from n for n+1)='3') or (substring(in_str from n for n+1)='4') or (substring(in_str from n for n+1)='5')
    or (substring(in_str from n for n+1)='6') or (substring(in_str from n for n+1)='7') or (substring(in_str from n for n+1)='8')
    or (substring(in_str from n for n+1)='A') or (substring(in_str from n for n+1)='B') or (substring(in_str from n for n+1)='C')
    or (substring(in_str from n for n+1)='D') or (substring(in_str from n for n+1)='E') THEN
    x=raplace_char(n,x,chr((ascii(substring(in_str from n for n+1))+1))); -- inc() только для string
    ELSIF (substring(in_str from n for n+1)='9') THEN
    x=raplace_char(n,x,'A'); -- ну т.к. это уже 10
    ELSIF (substring(in_str from n for n+1)='F') THEN
    x=raplace_char(n,x,'0'); -- ну т.к. это +1 разряд :)
    x=hex_inc(x,n-1);
    END IF;
    RETURN x;
    END;
    $BODY$
    LANGUAGE 'plpgsql' VOLATILE
    COST 100;
    
    CREATE OR REPLACE FUNCTION raplace_char(n integer, in_str character, in_char character)
    RETURNS character AS
    $BODY$
    Begin
    IF ( n = char_length(in_str) ) THEN
    RETURN substring(in_str from 1 for n-1) || in_char;
    ELSIF (n = 1) THEN
    RETURN in_char || substring(in_str from 2 for char_length(in_str)-1) ;
    ELSE
    RETURN substring(in_str from 1 for n-1) || in_char || substring(in_str from n+1 for char_length(in_str)-n);
    END IF;
    END;
    $BODY$
    LANGUAGE 'plpgsql' STABLE
    COST 100;

    Язык PL/PgSQL
    Функция должна делать inc числу, записанному в строке в шестнадцатеричном виде

    guest, 12 Декабря 2008

    Комментарии (1)
  3. SQL / Говнокод #110

    +142.2

    1. 1
    2. 2
    3. 3
    4. 4
    IF LTRIM(RTRIM(LEN(ISNULL(@MandantTags,'')))) > 0
    BEGIN
    	SET @TagsSQL = 'INSERT INTO #MandantTmp (...
    END

    guest, 11 Декабря 2008

    Комментарии (0)
  4. SQL / Говнокод #74

    −152.7

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    SELECT FROM Staff
      CASE Sex
      WHEN 'M' THEN 'МужЫк!'
      WHEN 'F' THEN 'Баба'
      ELSE 'ЫЫЫыы?'

    препод сказал что елсе не будет выполняться изза ограничений целостности базы

    guest, 08 Декабря 2008

    Комментарии (4)
  5. SQL / Говнокод #24

    −136.4

    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
    SELECT *
    FROM (SELECT n.root, n.nodeid, n.name, UNIX_TIMESTAMP(m.createtime) AS
     updatetime, (SELECT COUNT(*) FROM fs_node WHERE sid = n.sid AND root = n.root
      AND cleft > n.cleft AND cright < n.cright AND clevel = n.clevel + 1 AND
       nodetype = 1) AS objcount FROM fs_node AS n, fs_node AS m WHERE n.sid = $ sid
        AND n.root IN (0, 1, 2, 3) AND n.nodetype = 0 AND m.nodeid =(SELECT MAX(
        nodeid) FROM fs_node WHERE sid = n.sid AND root = n.root AND cleft > n.cleft
         AND cright < n.cright AND clevel = n.clevel + 1 AND nodetype = 1) UNION ALL
         SELECT n.root, NULL, NULL, UNIX_TIMESTAMP(m.createtime) AS updatetime, (
         SELECT COUNT(*) FROM fs_node WHERE sid = $ sid AND root = n.root AND clevel
          = 1 AND nodetype = 1) AS objcount FROM (SELECT 0 AS root UNIONSELECT 1
           UNIONSELECT 2 UNIONSELECT 3) AS n, fs_node AS m WHERE m.nodeid =(SELECT
            MAX(nodeid) FROM fs_node WHERE sid = $ sid AND root = n.root AND clevel
             = 1 AND nodetype = 1)) AS tmp
    ORDER BY updatetime DESC

    guest, 27 Ноября 2008

    Комментарии (8)
  6. SQL / Говнокод #23

    −143.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)
  7. SQL / Говнокод #22

    +155

    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)