Conference orarep::nomahs::sql

~    SQL> select cast(999 as interval second(3)) from rdb$relations limit to
~    1 row;
~    %SQL-F-DATETIM_SCALE, Date/time scale for <value expression> must be
~    less than or equal to 2

second(3) is equivalent to SECOND(2,3) which is illegal.  I think you wanted
to say SECOND (3,2)...

Ian

>second(3) is equivalent to SECOND(2,3) which is illegal.  I think you wanted
>to say SECOND (3,2)...
    
    Yes, that works.
    
    The manual is not at all clear about what SECOND(n) means, but the last
    sentence of page 2-70 (SQL Reference Manual) uses that format to
    describe a precision of 2.
    
    Peter

You are correct there is not good discussion, we need to fix that.

Ian

~    SQL> select cast(999 as interval second(3)) from rdb$relations limit to
~    1 row;

This might be obvious but if they just want a literal then they should use:

	interval'999'second(3,0)

or

	interval'999.00'second(3,2)

I have seen too many programming examples posted which use CAST instead of
interval literals.  Is this generally a problem?

Ian

    I just used a literal to demonstrate the problem. The customer has a
    integer field containing a number of seconds since 1-jan-1970 0:0:0. He
    wants to convert it into a text version of the date/time.
    
    His main problem is that he has V5.1A - ANSI DATE literals don't work -
    SECONDS (9,2) doesn't work - functions aren't available. He can't even
    use cast to switch between date and bigint formats as you could in
    V4.2. I told him he'll have to wait until he has upgraded. They are
    testing V7, but haven't gone live with it.
    
    Peter

~    His main problem is that he has V5.1A - ANSI DATE literals don't work -

What do you mean "don't work"

~    SECONDS (9,2) doesn't work

What do you mean "doesn't work"

~- functions aren't available. He can't even
~    use cast to switch between date and bigint formats as you could in
~    V4.2.

Huh?

~I told him he'll have to wait until he has upgraded. They are testing V7, but
~haven't gone live with it.

ALl the date/time support works just fine in V5.1A.  I have no idea what you
are talking about.  It may be that the customer is running an older version of
SQL against V5.1A, which might explain the problem.

Ian

>~    His main problem is that he has V5.1A - ANSI DATE literals don't work -
>
>What do you mean "don't work"
    
    The customer said he got a date conversion error. Casting a date vms
    literal as date ansi worked around it. 
    
>~    SECONDS (9,2) doesn't work
>
>What do you mean "doesn't work"
    
    The customer said he got a syntax error.
    
    We don't have 5.1A on our systems anymore, so I couldn't try for
    myself. I just had to take his word for it. It worked for me on V6.0-16.
    
    Peter

    > We don't have 5.1A on our systems
    
    	doesn't seem like a difficult problem to resolve.

~SECONDS (9,2) doesn't work 

I just had a thought, I think for V5.1 and earlier the syntax had to be:

SECONDS (9.2) i.e. a period and not a comma.  In V6.0 and later we support bot
a , (SQL92 Standard) and . (SQL2 draft standard).

