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

    +7

    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
    (case 
          when (ccc.company_ogrn=0 or ccc.company_ogrn='') 
          then '<span class=red>не заполнено, заполните в 1С</span>' else ccc.company_ogrn end ) 
          else ccc.company_ogrn end company_ogrn,
         case when ccc.company_prefix <> 4 then 
         (case when (ccc.company_kpp=0 or ccc.company_kpp='') 
         then '<span class=red>не заполнено, заполните в 1С</span>' 
         else ccc.company_kpp end ) else ccc.company_kpp end company_kpp,
        case when ccc.company_prefix = 0 then 
         '<span class=red>не заполнено, заполните в 1С</span>' 
         else ccp.prefix_name end prefix_name,
        case when ccc.company_jur_name = '' then 
         '<span class=red>не заполнено, заполните в 1С</span>' 
         else ccc.company_jur_name end company_jur_name,
        case when ccc.company_inn = '' then 
        '<span class=red>не заполнено, заполните в 1С</span>' 
        else ccc.company_inn end company_inn,
        case when company_jur_address = '' then 
        '<span class=red>не заполнено, заполните в 1С</span>' else 
        ccc.company_jur_address end company_jur_address,
        case when ccc.company_fact_address = '' then 
        '<span class=red>не заполнено, заполните в 1С</span>' else 
        ccc.company_fact_address end company_fact_address,
        case when ccc.company_post_address = '' then 
        '<span class=red>не заполнено, заполните в 1С</span>' else 
        ccc.company_post_address end company_post_address,

    Да, это часть MySql запроса.
    Верстка в SQL, Карл

    ixvil, 27 Сентября 2016

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

    0

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders1]') AND type in (N'U'))
      IF EXISTS (SELECT * FROM [dbo].[Orders1])
    BEGIN
       SELECT * FROM [dbo].[Orders1] where OrderID = 1
    END

    Кусок кода из живого проекта в котором очень сильно борятся за перформенс.

    foxocoder, 26 Сентября 2016

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

    +317

    1. 1
    2. 2
    3. 3
    CREATE FUNCTION this_function_check_first_payment_under_agreement_without_previous_periods
    (
    ....

    Ну вот сразу всё понятно)

    Onimys, 17 Августа 2016

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

    +3

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    8. 8
    CREATE OR REPLACE PACKAGE BODY REPORT."PKG_COMMIT" 
    	IS
    	PROCEDURE P_COMMIT
    		IS
    	BEGIN
    		COMMIT;
    	END;
    END PKG_COMMIT;

    откопал чудный пакет

    Lokich, 08 Августа 2016

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

    +12

    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
    SELECT
    real_day, 1 start_mon, TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE ('{date}','dd.mm.yyyy')),'dd')) end_mon,
    (CASE WHEN d.t1_aup_f IS NULL THEN (
      CASE WHEN (lag(sum(d.t1_aup_f),1) over (order by d.real_day)) IS NULL THEN (
       CASE WHEN (lag(sum(d.t1_aup_f),2) over (order by d.real_day)) IS NULL THEN (
        CASE WHEN (lag(sum(d.t1_aup_f),3) over (order by d.real_day)) IS NULL THEN (
         CASE WHEN (lag(sum(d.t1_aup_f),4) over (order by d.real_day)) IS NULL THEN (
          CASE WHEN (lag(sum(d.t1_aup_f),5) over (order by d.real_day)) IS NULL THEN (
           CASE WHEN (lag(sum(d.t1_aup_f),6) over (order by d.real_day)) IS NULL THEN (
            CASE WHEN (lag(sum(d.t1_aup_f),7) over (order by d.real_day)) IS NULL THEN (
             CASE WHEN (lag(sum(d.t1_aup_f),8) over (order by d.real_day)) IS NULL THEN (
              CASE WHEN (lag(sum(d.t1_aup_f),9) over (order by d.real_day)) IS NULL THEN (
               CASE WHEN (lag(sum(d.t1_aup_f),10) over (order by d.real_day)) IS NULL THEN (
                CASE WHEN (lag(sum(d.t1_aup_f),11) over (order by d.real_day)) IS NULL THEN (
                 CASE WHEN (lag(sum(d.t1_aup_f),12) over (order by d.real_day)) IS NULL THEN (
                  CASE WHEN (lag(sum(d.t1_aup_f),13) over (order by d.real_day)) IS NULL THEN (
                   CASE WHEN (lag(sum(d.t1_aup_f),14) over (order by d.real_day)) IS NULL THEN (
                    CASE WHEN (lag(sum(d.t1_aup_f),15) over (order by d.real_day)) IS NULL THEN (
                     CASE WHEN (lag(sum(d.t1_aup_f),16) over (order by d.real_day)) IS NULL THEN (
                      CASE WHEN (lag(sum(d.t1_aup_f),17) over (order by d.real_day)) IS NULL THEN (
                       CASE WHEN (lag(sum(d.t1_aup_f),18) over (order by d.real_day)) IS NULL THEN (
                        CASE WHEN (lag(sum(d.t1_aup_f),19) over (order by d.real_day)) IS NULL THEN (
                         CASE WHEN (lag(sum(d.t1_aup_f),20) over (order by d.real_day)) IS NULL THEN (
                          CASE WHEN (lag(sum(d.t1_aup_f),21) over (order by d.real_day)) IS NULL THEN (
                           CASE WHEN (lag(sum(d.t1_aup_f),22) over (order by d.real_day)) IS NULL THEN (
                            CASE WHEN (lag(sum(d.t1_aup_f),23) over (order by d.real_day)) IS NULL THEN (
                             CASE WHEN (lag(sum(d.t1_aup_f),24) over (order by d.real_day)) IS NULL THEN (
                              CASE WHEN (lag(sum(d.t1_aup_f),25) over (order by d.real_day)) IS NULL THEN (
                               CASE WHEN (lag(sum(d.t1_aup_f),26) over (order by d.real_day)) IS NULL THEN (
                                CASE WHEN (lag(sum(d.t1_aup_f),27) over (order by d.real_day)) IS NULL THEN (
                                 CASE WHEN (lag(sum(d.t1_aup_f),28) over (order by d.real_day)) IS NULL THEN (
                                  CASE WHEN (lag(sum(d.t1_aup_f),29) over (order by d.real_day)) IS NULL THEN
                                   lag(sum(d.t1_aup_f),30) over (order by d.real_day )
                                  ELSE (lag(sum(d.t1_aup_f),29) over (order by d.real_day)) END)
                                 ELSE (lag(sum(d.t1_aup_f),28) over (order by d.real_day)) END)
                                ELSE (lag(sum(d.t1_aup_f),27) over (order by d.real_day)) END)
                               ELSE (lag(sum(d.t1_aup_f),26) over (order by d.real_day)) END)
                              ELSE (lag(sum(d.t1_aup_f),25) over (order by d.real_day)) END)
                             ELSE (lag(sum(d.t1_aup_f),24) over (order by d.real_day)) END)
                            ELSE (lag(sum(d.t1_aup_f),23) over (order by d.real_day)) END)
                           ELSE (lag(sum(d.t1_aup_f),22) over (order by d.real_day)) END)
                          ELSE (lag(sum(d.t1_aup_f),21) over (order by d.real_day)) END)
                         ELSE (lag(sum(d.t1_aup_f),20) over (order by d.real_day)) END)
                        ELSE (lag(sum(d.t1_aup_f),19) over (order by d.real_day)) END)
                       ELSE (lag(sum(d.t1_aup_f),18) over (order by d.real_day)) END)
                      ELSE (lag(sum(d.t1_aup_f),17) over (order by d.real_day)) END)
                     ELSE (lag(sum(d.t1_aup_f),16) over (order by d.real_day)) END)
                    ELSE (lag(sum(d.t1_aup_f),15) over (order by d.real_day)) END)
                   ELSE (lag(sum(d.t1_aup_f),14) over (order by d.real_day)) END)
                  ELSE (lag(sum(d.t1_aup_f),13) over (order by d.real_day)) END)
                 ELSE (lag(sum(d.t1_aup_f),12) over (order by d.real_day)) END)
                ELSE (lag(sum(d.t1_aup_f),11) over (order by d.real_day)) END)
               ELSE (lag(sum(d.t1_aup_f),10) over (order by d.real_day)) END)
              ELSE (lag(sum(d.t1_aup_f),9) over (order by d.real_day)) END)
             ELSE (lag(sum(d.t1_aup_f),8) over (order by d.real_day)) END)
            ELSE (lag(sum(d.t1_aup_f),7) over (order by d.real_day)) END)
           ELSE (lag(sum(d.t1_aup_f),6) over (order by d.real_day)) END)
          ELSE (lag(sum(d.t1_aup_f),5) over (order by d.real_day)) END)
         ELSE (lag(sum(d.t1_aup_f),4) over (order by d.real_day)) END)
        ELSE (lag(sum(d.t1_aup_f),3) over (order by d.real_day)) END)
       ELSE (lag(sum(d.t1_aup_f),2) over (order by d.real_day)) END)
      ELSE (lag(sum(d.t1_aup_f),1) over (order by d.real_day)) END)
    ELSE d.t1_aup_f END) gf_1,
    ........

    Фул: http://paste.org.ru/?zlc3b8
    via https://habrahabr.ru/post/305926/#comment_9706606

    package, 19 Июля 2016

    Комментарии (9)
  6. 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)
  7. SQL / Говнокод #20226

    +15

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

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

    boconon, 17 Июня 2016

    Комментарии (6)
  8. 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)
  9. 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)
  10. SQL / Говнокод #20121

    +15

    1. 1
    id INT(10)

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

    valmus, 02 Июня 2016

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