AW: SQL code to find the longest line

I can't remember the exact syntax off hand but you will find it in the

archives.
It's quite easy to find: All SQL Messages are located within the QSQLMSG
Message File. The message Id is composed by SQL + absolute value of the
SQLCODE (i.e. SQLCODE -204 results in message id SQL0204 --> &1 in &2 type
*&3 not found. )

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: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Dave
Gesendet: Wednesday, 24.10 2012 11:38
An: RPG programming on the IBM i / System i
Betreff: Re: SQL code to find the longest line

Also, use the GET DIAGNOSTICS statement with the Message_text parameter. It
should give you a perfectly understandable message. I can't remember the
exact syntax off hand but you will find it in the archives.

2012/10/24 aec <cfuture@xxxxxxxxxxx>

One thing, Erick, those SQL statements don't need to be run Execute
Immediate, you get better performance just running the statement itself:

exec sql DROP ALIAS QTEMP/AA;
exec sql CREATE ALIAS QTEMP/AA FOR QTEMP/DISP(DISP);

But using embedded SQL, you need to "Select/Into/" some host variable.
It has to return one row only, but your statement does that.

exec sql SELECT MAX( LENGTH( TRIM(DISP) ) ) INTO :MyVar FROM QTEMP/AA

Where after executed, MyVar should have the value of the length of the
longest line.






On 10/23/12 7:48 PM, Erick Garske wrote:

I have a SQLPRGLE program compiled to a target release of V5R4M0
that works correctly in V7R1M0, but throws a SQL exception when
processing the prepare statement. The purpose of the code is to
simply find the longest line in the file.

/ Eval cmdSQL = 'DROP ALIAS QTEMP/AA';
/ exec sql Execute Immediate :cmdSQL;
/
/ Eval cmdSQL = 'CREATE ALIAS QTEMP/AA FOR QTEMP/DISP(DISP)';
/ exec sql Execute Immediate :cmdSQL;
/
/ cmdSQL = 'SELECT MAX( LENGTH( TRIM(DISP) ) ) FROM QTEMP/AA';
/ exec sql prepare s1 from :cmdSQL;

SQL values after processing the prepare statement:

sqlcod = 204
sqlerm =
....5...10...15...20...25...30...35...40...45...50...55...60
' *ALL *N MEM '
' '

Does anyone know if there is an issue with the above code?

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.