max length between two value marks in a multi value column

kamesh · Post by **kamesh** » Fri Jan 29, 2010 8:30 pm

RT_LOGxxx has a multi value column MSG.TEXT with output depth of 60T.

What would be the maximum length between two value marks? This is required to determine FMT format value to get the text in one line without any breaks.

Below statement works as expected when number of characters before the first value mark are less than 100 but fails when it exceeds. So I would like to know what value we can use for FMT so that irrespective of length all the TEXT is displayed in one line?

SELECT EVAL "FIELD(MSG.TEXT,CHAR(253),1)" AS EVENT FMT '100L' FROM RT_LOGxxx

ray.wurlod · Post by **ray.wurlod** » Sat Jan 30, 2010 5:14 am

There are no limits. The original UniVerse database storage concept allowed for completely arbitrary stucture sizes (limited only by machine resources).

Some log messages, particularly those returned from Microsoft ODBC data sources, are thousands of characters long.

kamesh · Post by **kamesh** » Sat Jan 30, 2010 8:24 am

Thank you Ray!
I am not a premium member but planning to be one very soon. Would you mind suggesting the alternative for my requirement so that below sql works without any line breaks for each row it returns irrespective of log message length.

SELECT EVAL "FIELD(MSG.TEXT,CHAR(253),1)" AS EVENT FMT '100L' FROM RT_LOGxxx

Please help me out! I really appreciate it!

ArndW · Post by **ArndW** » Sat Jan 30, 2010 8:38 am

Your are using "FMT '100L'" in your command, so if you have more than 100 characters the SELECT inserts a line break. It is easiest to filter those out yourself from UNIX or elsewhere.

kamesh · Post by **kamesh** » Sat Jan 30, 2010 9:30 am

Thank you ArndW!

I can easily filter it but I still need the complete message text as one line input to my looping logic in the UNIX script.

ArndW · Post by **ArndW** » Sat Jan 30, 2010 9:50 am

Your SELECT output is comin into your script on stdin, right? So just filter it with "tr -d '\n' " and you've achieved your goal.

kamesh · Post by **kamesh** » Sat Jan 30, 2010 11:19 am

That is true ArndW but that would also remove line breaks between each output row as well which I need to keep.

ArndW · Post by **ArndW** » Sat Jan 30, 2010 11:30 am

Change your query to

Code: Select all

SELECT EVAL "'*':FIELD(MSG.TEXT,CHAR(253),1)" AS EVENT FMT '100L' FROM RT_LOGxxx

Then convert the '*' character back into a <Lf> after removing all linefeeds; use some other character if the '*' appears in your text.

ray.wurlod · Post by **ray.wurlod** » Sat Jan 30, 2010 2:57 pm

There is no general solution, because there is an upper limit on the device width but there is no upper limit on the size of MSG.TEXT.

kamesh · Post by **kamesh** » Sat Jan 30, 2010 9:35 pm

Thank You Arnd & Ray!!!
I will work on Arnd suggestion to get what I required.