How can a SaaS user find what SQL datatypes *really* are?

Kinetic ERP
1

TL;DR - Data Dictionary Viewer doesn’t have this detail.

BAQ is vague about datatypes, which leads to frequent footgun events for BAQ users with more than a little SQL experience. At least when doing valid nvarchar operations fails on a text datatype, the error message is usually enough to point someone who knows what datatypes are in the right direction. decimal on the other hand… There’s no indication that one is float or numeric or real or decimal(19,9) or decimal(12,2), and then you do math, and implicit conversion silently avoids your expected outcome. I do know how to poke and prod the facts out of BAQ, but the novelty’s worn off. Got any shortcuts?

2

There is a table called ZDataField. I believe this contains the data types for all the fields in all the tables.

1 Like
3

Occasionally, @hkeric.wci runs a program that documents the database:

2 Likes
4

Looks like ZDataField is the source of the datatype descriptions. Select distinct on that column only returns

bigint
bit
date
datetime
decimal
int
nchar
nvarchar
sysname
timestamp
uniqueidentifier
varbinary
xml

which any of us who’ve been scolded by BAQ that you can’t LEN a text datatype will scowl at.

I’m gonna check out @hkeric.wci 's useful contraption. Even if that doesn’t answer my question it looks like it’s super useful anyway. Thanks Mark!

1 Like