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

    +15

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    create or replace FUNCTION ISNUMBER(test_str IN VARCHAR) RETURN INTEGER AS
    i INTEGER;
    num NUMBER;
    BEGIN
     select DECODE(INSTR(TRANSLATE(test_str,
     'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X'),0,1,0) INTO i
     FROM DUAL;
    RETURN i;
    END;
    /

    классический isChislo()

    testToBeRemoved, 21 Июня 2016

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

    +15

    1. 1
    Amount * VALUE('0.'& TEXT(Vat__c) )

    Формула для рассчета % VAT из суммы (Amount) и процента налога (Vat__c). Особенно хорошо работает с процентами до 10.

    boconon, 17 Июня 2016

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

    +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
    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
    create procedure [dbo].[GetXML] (
    	@ExternalId		varchar(255)
    )	
    as
    begin
    	declare @Contained int = 2;
    	declare @Sent int = 3;
    	declare @ResultAdmissionSubmissionNO int = 76;
    	declare @dcStateSent int = 7;
    	declare @dcStateInProgress int = 1;
    	declare @dcStateFinishedWithError int = 2;
    	declare @dcStateFinished int = 3;
    
    with ExternalIdMain as (
    select 
    	ExternalId as ExternalId,
    	dc.DocCircId as GroupId,
    	dc.DocCircId as DocCircId
    	from 
    	[dbo].[gate_DocCirculations] as gdc
    	inner join [dbo].[DocCirculations] as dc on dc.DocCircId = gdc.DocCircId and not exists 
    		(select TOP 1 1 from [dbo].[DocCircConnection] as dcci where dcci.Parent = dc.DocCircId or dcci.Child = dc.DocCircId )
    	where ExternalId = @ExternalId and 
    		dc.StateId in (@dcStateSent, @dcStateInProgress, @dcStateFinishedWithError, @dcStateFinished)
    	union all
    select 
    	ExternalId as ExternalId,
    	dcc0.Parent as GroupId,
    	dcc0.Child as DocCircId
    	from 
    	--dcc0.Child is leaf
    	[dbo].[DocCircConnection] as dcc0 
    	inner join [dbo].[DocCircConnection] as dcc1 on dcc0.Parent = dcc1.Child and dcc0.ConnectionType = @Contained and dcc1.ConnectionType = @Sent
    	inner join [dbo].[gate_DocCirculations] as gdc on dcc1.Parent = gdc.DocCircId
    	where ExternalId = @ExternalId
    ),
    ExternalIdStates as (
    	select 
    		eim.ExternalId as ExternalId,
    		eim.GroupId as GroupId, 
    		dcsg.StateId as GroupState, 
    		dcsg.[Description] as GroupStateDescription, 
    		eim.DocCircId as DocCircId, 
    		dcs.StateId as DocCircState, 
    		dcs.[Description] as DocCircStateDescription, 
    		dc.timeUpdate as [Date] 
    		from 
    	ExternalIdMain as eim
    	inner join dbo.DocCirculations as dc on eim.DocCircId = dc.DocCircId
    	inner join dbo.DocCirculations as dcg on eim.GroupId = dcg.DocCircId
    	inner join dbo.DocCircStates as dcs on dcs.StateId = dc.StateId
    	inner join dbo.DocCircStates as dcsg on dcsg.StateId = dcg.StateId
    ),
    ExternalIdFull as (
    select
    	ExternalId as ExternalId,
    	GroupId as GroupId,
    	GroupState as GroupState,
    	GroupStateDescription as GroupStateDescription,
    	eis.DocCircId as DocCircId,
    	DocCircState as DocCircState,
    	DocCircStateDescription as DocCircStateDescription,
    	[Date] as [Date],
    	[Filename] as [Filename]
    from 
    ExternalIdStates as eis
    left join dbo.Transactions as t on eis.DocCircId = t.DocCircId and t.idTranstype = @ResultAdmissionSubmissionNO
    left join dbo.Docs as d on t.idTransaction = d.idTransaction
    left join dbo.Contents as c on d.idContent = c.idContent
    )
    select ExternalId as "@Value",
    (
    	select GroupId as "@GroupId", Max(GroupState) as "@GroupState", Max(GroupStateDescription) as "@GroupStateDescription",
    	(
    		select DocCircId as "@DocCircId", Max(DocCircState) as "@DocCircState", Max(DocCircStateDescription) as "@DocCircStateDescription", Max([Date]) as "@Date",
    		(
    			--"case when" for null filenames
    			case when 
    						(exists (select TOP 1 [Filename] from ExternalIdFull as eif3 where eif3.DocCircId = eif2.DocCircId and [Filename] is not null))
    				then (select [Filename] as "@Value" from ExternalIdFull as eif3 where eif3.DocCircId = eif2.DocCircId FOR XML PATH('Filename'), type) 
    				else N'' end
    		)
    		from ExternalIdFull as eif2 where eif2.GroupId = eif1.GroupId group by DocCircId FOR XML PATH('DocCirc'), type
    	)
    	from ExternalIdFull as eif1 where eif1.ExternalId = eif0.ExternalId group by GroupId FOR XML PATH('Group'), type
    )
    from ExternalIdFull as eif0 group by ExternalId FOR XML PATH('ExternalId'), Root('Root')
    end

    laMer007, 15 Июня 2016

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

    +6

    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
    if P_ID_STANDART is null then -- Это отвязка
    
      
      MANAGER_OBJECT_DSD.SET_RELATION_VARIANT(V_ID_OBJECT,ID_VARIANT,P_ID_STANDART,P_USER_NM);
      
     
      
      
      
    
    
    else -- Иначе привязка
    
    
      MANAGER_OBJECT_DSD.SET_RELATION_VARIANT(V_ID_OBJECT,ID_VARIANT,P_ID_STANDART,P_USER_NM);
      
    
      
      
    
    end if;

    кусок PL/SQL процедуры. форматирование сохранено.

    для чего нужно было вставлять столько переносов строк мне вообще непонятно.

    Lokich, 03 Июня 2016

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

    +15

    1. 1
    id INT(10)

    Десятка для базы вещей Steam предметов

    valmus, 02 Июня 2016

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

    +9

    1. 1
    (CASE WHEN "order".payment_type = 1 AND payed = 0 THEN 0 ELSE 1 END) = 1

    Одно из индусских условий в WHERE. Выражение вполне можно сократить до такого:

    ("order".payment_type <> 1 OR payed > 0)
    или такого:
    NOT ("order".payment_type = 1 AND payed = 0)

    jbot, 31 Мая 2016

    Комментарии (79)
  7. SQL / Говнокод #20092

    +21

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    8. 8
    SELECT AVG(sell) 
    FROM table_name
    WHERE id IN (
      SELECT id
      FROM table_name
      WHERE /* тут какое-то большое условие */
      ORDER BY day
    )

    Настоящий индус

    jbot, 30 Мая 2016

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

    +1

    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
    Declare
    alph Varchar2(26)   := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    ch   Varchar2(1);
    
    Begin
        dbms_output.put_line('Starting search: ' || srch);
        For i In 1..26 Loop
            
        Select Substr(alph,i,1) Into ch From dual;
    
            with 
              v_tab_col as (
            Select '"' || c.Table_Name || '"' As Table_Name,
                   '"' || c.Column_Name || '"' As Column_Name,
                   '"' || c.Owner || '"' As Owner,
                   Row_Number() Over(Partition By c.Owner, c.Table_Name Order By c.Column_Id) As Rn
              From Dba_Tab_Columns c, Dba_Objects o
            Where Data_Type In ('CHAR', 'VARCHAR2')
               And c.Table_Name = o.Object_Name
               And o.Object_Type = 'TABLE'
               And o.Owner = c.Owner
               And o.object_name Like ch || '%' -- checking by letter

    Циклическая выборка таблиц, начинающихся на букву алфавита, в каждом следующем прогоне берется следующая.

    eggman, 26 Мая 2016

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

    +2

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    8. 8
    (
            SELECT IFNULL(
              (
                IFNULL((SELECT SUM(`sale`.`credited`) FROM `sale` WHERE `sale`.`client_id` = `user`.`id` ), 0)
                -
                IFNULL((SELECT SUM(`sale`.`shot`) FROM `sale` WHERE `sale`.`client_id` = `user`.`id` ), 0)
                ), 0)
    )

    Супер подсчет.

    govnokoderphp, 09 Мая 2016

    Комментарии (30)
  10. SQL / Говнокод #19850

    +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
    SELECT 'январь' mes, a.datogt, gr.date_ogt datogt_, a.id_grafik,
               a.date_ogt dat_zam, 0 pr_zam, TO_CHAR(gr.date_ogt, 'DD') dat_zam_,
               0 pr_zam_, a.date_inp dat_nach, 0 pr_nach,
               TO_CHAR(gr.date_inp, 'DD') dat_nach_, 0 pr_nach_
          FROM (SELECT id_grafik, TO_CHAR(date_inp, 'DD') date_inp,
                        TO_CHAR(date_ogt, 'DD') date_ogt, date_ogt datogt, god,
                        TO_CHAR(date_ogt, 'MM') mon
                   FROM protokol p
                  WHERE god = p_god
                    AND TO_NUMBER(TO_CHAR(date_inp, 'MM')) = 01
                    AND pr_protokol = 1
                    AND flag_a = 1) a
         INNER JOIN protokol gr
            ON gr.god = a.god
           AND TO_CHAR(gr.date_ogt, 'mm') = a.mon
           AND gr.pr_protokol = 2
           AND flag_a = 1

    И так 12 раз от января до декабря

    raupe, 20 Апреля 2016

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