How do i declare variables in my sql statement
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
Answers (1)
Please log in to answer
Posted by:
StockTrader
8 years ago
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
Comments:
-
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 - emorrison 8 years ago
-
I do not think it is allowed to use variables in a KACE SQL report definition anyway.
Kind regards,
Marco - StockTrader. - StockTrader 8 years ago-
-
Thanks - emorrison 8 years ago
