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

    −143

    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
    IF @Detail is not null
      AND (SELECT
            COUNT(AppraisalResponseID)
            FROM AppraisalResponseHistory
            WHERE AppraisalResponseID = @AppraisalResponseID) >0
      BEGIN
            INSERT INTO AppraisalResponseHistory 
            ( AppraisalResponseID, 
                  PermissionID, 
                  AppraisalTypeQuestionPromptID, 
                  Detail, 
                  ModifiedDate, 
                  PersonID )
      
            SELECT AppraisalResponse.ID, 
                  AppraisalResponse.PermissionID,
                   AppraisalResponse.AppraisalTypeQuestionPromptID,
                   AppraisalResponse.Detail, 
                  AppraisalResponse.ModifiedDate,
                  PersonID
            FROM AppraisalResponse
            WHERE AppraisalResponse.ID=@AppraisalResponseID AND AppraisalResponse.Detail<>@Detail
      
      END
ELSE
      IF COALESCE(@Detail, '') = ''
--if the reviewer is inserting a blank record after having filled in a response, insert into history
--if the blank record is the place holder, do not insert into history
      AND (SELECT
            COUNT(AppraisalResponseID)
            FROM AppraisalResponseHistory
            WHERE AppraisalResponseID = @AppraisalResponseID) >0
      BEGIN
            INSERT INTO AppraisalResponseHistory 
            ( AppraisalResponseID, 
                  PermissionID, 
                  AppraisalTypeQuestionPromptID, 
                  Detail, 
                  ModifiedDate, 
                  PersonID )
      
            SELECT AppraisalResponse.ID, 
                  AppraisalResponse.PermissionID,
                   AppraisalResponse.AppraisalTypeQuestionPromptID,
                   AppraisalResponse.Detail, 
                  AppraisalResponse.ModifiedDate,
                  PersonID
            FROM AppraisalResponse
            WHERE AppraisalResponse.ID=@AppraisalResponseID AND AppraisalResponse.Detail<>@Detail
END

    If @Detail is not null or blank, I need to check if AppraisalResponse.Detail is blank or null (- I think this is where I am running into so much trouble). If it is blank or null then
    I need to check if there is already a record in AppraisalHistory with this ID. If there is, I need to insert the blank record to history, otherwise not.

    If @Detail is null.
    I need to check if there is already a record in Appraisal History with this ID. If there is, I need to insert the blank record to history, otherwise not.

    Даже описание скопипащено

    3.14159265, 23 Февраля 2011

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

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

    −858

    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
    INSERT INTO actpr (userid,orgid,grupid,grupa, sum,abo_sum,;
	req,atbilde,kods,time,requestid,pakalpojum);
	value(Grozs .userid, Grozs .orgid,Grozs .grupid,Grozs .grupa,Grozs.sum ,0, ;
	Grozs .req,Grozs .atbilde, Grozs .kods,;
	this.time,this.requestId ,Grozs .pakalpojum)

replace ;
	tarifs WITH  Grozs .tarifs ,;
	aid with Grozs .aid,;
	param with Grozs .param,;
	ip with this.ip,;
	atlikum WITH konti.extra_sum - Grozs.sum ,;
	abo_atlik WITH konti.abo_sum, ;
	old_atlik WITH konti.old_sum ,;
	sessionid  WITH this.sessionid,;
	veids with Grozs .veids,;
	stan_cena WITH Grozs .stan_cena,;
	pvn WITH Grozs .pvn,;
	next_sum WITH 0,;
	next_atlik WITH konti.next_sum;
	 IN  actpr

    Написано на FoxPro (!)
    Незнаю даже что написать...

    lurseg, 18 Февраля 2011

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

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

    −860

    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
    CREATE OR REPLACE VIEW PR_CCC_QP_TRADES AS
