Topic
  • 5 replies
  • Latest Post - ‏2017-01-02T16:59:36Z by fjb_saper
SupriyoDebnath
SupriyoDebnath
27 Posts

Pinned topic Compilation Error While Declaring INTERVAL using CHARACTER VARIABLE

‏2016-12-06T10:32:51Z | esql iib10

Hi,

      I'm facing an issue while trying to set an INTERVAL in compute node. I have a requirement based on timer features in IIB. My application should run everyday starting from 07:15:00 to 17:15:00. As per current requirement the interval is 1 hour, but later it may change. The following snippet of code gives an compilation error, stating invalid syntax

 

DECLARE intNewInterval INTERVAL;

DECLARE cTemp CHARACTER '1';

SET intNewInterval = INTERVAL cTemp HOUR;

 

Whereas the below code works fine....

DECLARE intNewInterval INTERVAL;

SET intNewInterval = INTERVAL '1' HOUR;

 

Can anybody tell me what is the mistake, if I use a character variable to define my interval period?

Updated on 2016-12-06T10:34:47Z at 2016-12-06T10:34:47Z by SupriyoDebnath
  • fjb_saper
    fjb_saper
    159 Posts

    Re: Compilation Error While Declaring INTERVAL using CHARACTER VARIABLE

    ‏2016-12-08T19:23:26Z  

    Have you tried changing the declaration of cTemp to a numerical variable, (int, decimal, float)?

     

  • SupriyoDebnath
    SupriyoDebnath
    27 Posts

    Re: Compilation Error While Declaring INTERVAL using CHARACTER VARIABLE

    ‏2016-12-09T14:54:54Z  

    Hi fjb_saper

                No, I did not. Because as per infocentre the format should be like this

    
    
    INTERVAL 
    <
    interval 
    string
    > 
    <
    interval qualifier
    >
    

    Now it is really confusing why it does not accept a variable after INTERVAL keyword.

  • fjb_saper
    fjb_saper
    159 Posts

    Re: Compilation Error While Declaring INTERVAL using CHARACTER VARIABLE

    ‏2016-12-12T14:22:43Z  

    Hi fjb_saper

                No, I did not. Because as per infocentre the format should be like this

    <pre class="pre codeblock codeblock prettyprint prettyprinted" dir="ltr" style="font-family: monospace, monospace; background-color: rgb(218, 218, 218); padding: 2px; white-space: pre-wrap; word-wrap: break-word; font-size: small; overflow: auto; border: 1px solid rgb(136, 136, 136); color: rgb(0, 0, 0);"> INTERVAL < interval string > < interval qualifier > </pre>

    Now it is really confusing why it does not accept a variable after INTERVAL keyword.

    Because the string it is referring to is the interval qualifier... in your case that would be HOUR....

    The definition is:

    INTERVAL <elapsed measure (numerical) > <unit of measure (string)>

    Apparently when you define it as INTERVAL '1' HOUR there is some kind of internal conversion taking place.

    Usually you assign to the interval variable an operation on 2 variables and a unit of measure.

    like 

    INTERVAL elapsed_time = timestp1 - timestp2 in SECONDS....

    For more details look it up in the infocenter...

  • SupriyoDebnath
    SupriyoDebnath
    27 Posts

    Re: Compilation Error While Declaring INTERVAL using CHARACTER VARIABLE

    ‏2016-12-13T13:29:24Z  
    • fjb_saper
    • ‏2016-12-12T14:22:43Z

    Because the string it is referring to is the interval qualifier... in your case that would be HOUR....

    The definition is:

    INTERVAL <elapsed measure (numerical) > <unit of measure (string)>

    Apparently when you define it as INTERVAL '1' HOUR there is some kind of internal conversion taking place.

    Usually you assign to the interval variable an operation on 2 variables and a unit of measure.

    like 

    INTERVAL elapsed_time = timestp1 - timestp2 in SECONDS....

    For more details look it up in the infocenter...

    Hi fjb_saper

     

    I agree with your second point that INTERVAL is defined as difference in two timestamp/datetime with a unit of measure. But I'm not convinced with your first point that in INTERVAL the quantity of interval is numerical value. You should get it contradicted in below page

    http://www.ibm.com/support/knowledgecenter/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ac05950_.htm

    So its still an open question!!!! Laughing

     

    Updated on 2016-12-13T13:30:04Z at 2016-12-13T13:30:04Z by SupriyoDebnath
  • fjb_saper
    fjb_saper
    159 Posts

    Re: Compilation Error While Declaring INTERVAL using CHARACTER VARIABLE

    ‏2017-01-02T16:59:36Z  

    Hi fjb_saper

     

    I agree with your second point that INTERVAL is defined as difference in two timestamp/datetime with a unit of measure. But I'm not convinced with your first point that in INTERVAL the quantity of interval is numerical value. You should get it contradicted in below page

    http://www.ibm.com/support/knowledgecenter/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ac05950_.htm

    So its still an open question!!!! Laughing

     

    In the end I think it has to be a substitution problem....

    Have you tried 

    DECLARE myInterval INTERVAL {string_name} HOUR;