Re: calculating collength in syscolumns

Vardan Aroustamian wrote: > > In article <01bd3183$302eeb60$35207da7@rfahey.gemini.ca>, > "R Fahey" <r.fahey@usa.net> wrote: > > > > I am trying to calculate the actual column length for a column type of > > 10/14(DATETIME/INTERVAL) from within a > > stored procedure.In reading TFM it provides a formula of, > > (length*256)+(largest_qualifier_value*16)+smallest_qualifier_value). > > It seems that > > select tabid, colname, coltype, TRUNC(collength/256) from syscolumns;> > must return length. Isn't it? > > > These 'qualifier_values' are given in TFM but I can't seem to find a way of > > determing them from within my stored proc. > > For example, a type 10(DATETIME), can be any 1 of 28 possible precisions. > > What I need is to be able to determine > > this precision, so that I can then detrermine the actual number of bytes > > used for that column. OK. Here is the formula: LET strtprec = (syscolumns.collength / 16) MOD 16 LET end_prec = (syscolumns.collength MOD 16 if (strtprec = 0) then -- YEAR LET strt_adj = 0 elif (strtprec = 2) then -- MONTH LET strt_adj = 1 elif (strtprec = 4) then -- DAY LET strt_adj = 2 elif (strtprec = 6) then -- HOUR LET strt_adj = 3 elif (strtprec = 8) then -- MINUTE LET strt_adj = 4 elif (strtprec = 10) then -- SECOND LET strt_adj = 5 elif (strtprec >= 11 AND strtprec <= 15) then -- FRACTION(strtprec-10) LET strt_adj = 6 -- You can fine tune this to allocate less -- display space for smaller fractional -- precisions. I wanted decimal alignment. end if Do the same if-block test for end_prec and set end_adj to the equivalent values. The on-disk storage size for the interval is then: LET storage = ((syscolumns.collength / 256) MOD 256 + 3) / 2 The display length for the interval is then: LET adjustment = end_adj - strt_adj LET display_len = (syscolumns.collength / 256) MOD 256 + adjustment Art S. Kagel