Ian

    Re .8
    Installing 5.1A would be easy, finding a kit, and the disk space to
    install it would be more difficult, especially, as some of our systems
    have been upgraded to VMS 7.1, and so won't run it anyway.
    
    Re .9
    You are right. The 5.0 SQL reference manual uses a '.'.
    It also only shows the format DATE 'yyy-mm-dd' for date literals. I.e.
    DATE ANSI 'yyyy-mm-dd' is not a valid format according to the manual.
    
    Peter

    >Installing 5.1A would be easy, finding a kit, and the disk space to
    >install it would be more difficult, especially, as some of our systems
    >have been upgraded to VMS 7.1, and so won't run it anyway.
    
    	I don't understand why vms 7.1 would make any difference at all
    unless all of your systems are alphas.  It would seem handy to have a
    vax or two around so that you could support customers who've not
    upgraded as well.

    re .11
    
    My mistake. I forgot that the problem only affects Alphas, but it still
    reduces the number of systems it is possible to install 5.1A on, since
    some of the systems are Alphas.
    
    The reason we overwrote 5.1A is becuase it isn't supported anymore. We
    have all supported versions on both VAX and Alpha.
    
    Peter
    
    

    While you might not be 'supporting' v5.1 any more, having to support
    customers that are using it (like .0 would imply) seems like a pretty
    good reason to have it installed.

    Re .13
    
    That would also be a reason to have 4.2A, 4.0A, etc. installed. We have
    to draw a line somewhere. When we moved from Digital we only installed
    5.1A standard, 6.0 MV and 6.1 MV. The 5.1A standard was overwritten by
    V7.0 standard, but not on all systems until several months after V7
    came out.
    
    We can still look up known problems, and try the same thing on a
    current version. If it is not a known problem and works on the current
    version, then the solution is to upgrade. 
    
    Having it installed would probably not have helped much in this case.
    The customer was testing my suggestions out on V5.1A while I was on the
    phone to him and telling me that they failed. They would have still
    have failed if I tried them myself (on V5.1A - they worked for me on
    V6.0-16).
    
    Originally the customer asked for help defining an external function to
    do the conversion :-) I suggested the use of cast and date arithmetic.
    
    Peter 

    I understand that there are  all sorts of excuses why it isn't
    installed (and, perhaps why you don't want it installed).  However, in
    a prior reply, you explained that you were unable to test something
    because you didn't have it installed.  I was simply pointing out that
    the excuse of not having it installed doesn't hold too much water.

    The customer is now using 9.2, but still had a problem. The values he
    is getting are larger than the maximum value allowed for a seconds
    interval. To get around this he is using a later base date and is
    subtract the number of seconds between that date and 1-Jan-1970 from
    the field before casting as interval seconds (9.2) so that he can add
    it to the new base date.
    
    Peter

It sounds like he should be using MINUTE (9) to SECOND (2) to get a larger
range.  

Ian

>It sounds like he should be using MINUTE (9) to SECOND (2) to get a larger
>range.  
    
    That doesn't work, nor would I expect it too.
    
    SQL> sel cast(30000000 as interval minute (9) to second (2))
    cont> from rdb$relations limit to 1 row;
    %RDB-E-CONVERT_ERROR, invalid or unsupported data conversion
    -RDMS-F-INV_DATE_CHG, invalid field datatype change to/from datetime
                                                 
    Rdb 7.0-01
    
    Peter

I knew when I posted that reply that it would be confusing (sorry I was
running to a meeting).  What I meant was if they want to get the interval
between 1-JAN-1970 and today, don't use seconds, but use a interval qualifier
with more one field (e.g. MINUTE TO SECOND).  If they have a number of seconds
with more than 9 digits (where did that come from?) then they can store it in
the database using na interval of more than one field.  e.g.

	cast (div (:hv, 60) as interval minute (9)) +
	cast (mod (:hv, 60) as interval second)

which results in an interval of MINUTE (9) TO SECOND (2).

This is all guess work on my part because I don't have the full description of
the customers problem.

Ian

    Re .19
    
    The customer has stored in an integer field in his database the number
    of seconds between 1-Jan-1970 and dates within the last few months. He
    wants to display them as dates. The numbers have 8 digits and are
    larger than 21474836.47 - the maximum value for a INTERVAL SECONDS
    field.
    
    The solution we came up with was something like
    
    SELECT (CAST(DATE '1996-12-31' AS DATE ANSI) + CAST((field - number) AS
    INTERVAL SECONDS (9.2))) FROM table;
    
    where number is the number of seconds between 1996-12-31 and 1970-1-1.
    
    Note, it is not possible to store a 9 digit number of seconds in a
    INTERVAL SECONDS(9,2). This is documented, but rather surprising.
    
    Peter

~    Note, it is not possible to store a 9 digit number of seconds in a
~    INTERVAL SECONDS(9,2). This is documented, but rather surprising.

It is possible...  For example.

SQL> select interval'999999999'second(9,0) from rdb$database;

  999999999
1 row selected

It is surprising since I implemented this support.  Please submit a BUG
report, it looks like CAST is broken, I wonder what it is trying to do with
the integer.

It is possible to work around this problem I think by casting the number to a
CHAR and then casting that to an INTERVAL.

SQL> declare :x char(10);
SQL> select '999999999' into :x from rdb$database;
SQL> select cast(:x as interval second(9,2)) from rdb$database;

  999999999.00
1 row selected

Ian

>It is surprising since I implemented this support.  Please submit a BUG
>report, it looks like CAST is broken, I wonder what it is trying to do with
>the integer.
    
    Done. Note that cast is behaving as documented - table 2-10 of the SQL
    reference manual.
    
    SQL> select cast(21474836 as interval second(9,2)) from rdb$database;
    
      021474836.00
    1 row selected
    SQL> select cast(21474837 as interval second(9,2)) from rdb$database;
    %RDB-E-ARITH_EXCEPT, truncation of a numeric value at runtime
    -COSI-F-INTOVF, integer overflow
    
    Peter

~    Done. Note that cast is behaving as documented - table 2-10 of the SQL
~    reference manual.

Yes I know it is documented that way, but the documentation describes a bug
(or at least an unreasonable limitation).  Thanks for the bug...

Ian

I have fixed this, and this will be in the next ECO for V6.1A and Rdb7.

What I actually did is use a QUADWORD as the intermediate variable so that
larger values can be used, as shown here.

The side effect of the change is that large values on Alpha also now work
(oops they didn't report overflow correctly previously), and the code uses
about 10th the number of instructions...

It turns out that I wrote the original code for VAX in March 1991, and it is
amazing how much I have learned since then :-)

Ian

SQL> -- YEAR
SQL> select cast(999999999.5 as interval year(9)),
cont> 	cast(999999999.2 as interval year(9))
cont>  from rdb$database;
                           
  *********    999999999   
1 row selected
SQL> 
SQL> -- MONTH
SQL> select cast(999999999.5 as interval month(9)),
cont> 	cast(999999999.3 as interval month(9))
cont>  from rdb$database;
                           
  *********    999999999   
1 row selected
SQL> 
SQL> -- DAY
SQL> select cast(999999999.5 as interval day(9)),
cont> 	cast(999999999.4 as interval day(9))
cont>  from rdb$database;
%RDB-E-ARITH_EXCEPT, truncation of a numeric value at runtime
-COSI-F-INTOVF, integer overflow
SQL> 
SQL> -- HOUR
SQL> select cast(999999999.5 as interval hour(9)),
cont> 	cast(999999999.1 as interval hour(9))
cont>  from rdb$database;
%RDB-E-ARITH_EXCEPT, truncation of a numeric value at runtime
-COSI-F-INTOVF, integer overflow
SQL> 
SQL> -- MINUTE
SQL> select cast(999999999.5 as interval minute(9)),
cont> 	cast(999999999.2 as interval minute(9))
cont>  from rdb$database;
                           
  *********    999999999   
1 row selected
SQL> 
SQL> -- SECOND (0)
SQL> select cast(999999999.5 as interval second(9,0)),
cont> 	cast(999999999.1 as interval second(9,0)),
cont> 	cast(999999999.235 as interval second(9,0))
cont>  from rdb$database;
                                        
  *********    999999999    999999999   
1 row selected
SQL> 
SQL> -- SECOND (1)
SQL> select cast(999999999.5 as interval second(9,1)),
cont> 	cast(999999999.1 as interval second(9,1)),
cont> 	cast(999999999.235 as interval second(9,1))
cont>  from rdb$database;
                                              
  999999999.5    999999999.1    999999999.2   
1 row selected
SQL> 
SQL> -- SECOND (2)
SQL> select cast(999999999.5 as interval second(9,2)),
cont> 	cast(999999999.1 as interval second(9,2)),
cont> 	cast(999999999.235 as interval second(9,2))
cont>  from rdb$database;
                                                 
  999999999.50    999999999.10    999999999.24   
1 row selected

Yes, Peter you can actually store more than 999999999.5 seconds.  :-)