Hi Justin,

I'm stumped on that one. I pull QSYS2.SYSTABLES.TABLE_TEXT using SQL
without CAST all the time without issues. The SQL environments I typically
do that are Navigator's Run SQL Script, and server side SQL objects.

Makes me think there's a second place somewhere in the System Catalog that
I've not noticed, that GENERATE_SQL() will fetch a label from if the
primary location TABLE_TEXT is blank. I've used Navigator's generate SQL a
fair amount but not GENERATE_SQL(), so I don't know if its behavior differs.

Mike



date: Thu, 17 Dec 2015 16:14:13 +0000
from: Justin Dearing <zippy1981@xxxxxxxxx>
subject: Displaying the contents of QSYS2.SYSTABLES.TABLE_TEXT

TL;DR: SYSTABLES.TABLE_TEXT is supposed to contain table labels. I know
this table has a label. I can't get strsql or sqlworkbench to display
anything in that field.

I have a query like so:

SELECT
TABLE_SCHEMA AS OBJ_SCHEMA, TABLE_NAME AS OBJ_NAME,
CASE TABLE_TYPE
WHEN 'P' THEN 'TABLE'
WHEN 'T' THEN 'TABLE'
WHEN 'L' THEN 'VIEW'
WHEN 'V' THEN 'VIEW'
END AS OBJ_TYPE,
TABLE_TEXT AS OBJ_TEXT
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA='FOO'


That's actually inside a CTE (window function) and the main query generates
some GENERATE_SQL() calls. I hope to post a polished version of this later.
TABLE_TEXT seems to always become an empty string, but if I run
GENERATE_SQL() on these tables, a LABEL statement gets generated.

The docs say the following about TABLE_TEXT:

TABLE_TEXTLABELVARGRAPHIC(50) CCSID 1200A character string provided with
the LABEL statement.
I tried the following, and still get no results:
CAST(TABLE_TEXT AS VARCHAR(25))
VARCHAR(TABLE_TEXT). I've used sqlworkbench, and strsql and the field is
always blank. I would figure the green screen would be more forgiving in
this case.

What am I doing wrong?

Justin


This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].