If you convert a fixed length character field by using the scalar function
COALESCE, the result is returned as VARCHAR.
When fetching this result into a fixed length field, data conversion occurs.
So message SQL7919 is correct.
Explicitly casting the result of COALESCE into a fixed length character
removed the message.
BTW message SQL7919 is not even a warning but only an information message.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Buck
Calabro
Gesendet: Thursday, 08.1 2015 01:25
An: midrange-l@xxxxxxxxxxxx
Betreff: Don't believe SQL debug joblog
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 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.
--
--buck
'I had nothing to offer anybody except my own confusion' - Jack Kerouac
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