select "INT_MSG_ID","PRODUCT_TYPE","ICE_TRANSACTION_ID","SUMMIT_TRADE_ID","MPP_ID","BLOCK_MSG_ID","BUY_SELL","SUMMIT_BOOK","ICELINK_BOOK","SUMMIT_COUNTERPARTY","ICELINK_COUNTERPARTY","NOTIONAL","CURRENCY","DB_PAY_REC","FIXED_RATE","SPREAD","TRADE_DATE","EFFECTIVE_DATE","END_DATE","FEATURE_CODES","STRUCTURE_ID","CURVE_NAME","CDX_IDX_RED_CODE","CDX_IDX_NAME","RESTRUCT_TYPE","EVENT_TRANSACTION","EVENT_SUBTYPE","EVENT_STATUS","REJECT_CODE","REJECT_REASON","STATUS","CLEARING_STATUS","NOTIF_ORIGINATOR","NOTIF_VERSIONNO","NOTIF_TIMESTAMP","ORIG_SYSTEM_NAME","ORIG_SYSTEM_LOC","CATALOGUE_CODE","MSG_COMMENTS","MSG_SENDER","RETRY_NUMBER","SOURCE_MSG","MODIFICATION_TIME","CREATION_TIME","CLEARING_TYPE" from (
	  SELECT ICE.* FROM TPADMIN_OWNER.CCC_QP_TRADES ICE,
		TPADMIN_OWNER.CCC_QP_TRADE_STATUS S
	  WHERE ICE.STATUS = S.STATUS
	  AND S.PRIORITY =
		(SELECT MIN(s.priority)
		FROM TPADMIN_OWNER.CCC_QP_TRADES CM
		JOIN TPADMIN_OWNER.CCC_QP_TRADE_STATUS S
		ON CM.STATUS    = S.STATUS
		WHERE CM.MPP_ID = ICE.MPP_ID)
  ) T 
  WHERE T.NOTIF_TIMESTAMP = (SELECT MAX(trade.NOTIF_TIMESTAMP)
    FROM TPADMIN_OWNER.CCC_QP_TRADES trade
    WHERE T.STATUS = trade.STATUS
    and t.MPP_ID = trade.MPP_ID)
    AND T.INT_MSG_ID =  (SELECT MAX(trade.INT_MSG_ID)
    FROM TPADMIN_OWNER.CCC_QP_TRADES trade
    WHERE T.STATUS       = TRADE.STATUS
    AND T.MPP_ID         = TRADE.MPP_ID
    AND T.NOTIF_TIMESTAMP=TRADE.NOTIF_TIMESTAMP
    );

    mottari, 18 Февраля 2011

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

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

    −982

    1. 1
    SELECT * FROM `club_node` WHERE date_format(FROM_UNIXTIME(`created`),'%Y-%m-%d') = date_format(FROM_UNIXTIME(UNIX_TIMESTAMP() + ${поправка на пояс} ),'%Y-%m-%d')

    так пхпкодеры с drupal.ru решают вопрос временных зон. знание матчасти - бляпиздец.
    http://drupal.ru/node/57305

    brainstorm, 13 Февраля 2011

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

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

    −178

    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
    SELECT DISTINCT BE.ID as ID,BE.NAME as NAME,BE.IBLOCK_ID as IBLOCK_ID,BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID, FPEN0.VALUE as PROPERTY_TYPE_LAMP_VALUE, FPEN0.ID as PROPERTY_TY
