SQL — Говнокод.ру

SQL / Говнокод #118

−370.9

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
36
37
38
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)

SQL / Говнокод #74

−417.7

1
2
3
4
5

SELECT FROM Staff
  CASE Sex
  WHEN 'M' THEN 'МужЫк!'
  WHEN 'F' THEN 'Баба'
  ELSE 'ЫЫЫыы?'

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

guest, 08 Декабря 2008

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

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

−407.3

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
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

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

SQL / Говнокод #22

−1094

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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

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

Говнокод: по колено в коде.

SQL / Говнокод #118

SQL / Говнокод #110

SQL / Говнокод #74

SQL / Говнокод #24

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

SQL / Говнокод #22