/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


How do i declare variables in my sql statement

02/18/2016 765 views
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


All 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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