PE_LAMP_ENUM_ID, FPV0.ID as PROPERTY_TYPE_LAMP_VALUE_ID, FPEN1.VALUE as PROPERTY_STYLE_VALUE, FPEN1.ID as PROPERTY_STYLE_ENUM_ID, FPV1.ID as PROPERTY_STYLE_VALUE_ID,BE.PREVIEW_PICTURE as PREVIEW_PICTURE, FPEN2.VALUE as PROPERTY_SERIES_VALUE, FPEN2.ID as PROPERTY_SERIES_ENUM_ID, FPV2.ID as PROPERTY_SERIES_VALUE_ID, FPEN3.VALUE as PROPERTY_COLOR_ARMAT_VALUE, FPEN3.ID as PROPERTY_COLOR_ARMAT_ENUM_ID, FPV3.ID as PROPERTY_COLOR_ARMAT_VALUE_ID, FPEN4.VALUE as PROPERTY_COLOR_PLAFON_VALUE, FPEN4.ID as PROPERTY_COLOR_PLAFON_ENUM_ID, FPV4.ID as PROPERTY_COLOR_PLAFON_VALUE_ID, FPEN5.VALUE as PROPERTY_NEW_VALUE, FPEN5.ID as PROPERTY_NEW_ENUM_ID, FPV5.ID as PROPERTY_NEW_VALUE_ID, FPEN6.VALUE as PROPERTY_SALE_VALUE, FPEN6.ID as PROPERTY_SALE_ENUM_ID, FPV6.ID as PROPERTY_SALE_VALUE_ID , CAT_P1.ID as CATALOG_PRICE_ID_1,  CAT_P1.CATALOG_GROUP_ID as CATALOG_GROUP_ID_1,  CAT_P1.PRICE as CATALOG_PRICE_1,  CAT_P1.CURRENCY as CATALOG_CURRENCY_1,  CAT_P1.QUANTITY_FROM as CATALOG_QUANTITY_FROM_1,  CAT_P1.QUANTITY_TO as CATALOG_QUANTITY_TO_1,  '<C1><E0><E7><EE><E2><E0>
<FF> <F6><E5><ED><E0>' as CATALOG_GROUP_NAME_1,  'Y' as CATALOG_CAN_ACCESS_1,  'Y' as CATALOG_CAN_BUY_1,  CAT_P1.EXTRA_ID as CATALOG_EXTRA_ID_1, CAT_PR.QUANTITY as CATALOG_QUANTITY,  CAT_PR.QUANTITY_TRACE as CATALOG_QUANTITY_TRACE,  CAT_PR.WEIGHT as CATALOG_WEIGHT,  CAT_VAT.RATE as CATALOG_VAT,  CAT_PR.VAT_INCLUDED as CATALOG_VAT_INCLUDED,  CAT_PR.PRICE_TYPE as CATALOG_PRICE_TYPE,  CAT_PR.RECUR_SCHEME_TYPE as CATALOG_RECUR_SCHEME_TYPE,  CAT_PR.RECUR_SCHEME_LENGTH as CATALOG_RECUR_SCHEME_LENGTH,  CAT_PR.TRIAL_PRICE_ID as CATALOG_TRIAL_PRICE_ID,  CAT_PR.WITHOUT_ORDER as CATALOG_WITHOUT_ORDER,  CAT_PR.SELECT_BEST_PRICE as CATALOG_SELECT_BEST_PRICE
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
LEFT JOIN b_iblock_property FP0 ON FP0.IBLOCK_ID = B.ID AND  FP0.CODE='TYPE_LAMP'
INNER JOIN b_iblock_property FP1 ON FP1.IBLOCK_ID = B.ID AND  FP1.CODE='STYLE'
INNER JOIN b_iblock_property FP2 ON FP2.IBLOCK_ID = B.ID AND  FP2.CODE='SERIES'
LEFT JOIN b_iblock_property FP3 ON FP3.IBLOCK_ID = B.ID AND  FP3.CODE='COLOR_ARMAT'
LEFT JOIN b_iblock_property FP4 ON FP4.IBLOCK_ID = B.ID AND  FP4.CODE='COLOR_PLAFON'
LEFT JOIN b_iblock_property FP5 ON FP5.IBLOCK_ID = B.ID AND  FP5.CODE='NEW'
LEFT JOIN b_iblock_property FP6 ON FP6.IBLOCK_ID = B.ID AND  FP6.CODE='SALE'
/*... тут еще 14 джоинов ...*/
INNER JOIN (
	SELECT DISTINCT BSE.IBLOCK_ELEMENT_ID
	FROM b_iblock_section_element BSE

	INNER JOIN b_iblock_section BS ON BSE.IBLOCK_SECTION_ID = BS.ID

	WHERE (((BS.ID = 25)))
	) BES ON BES.IBLOCK_ELEMENT_ID = BE.ID
LEFT JOIN b_catalog_price CAT_P1 ON (CAT_P1.PRODUCT_ID = BE.ID AND CAT_P1.CATALOG_GROUP_ID = 1)
LEFT JOIN b_catalog_product CAT_PR ON (CAT_PR.ID = BE.ID)
LEFT JOIN b_catalog_iblock CAT_IB ON ((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0) AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)
LEFT JOIN b_catalog_vat CAT_VAT ON (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID))
WHERE 1=1
AND ( 13:19 
((((BE.IBLOCK_ID = '3'))))
	AND ((((BE.ACTIVE='Y'))))
	AND (
		(
		((((CAT_P1.PRICE >= '386'))))
		)
		AND (
		((((CAT_P1.PRICE <= '4166'))))
		)
	)
	AND ((((CAT_PR.QUANTITY > '0'))))
	AND ((((FPEN1.VALUE LIKE '<CA><EB><E0><F1><F1><E8><F7><E5><F1><EA><E8><E9>'))))
	AND ((((FPEN2.VALUE LIKE 'Davoli'))))
)
AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)));

    Bitrix, что еще тут скажешь))

    greevex, 10 Февраля 2011

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

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

    −864

    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
    select
	distinct q.subject as subject,
	q.notification_body as notification_body,
	q.record_id as notification_id,
	q.notif_type as notification_type,
	pkgxyz.sql_queue_addr_set_status(a.record_id, 4)
from
	NOTIF_QUEUE q,
	NOTIF_QUEUE_address a 
