here is an example of what am try to do

SET @PickUpDate :=  HD_TICKET.CUSTOM_FIELD_VALUE9;
SET @dayofweek :=  EXTRACT(DAY FROM DATE_FORMAT(@PickUpDate,'%y-%m-%w'));

SET @d := DATEDIFF(@PickUpDate,DATE_FORMAT(NOW(),'%y-%m-%d')) ; 
CASE 
    WHEN ( @d <0 && @dayofweek = 3 || @dayofweek = 4 || @dayofweek = 5 )then( update HD_TICKET  set HD_TICKET.CUSTOM_FIELD_VALUE9 = DATE_ADD( DATE_FORMAT(@PickUpDate,'%y-%m-%d') ,INTERVAL 5 DAY ) )where  (HD_TICKET.ID in (<TICKET_IDS>))

    WHEN @d <=3 && @dayofweek = 6|| @dayofweek = 7 then( UPDATE HD_TICKET  set HD_TICKET.CUSTOM_FIELD_VALUE9 = DATE_ADD( DATE_FORMAT(@PickUpDate,'%y-%m-%d') ,INTERVAL 4 DAY ))where  (HD_TICKET.ID in (<TICKET_IDS>))
    WHEN @d >3 && @dayofweek = 6 then( update HD_TICKET  set HD_TICKET.CUSTOM_FIELD_VALUE9 = DATE_ADD( DATE_FORMAT(@PickUpDate,'%y-%m-%d') ,INTERVAL 4 DAY ))where  (HD_TICKET.ID in (<TICKET_IDS>))
WHEN @d >3 && @dayofweek = 0 then (update HD_TICKET  set HD_TICKET.CUSTOM_FIELD_VALUE9 = DATE_ADD( DATE_FORMAT(@PickUpDate,'%y-%m-%d') ,INTERVAL 3 DAY ))where  (HD_TICKET.ID in (<TICKET_IDS>))
    
    ELSE -1 --unknownwhere 
END

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1
Hello,

I'm not a big expert of SQL but it seems to me that this statement:
SET @PickUpDate :=  HD_TICKET.CUSTOM_FIELD_VALUE9;
does not make sense.

HD_TICKET is a table that potentially has many records. CUSTOM_FIELD_VALUE9 is a field of the table and does not refer to any of the possibly present records in the table...so what value the variable should have?

Kind regards,

 Marco - StockTrader

Answered 02/18/2016 by: StockTrader
Red Belt

  • It works when i use it in mysql work bench but not when i try to execute in kace...i have a criteria that will only return result but its a date value
    • I do not think it is allowed to use variables in a KACE SQL report definition anyway.
      Kind regards,
      Marco - StockTrader.
      • StockTrader,

        You're correct, no variables allowed in KACE.
      • Thanks
Please log in to comment
Answer this question or Comment on this question for clarity

Share