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

    −153

    1. 1
    2. 2
    3. 3
    SELECT COUNT(*) INTO v_inserted_data FROM f2s.column_map 
WHERE old_id='TYPE='||i.pk_0 AND migration_id='fin95_aral' 
AND fndr_table='R_ALLOWABLE' AND UPPER(NVL(fndr_constraint,'null'))=UPPER('NULL');

    Oracle 10.2 PL/SQL. продакшн код.
    Условие поиска NULL убило...

    slbsomeone, 11 Октября 2011

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

  2. SQL / Говнокод #8155

    −113

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    CURSOR v_cursor IS
  SELECT
   ...
   NVL(DECODE(record_status,'CURRENT',1,0),'1')       r_grain_ro_1_ins_64,
  ...

    Это в продакшн коде.
    Oracle 10.2, PL/SQL

    slbsomeone, 11 Октября 2011

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

  3. SQL / Говнокод #8122

    −107

    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
    SELECT SUBSTR(exp, 1, 20) "expression", (CASE WHEN SUBSTR(exp, 6, 1) = '+' THEN (CASE WHEN SUBSTR(exp, 3, 1) = '+' THEN TO_NUMBER(SUBSTR(exp, 1, 2))
 + TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE WHEN SUBSTR(exp, 3, 1) = '-' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) - TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE 
WHEN SUBSTR(exp, 3, 1) = '*' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) * TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE WHEN SUBSTR(exp, 3, 1) = '/' THEN 
TO_NUMBER(SUBSTR(exp, 1, 2)) / TO_NUMBER(SUBSTR(exp, 4, 2)) END END END END) + TO_NUMBER(SUBSTR(exp, 7, 2)) ELSE CASE WHEN SUBSTR(exp, 6, 1) = '-' 
THEN (CASE WHEN SUBSTR(exp, 3, 1) = '+' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) + TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE WHEN SUBSTR(exp, 3, 1) = '-' THEN 
TO_NUMBER(SUBSTR(exp, 1, 2)) - TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE WHEN SUBSTR(exp, 3, 1) = '*' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) * 
TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE WHEN SUBSTR(exp, 3, 1) = '/' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) / TO_NUMBER(SUBSTR(exp, 4, 2)) END END END END) 
- TO_NUMBER(SUBSTR(exp, 7, 2)) ELSE CASE WHEN SUBSTR(exp, 6, 1) = '*' THEN (CASE WHEN SUBSTR(exp, 3, 1) = '+' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) + 
TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE WHEN SUBSTR(exp, 3, 1) = '-' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) - TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE WHEN 
SUBSTR(exp, 3, 1) = '*' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) * TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE WHEN SUBSTR(exp, 3, 1) = '/' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) / 
TO_NUMBER(SUBSTR(exp, 4, 2)) END END END END) * TO_NUMBER(SUBSTR(exp, 7, 2)) ELSE CASE WHEN SUBSTR(exp, 6, 1) = '/' THEN (CASE WHEN SUBSTR(exp, 3, 1) = '+' THEN 
TO_NUMBER(SUBSTR(exp, 1, 2)) + TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE WHEN SUBSTR(exp, 3, 1) = '-' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) - TO_NUMBER(SUBSTR(exp, 4, 2)) 
ELSE CASE WHEN SUBSTR(exp, 3, 1) = '*' THEN TO_NUMBER(SUBSTR(exp, 1, 2)) * TO_NUMBER(SUBSTR(exp, 4, 2)) ELSE CASE WHEN SUBSTR(exp, 3, 1) = '/' THEN 
TO_NUMBER(SUBSTR(exp, 1, 2)) / TO_NUMBER(SUBSTR(exp, 4, 2)) END END END END) / TO_NUMBER(SUBSTR(exp, 7, 2)) END END END END) "result" FROM lab3_2;

    Сидел я на прошлой лабе и делал значит задания по Oracle и вот последнее задание было таким:

    "Дана таблица с единственной колонкой (строкового типа) с выражениями вида:’xx*xx*xx’ где xx – символы цифр 0..9, * - один из символов математических операций: *+-/ Например, таким выражениями являются ‘01*23+34’ ‘32+13-12’ и т.д. Требуется создать таблицу и заполнить ее строками содержащими такие выражения (не менее 5 строк), составить оператор SELECT который используя данные из этой таблицы выведет строки вида ‘<исходное выражение>=<вычисленный результат>’. Задание выполнить без учета приоритетности операций."

    Вооружившись Notepad++ я составил вот такое вот запросище, которое работает между прочим :) Для повторения эксперимента нужно создать таблицу lab3_2 с полем exp строкового типа, ну и внести в нее пару каких-нибудь выражений.

    10a10b1s, 08 Октября 2011

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

  4. SQL / Говнокод #8112

    −853

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    8. 8
    SELECT a.*,r.rating_count,r.rating_sum,cc.params AS catparams,cc.title AS cattle,cc.alias AS category_alias FROM gn_content AS a INNER JOIN gn_categories AS cc ON cc.id = a.catid LEFT JOIN gn_content_rating AS r ON r.content_id = a.id WHERE a.state = 1 AND cc.published = 1 

                AND ( a.publish_up = '2011-10-07 21:02:32' OR a.publish_up <= '2011-10-07 18:02:38' ) 

                AND ( a.publish_down = '2011-10-07 21:02:32' OR a.publish_down >= '2011-10-07 18:02:38' ) 

                AND (cc.id = 105 OR cc.id = 106 OR cc.id = 107 OR cc.id = 108 OR cc.id = 109 OR cc.id = 110 OR cc.id = 111 OR cc.id = 106 OR cc.id = 107 OR cc.id = 108 OR cc.id = 109 OR cc.id = 110 OR cc.id = 111) 
                ORDER BY a.created DESC

    Это код модуля adinews2 для joomla

    Используются всегда только 2 даты: начало, конец

    uadeveloper, 07 Октября 2011

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

  5. SQL / Говнокод #8012

    −857

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    # Query_time: 1  Lock_time: 0  Rows_sent: 50807  Rows_examined: 160282
SELECT  `node`.id, (
	TO_DAYS( NOW( ) ) - TO_DAYS(  `node`.datecreate )
	) AS countDay, (
	`rating_node`.up -  `rating_node`.down
	) AS countRating, COUNT(  `comments`.cid ) AS countComments,  `node`.view AS countViews
	FROM node
	LEFT JOIN  `rating_node` ON  `rating_node`.node_id =  `node`.id
	LEFT JOIN  `comments` ON  `comments`.nid =  `node`.id
	GROUP BY  `node`.id;

    фрилансеры...

    Venomous, 28 Сентября 2011

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

  6. SQL / Говнокод #7999

    −853

    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
    Create Proc char32add1(  
        @char32 nvarchar(1) =Null, 
	@rez nvarchar(1) output, 
	@rez1 int=-1 output 
                   )  