where 
	q.transport = 1234567890 and q.status = 1 and q.record_id = a.notification_id and a.status = 1;
	
	
  function sql_queue_addr_set_status(pRecord_id number, pValue number) return int as
    pragma autonomous_transaction;
  begin
    Trace('sql_queue_addr_set_status', '[pRecord_id='||pRecord_id||', pValue='||pValue||'] start');
    update notif_queue_address a
      set a.status = pValue
      where a.record_id = pRecord_id;
    commit;
    Trace('sql_queue_addr_set_status', '[]end');
    return 1;
  end;

    "У нас есть только один шанс получить эти данные" - как бы говорит нам этот кусок г.. кусок кода.
    В селекте (строки 1-11) мы получаем данные, и сразу вызываем хранимую процедуру (строки 14-24), которая изменяет выбранные данные. Если запустим этот запрос ещё раз, то он ничего не вернёт.

    Имена и айдишники изменены, что бы не выдать компанию, в которой это написано.

    red_falcon, 04 Февраля 2011

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

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

    −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
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    declare @params varchar(max)
select @params = CAST((select @sid, ', ',
@login_hash, ', ',
@login_ip, ', ',
@login_user_agent_hash, ', ',
@mt_account, ', ',
@mt_passwd, ', ',
@phone, ', ',
@phone_passwd, ', ',
@wr_type, ', ',
@wr_sum, ', ',
@disable_mt_account, ', ',
@wr_user_name, ', ',
@wr_user_corpName, ', ',
@wr_user_addr, ', ',
@wr_bank_name, ', ',
@wr_bank_code, ', ',
@wr_bank_addr, ', ',
@wr_bank_account, ', ',
@wr_bank_iban, ', ',
@accCurrency, ', ',
@commission, ', ',
@wrCurrency, ', ',
@polandWrType, ', ',
@wr_bank_mfo, ', ',
@wr_bank_okpo, ', ',
@wr_bank_transitAccount, ', ',
@wr_bank_comment, ', ' for xml path('')) as varchar)
exec @r = web_write_log 
  @uid, 
  15, 
  @this_time, 
  @params

    MSSQL 2008
    Сериализация в строку кучи переменных без использования cast :D

    LameCoder, 03 Февраля 2011

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

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

    −857

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    8. 8
    INSERT INTO `modules`
VALUES (
'', 5, 1, 2, 3, 4, 5, 6, 7, 8, 9, (
SELECT max( `order` )
FROM `modules`
WHERE `root` =5
), 'Y'
)

    #1093 - You can't specify target table 'modules' for update in FROM clause

    newpdv, 30 Января 2011

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

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

    −855

    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
    declare @ДеньНедели int
set @ДеньНедели = 1
while @ДеньНедели <= 7
begin
  if @ДеньНедели = 1
  begin
    update #TTT set Пн = @Сумма where Поставщик = @Поставщик and datepart(dw,Дата) = @ДеньНедели
  end
  if @ДеньНедели = 2
  begin
    update #TTT set Вт = @Сумма where Поставщик = @Поставщик and datepart(dw,Дата) = @ДеньНедели
  end
  if @ДеньНедели = 3
  begin
    update #TTT set Ср = @Сумма where Поставщик = @Поставщик and datepart(dw,Дата) = @ДеньНедели
  end
  if @ДеньНедели = 4
  begin
    update #TTT set Чт = @Сумма where Поставщик = @Поставщик and datepart(dw,Дата) = @ДеньНедели
  end
  if @ДеньНедели = 5
  begin
    update #TTT set Пт = @Сумма where Поставщик = @Поставщик and datepart(dw,Дата) = @ДеньНедели
  end
  if @ДеньНедели = 6
  begin
    update #TTT set Сб = @Сумма where Поставщик = @Поставщик and datepart(dw,Дата) = @ДеньНедели
  end
  if @ДеньНедели = 7
  begin
    update #TTT set Вс = @Сумма where Поставщик = @Поставщик and datepart(dw,Дата) = @ДеньНедели
  end
  set @ДеньНедели = @ДеньНедели + 1
end

    Интересный разбор дня недели, цикл проходим 7 раз, с заходом в каждый if 1 раз

    mrvlmor, 25 Января 2011

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

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

    −143

    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
    if exists(
  select 1
  from PList
  where userId = @userId
  and opCode = 'SSDD' 
) 
begin
   select @SSDD = ssValue
   from  PList 
          where userId = @userId
            and opCode = 'SSDD' 
   .................
	<some code>
   .................
end

    оптимальненько

    3.14159265, 25 Января 2011

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