× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



On 07-Jan-2015 18:25 -0600, Buck Calabro wrote:
I was running in debug for other reasons and I saw SQL7919 reason
code 5 in the job log. The indicated column in error was CITY. In
my case, it was SELECT ... COALESCE(CITY, ' ') FROM a whopping load
of LEFT JOINs. The ellipsis indicates about 40 columns omitted for
clarity.

SQL7919 rc5 indicates that the incoming data is VARCHAR but
the destination variable is CHAR. There are no VARCHAR
columns in any of the tables in this SQL statement, and none
as the target of the FETCH INTO. After a fruitless search of the
Knowledge Center, I decided to brute force it for the moment and did
SELECT CAST(COALESCE(CITY, ' ') as CHAR(30)). Now CITY was OK, but
STATE was throwing the msg SQL7919 rc5.

Clearly something wasn't right. After some more manual / forum /
email searches, I assumed that the SQL7919 was bogus, gave up and
went for another brute force solution. One by one I started taking
columns out of the cursor and fetch until it ran without error.

It turns out it was a size mis-match - and SQLSTATE was 01004 the
whole time. I should have trusted the SQLSTATE from the beginning,
although there is virtually no hint from the database as to WHICH
columns are being truncated.

Bottom line: Trust SQLSTATE. Don't trust the messages issued in
debug mode.


There was nothing incorrect that I can see about the described scenario. If the concern is for the column named in the message, then realize that the expression\column-list of the SELECT has a column-name only for those expressions in the list that are merely a column-name, and thus for any other expression a column name can only be derived\inferred from just one [and is likely to be the first] column _named in_ the expression exhibiting the issue.

If CITY is CHAR, the reason the expression "COALESCE(CITY, ' ')" is typed by the SQL as VARCHAR is because the literal [what the DB2 for i SQL calls a constant] value of the single blank is implicitly typed as VARCHAR; in effect, specifying the literal is the equivalent of having specified explicitly, the expression VARCHAR(' '). That is documented here:

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzseldesc.htm>
_Data types of result columns_
"...
_When the expression is_: | _Data type of result column_:
...
character-string constant length n | VARCHAR(n)
...
"

For resolution\avoidance of the messaging in debug [or an SQLWARN], either the result of the desired expression could be cast to CHAR [as shown was done to resolve the concern], or the literal itself could be cast to CHAR to ensure that the overall result will be CHAR. Thus an alternate resolution to the one chosen\shown could be one of the following [where CHAR casting scalar function could be replaced with the CAST scalar function]:
SELECT COALESCE(CITY, CHAR(' '))
SELECT COALESCE(CITY, CHAR(' ', 1))
SELECT COALESCE(CITY, CHAR(' ', # /* where #=length(CITY) */))


As an Amazon Associate we earn from qualifying purchases.

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-2024 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.