Folks,

Does anyone know when can the value in syscolumns. collength column be
negative (<0) and is there a way to reverse engineer the fol. formula to
find the min_space and max_space so that it can be programmed:

If the collength value is -ve then
collength + 65536 = (min_space * 256) + max_size

Thanks for all your help.

--
Rajiv Jhaveri
rjhaveri@optonline.net

On Thu, 22 Jul 2004 10:42:03 -0400, Rajiv Jhaveri wrote:

> Folks,

For what datatype?

Art S. Kagel

> Does anyone know when can the value in syscolumns. collength column be
> negative (<0) and is there a way to reverse engineer the fol. formula to
> find the min_space and max_space so that it can be programmed:
>
> If the collength value is -ve then
> collength + 65536 = (min_space * 256) + max_size
>
> Thanks for all your help.
>
> --
> Rajiv Jhaveri
> rjhaveri@optonline.net

Art,

The SQL reference states that it could be -ve for NVARCHAR. I am
assuming it to be true also for VARCHAR.

--
Rajiv Jhaveri
rjhaveri@optonline.net


"Art S. Kagel" <kagel@bloomberg.net> wrote in message
newsan.2004.07.22.12.55.56.480893.1355@bloomberg .net...
> On Thu, 22 Jul 2004 10:42:03 -0400, Rajiv Jhaveri wrote:
>
> > Folks,
>
> For what datatype?
>
> Art S. Kagel
>
> > Does anyone know when can the value in syscolumns. collength column be
> > negative (<0) and is there a way to reverse engineer the fol. formula to
> > find the min_space and max_space so that it can be programmed:
> >
> > If the collength value is -ve then
> > collength + 65536 = (min_space * 256) + max_size
> >
> > Thanks for all your help.
> >
> > --
> > Rajiv Jhaveri
> > rjhaveri@optonline.net

Rajiv Jhaveri wrote:
> The SQL reference states that it could be -ve for NVARCHAR. I am
> assuming it to be true also for VARCHAR.

I suppose so; if the minimum length is 128..255, then the result would
be negative. I'm not sure I'd ever use such a large minimum size; in
particular, a minimum size of 255 is pointless. However...

> "Art S. Kagel" <kagel@bloomberg.net> wrote:
>>On Thu, 22 Jul 2004 10:42:03 -0400, Rajiv Jhaveri wrote:
>>
>>
>>For what datatype?

Fair question.

>>> Does anyone know when can the value in syscolumns. collength column be
>>>negative (<0) and is there a way to reverse engineer the fol. formula to
>>>find the min_space and max_space so that it can be programmed:
>>>
>>>If the collength value is -ve then
>>> collength + 65536 = (min_space * 256) + max_size

Apart from the fact we're dealing with 16-bit instead of 64-bit
quantities, I'm suffering from deja vu again. Earlier this week, I
was discussing similar issues for converting 32-bit INT to INT8.

mod((case when c.collength < 0 then c.collength + 65536 else
c.collength end), 256) AS max_size

trunc((case when c.collength < 0 then c.collength + 65536 else
c.collength) / 256, 0) AS min_space

Untested - should work.

Alternatively,

mod(c.collength + 65536, 256) AS max_size

trunc(mod(c.collength+65536, 65536) / 256, 0)

The first works because 65536 is a multiple of 256. The second works
because adding 65536 to a value and reducing it mod 65536 leaves a
remainder that is positive for values in the range -32767..+32767.

Beware the dreaded null!

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/