[Solved] Can someone clarify..... numerical types?

[GJM]

If I create a numerical field I can choose from:

Numeric
&
Decimal

or even Integer for that matter. (Integer I see you can set to do the auto value thing for you) But the other two I was trying to understand the subtleties between them, because as far as I can see I can still select a format of currency, date, time etc, etc, for both of them - even integer for that matter. Basically I am not sure if I should be choosing one over the other for some reason.

I guess while I am at it, the difference between Varchar and varchar_ingnorecase and Text (fix) [char] I am not real clear on, other than in certain situations the case sensitivity will come into play somewhere (not sure where). Is it when you do queries?

Thanks again,

GJM

[r4zoli]

In Base has built in HSQLDB database engine, data types detailed information you could find in 9th chapter of HSQLDB Documentation.

VARCHAR_IGNORECASE is a special case-insensitive type of VARCHAR

The NUMERIC data type is a synonym for fixed-point DECIMAL.
DECIMAL(p[, s]) and NUMERIC(p[, s])

Fixed precision and scale numbers.
p (precision)
Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision.
s (scale)
Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p.

[Villeroy]

TINYINT -128 to 127 [1 Byte = 2^8 different values]
SMALLINT -16.384 to 16.383 [2 Byte = 2^16 different values]
INTEGER -268.435.456 to 268.435.455 [4 Byte = 2^32 different values]
BIGINT -72.057.594.037.927.900 to 72.057.594.037.927.900 [8 Byte = 2^64 different values]

DOUBLE(FLOAT,REAL) 1.79769313486232 x 10E308 to 4.94065645841247 x 10E-324 [8 Byte with floating decimal point]

[GJM]

Holy Crap!

All I was trying to figure out is: Should I be using one over the other to show the Price of a product!

Thanks for the super detailed answer -- really I do mean it, you folks have been a great help so far!

I'll just take it that I can pretty much use the Decimal one and leave it at that.

Cheers,

GJM

[Villeroy]

If your column should be restricted to "small prices" -let's say up to 999.99- you want to use NUMERIC(5,2).

[GJM]

Ok, Thanks Villeroy.

Just out of curiosity, What is the Decimal format used for (that is the one the data is formatted to now).

Would it hurt to leave it? What problems could I encounter if I do?

Thanks

[Villeroy]

The NUMERIC data type is a synonym for fixed-point DECIMAL.
DECIMAL(p[, s]) and NUMERIC(p[, s])

Nitpicking: Don't mix formats with types. You can format a floating point number PI to be displayed as "3.14", "003.1", "€ 3,14", 314.159265358979%

[GJM]

Thanks again for the explanation Villeroy.

I will change the columns to be the Numeric data type and 2 decimal places.

May I ask, is there a similar reason for using Text [VARCHAR] v.s Text [VARCHAR_IGNORCASE] v.s. Text (fix) [CHAR]

Thanks again.

[maxqnz]

May I ask, is there a similar reason for using Text [VARCHAR] v.s Text [VARCHAR_IGNORCASE] v.s. Text (fix) [CHAR]

Thanks again.

This interests me, too. I can't find "(fix)" in the hsqldb documentation

[Villeroy]

Fields of type VARCHAR_IGNORECASE treat "OpenOffice.org" and "openoffice.org" as equal values. Similar to the numeric types you can limit the maximum count of chars in a string. Reducing types to the smallest possible size may reduce the probability of some errors while increasing the performance of lookups across large fields.

[maxqnz]

Fields of type VARCHAR_IGNORECASE treat "OpenOffice.org" and "openoffice.org" as equal values. Similar to the numeric types you can limit the maximum count of chars in a string. Reducing types to the smallest possible size may reduce the probability of some errors while increasing the performance of lookups across large fields.

So the (fix) type allows me to specifiy a maximum number of cahracters? THat is very useful, thanks.