As 
  Begin 
if (@char32 is Null) return -1 
Select @rez1=0 
if (@char32 = "0") Begin Select  @rez="1" 
 return 0 End 
if (@char32 = "1")  Begin Select  @rez="2"  
return 0 End 
if (@char32 = "2")  Begin Select  @rez="3"  
return 0 End 
if (@char32 = "3")  Begin Select  @rez="4"  
return 0 End 
if (@char32 = "4")  Begin Select  @rez="5" return 0 End 
if (@char32 = "5")  Begin Select  @rez="6" return 0 End 
if (@char32 = "6")  Begin Select  @rez="7" return 0 End 
if (@char32 = "7")  Begin Select  @rez="8" return 0 End 
if (@char32 = "8")  Begin Select  @rez="9" return 0 End 
if (@char32 = "9")  Begin Select  @rez="A" return 0 End 
if (@char32 = "a" or @char32 = "A" )  Begin Select  @rez="B" return 0 End 
if (@char32 = "b" or @char32 = "B" )  Begin Select  @rez="C" return 0 End 
if (@char32 = "c" or @char32 = "C" )  Begin Select  @rez="D" return 0 End 
if (@char32 = "d" or @char32 = "D" )  Begin Select  @rez="E" return 0 End 
if (@char32 = "e" or @char32 = "E" )  Begin Select  @rez="F" return 0 End 
if (@char32 = "f" or @char32 = "F" )  Begin Select  @rez="G" return 0 End 
if (@char32 = "g" or @char32 = "G" )  Begin Select  @rez="H" return 0 End 
if (@char32 = "h" or @char32 = "H" )  Begin Select  @rez="I" return 0 End 
if (@char32 = "i" or @char32 = "I" )  Begin Select  @rez="J" return 0 End 
if (@char32 = "j" or @char32 = "J" )  Begin Select  @rez="K" return 0 End 
if (@char32 = "k" or @char32 = "K" )  Begin Select  @rez="L" return 0 End 
if (@char32 = "l" or @char32 = "L" )  Begin Select  @rez="M" return 0 End 
if (@char32 = "m" or @char32 = "M" )  Begin Select  @rez="N" return 0 End 
if (@char32 = "n" or @char32 = "N" )  Begin Select  @rez="O" return 0 End 
if (@char32 = "o" or @char32 = "O" )  Begin Select  @rez="P" return 0 End 
if (@char32 = "p" or @char32 = "P" )  Begin Select  @rez="Q" return 0 End 
if (@char32 = "q" or @char32 = "Q" )  Begin Select  @rez="R" return 0 End 
if (@char32 = "r" or @char32 = "R" )  Begin Select  @rez="S" return 0 End 
if (@char32 = "s"or @char32 = "S" )  Begin Select  @rez="T" return 0 End 
if (@char32 = "t" or @char32 = "T" )  Begin Select  @rez="U" return 0 End 
if (@char32 = "u" or @char32 = "U" )  Begin Select  @rez="V" return 0 End 
Select @rez1=1 
if (@char32 = "v" or @char32 = "V" )  Begin Select  @rez="0" return 1 End 
 
