1

In UniVerse SQL. How to get the actual Max Len of a column.

I'm debugging an SSIS package and having trouble in ADO.Net Source. UniVerse SQL script gets an error of "UniVerse/SQL: Row Length Exceeds Buffer Size". I tried adjusting the Buffer size both in dataflow level and Connection Manager level.

Next steps is to get the max actual data length of each columns in the UniVerseSQL Script. Unfortunately, I dont have access to UniVerseSQL. I can only access it by querying in the ADO.Net Source in SSIS.

I'm looking for the same function as in SQL Server Query below: SELECT MAX(LEN(Column1))FROM Table1

FullMetalPanda
  • 46
  • 3

3 Answers3

0

Try

SELECT {fn LENGTH(Column1)} FROM Table1

webthaumaturge
  • 1,198
  • 1
  • 11
  • 23
0

I do it with using the EVAL keyword. I have found that I generally have to OCONV it it a masked string value so it will sort properly otherwise the highest value starting with 9 shows up. If you expect more the 99999 chars you will need to us MR%6 or whatever.

This example uses the @RECORD array notation that allows you to use the D type number to get the value without needing the Dictionary name and by extension bypassing Conversions.

SELECT MAX(EVAL "OCONV(LEN(@RECORD<2>),'MR%5')") FROM FileName;

If conversions are important you can use the dictionary name for it instead.

SELECT MAX(EVAL "OCONV(LEN(ColumnName),'MR%5')") FROM FileName;
Van Amburg
  • 1,207
  • 9
  • 15
0

The UniverseSQL function is CHARACTER_LENGTH (or abbreviated as CHAR_LENGTH). If the field is numeric it must be CAST to char first.

SELECT MAX(CHAR_LENGTH(FIELD)) FROM TABLE
Galaxiom
  • 109
  • 4