End

    CPlusPlusGovno, 27 Сентября 2011

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

  7. SQL / Говнокод #7933

    −862

    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
    SELECT POL_PREMIUM
   INTO i_csp
   FROM policy
   WHERE 
    policy_key = i_polkey AND 
    pol_status_cde = 1;
   
 SELECT POL_CLIENT_CDE
     INTO i_clientno
     FROM policy
   WHERE policy_key = i_polkey
   AND pol_status_cde = 1;
   
   SELECT POL_PLAN_CDE
     INTO i_plan
     FROM policy
   WHERE policy_key = i_polkey
   AND pol_status_cde = 1;

SELECT 110
      INTO i_SecAllrks
      FROM DUAL;

    Запятые и знаки инициализации переменных придумали для трусов =) Имхо. Не вкурила зачем так делать.

    Dragon, 22 Сентября 2011

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

  8. SQL / Говнокод #7927

    −530

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    SELECT ID, 
INTO var
FROM tbl_ref
WHERE 
   TO_DATE (TO_CHAR (sysdate, 'MM-DD-YYYY'), 'MM-DD-YYYY') BETWEEN VAL_EFF_FROM_DATE AND VAL_EFF_TO_DATE

    Индусы любят строки! А особенно конвертить даты в строки и обратно!

    Dragon, 21 Сентября 2011

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

  9. SQL / Говнокод #7845

    −112

    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
    CREATE TRIGGER after_update_limit AFTER UPDATE ON limits
FOR EACH ROW
BEGIN
	SET @annual_normative = NULL;
	SET @ud_use = NULL;
	SET @ud_disposal = NULL;
	SET @placed_deposited = NULL;
	SET @placed_disposal = NULL;
	SET @custom1_disposal = NULL;
	SET @custom1_deposited = NULL;
	SET @custom1_use = NULL;
	SET @custom1_neutralization = NULL;
	SET @custom2_disposal = NULL;
	SET @custom2_deposited = NULL;
	SET @custom2_use = NULL;
	SET @custom2_neutralization = NULL;
	SET @custom3_disposal = NULL;
	SET @custom3_deposited = NULL;
	SET @custom3_use = NULL;
	SET @custom3_neutralization = NULL;
	SET @custom4_disposal = NULL;
	SET @custom4_deposited = NULL;
	SET @custom4_use = NULL;
	SET @custom4_neutralization = NULL;
	SET @custom5_disposal = NULL;
	SET @custom5_deposited = NULL;
	SET @custom5_use = NULL;
	SET @custom5_neutralization = NULL;
	SELECT
		SUM(annual_normative), SUM(ud_use), SUM(ud_disposal), SUM(placed_deposited), SUM(placed_disposal),
		SUM(custom1_disposal), SUM(custom1_deposited), SUM(custom1_use), SUM(custom1_neutralization),
		SUM(custom2_disposal), SUM(custom2_deposited), SUM(custom2_use), SUM(custom2_neutralization),
		SUM(custom3_disposal), SUM(custom3_deposited), SUM(custom3_use), SUM(custom3_neutralization),
		SUM(custom4_disposal), SUM(custom4_deposited), SUM(custom4_use), SUM(custom4_neutralization),
		SUM(custom5_disposal), SUM(custom5_deposited), SUM(custom5_use), SUM(custom5_neutralization)
	INTO
		@annual_normative, @ud_use, @ud_disposal, @placed_deposited, @placed_disposal,
		@custom1_disposal, @custom1_deposited, @custom1_use, @custom1_neutralization,
		@custom2_disposal, @custom2_deposited, @custom2_use, @custom2_neutralization,
		@custom3_disposal, @custom3_deposited, @custom3_use, @custom3_neutralization,
		@custom4_disposal, @custom4_deposited, @custom4_use, @custom4_neutralization,
		@custom5_disposal, @custom5_deposited, @custom5_use, @custom5_neutralization
	FROM limits
	WHERE id_enterprise = NEW.id_enterprise;
	UPDATE limits_total
	SET
		annual_normative = @annual_normative, ud_use = @ud_use, ud_disposal = @ud_disposal, placed_deposited = @placed_deposited, placed_disposal = @placed_disposal,
		custom1_disposal = @custom1_disposal, custom1_deposited = @custom1_deposited, custom1_use = @custom1_use, custom1_neutralization = @custom1_neutralization,
		custom2_disposal = @custom2_disposal, custom2_deposited = @custom2_deposited, custom2_use = @custom2_use, custom2_neutralization = @custom2_neutralization,
		custom3_disposal = @custom3_disposal, custom3_deposited = @custom3_deposited, custom3_use = @custom3_use, custom3_neutralization = @custom3_neutralization,
		custom4_disposal = @custom4_disposal, custom4_deposited = @custom4_deposited, custom4_use = @custom4_use, custom4_neutralization = @custom4_neutralization,
		custom5_disposal = @custom5_disposal, custom5_deposited = @custom5_deposited, custom5_use = @custom5_use, custom5_neutralization = @custom5_neutralization
	WHERE id_enterprise = NEW.id_enterprise
	LIMIT 1;
END;

    Сперва можно посмеяться, а затем подскажите, пжл, как тоже самое написать по человечески ???

    DarkThinker, 12 Сентября 2011

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

  10. SQL / Говнокод #7820

    −110

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    $sql_info = "SELECT * FROM accessories";
$query_info = mysql_query($sql_info);
while($row_info=mysql_fetch_array($query_info)) {
    $found_something=1;
}

    Проверка наличия записей в БД

    mikhail-spb, 09 Сентября 2011

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