SQL Statement

[Guest.Visitor]

SELECT substr(tmcode,3,1) as tmhold FROM mailfile WHERE substr(tmcode,3,1) = 'B' If you want to select all of the other fields too, then: SELECT m.*, substr(tmcode,3,1) as tmhold FROM mailfile as m WHERE substr(tmcode,3,1) = 'B' Chris

[Diane Mueller]

Thanks Chris, that was the statement I was needing. Any suggestions on a good SQL book? Diane

[ukpi1b]

Books: SQL/400: A Professional Programmer's Guide Tim Martyn Richard Johnson Tim Hartley ISBN: 0070407991 Publisher: McGraw-Hill,1995 --------------------------- SQL/400 Developer's Guide ISBN: 1882419707 Paul Conte, Mike Cravitz Publisher: 29th Street Press/NEWS/400, 2000 IBM manuals http://publib.boulder.ibm.com/pubs/html/as400/v4r4/ic2924/info/db2/rbafymst02.htm#ToC http://www-919.ibm.com/servers/eserver/iseries/developer/db2/downloads/cpsqlref.pdf http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/apis/qsqgnddl.htm Good luck

[Guest.Visitor]

Not specifically for OS/400, but "SQL for Smarties" has some awesome examples.

[Diane Mueller]

Thanks for the suggestions! I will check out all these books. Diane

[H.Arner]

If I might be so bold there is also a book entitled iSeries and AS/400 SQL at Work which has extensive examples targeted to the AS/400 programmer. Information @ www.sqlthing.com Thanks Howard

[GlenKerner]

Just a thought, why not use Query Manager to build a SQL with the defined fields and see how the iSeries will do it. The following is a simple SQL that I generated from Query Manager: SELECT -- Columns A.ODDCEN, A.ODDDAT, A.ODDTIM, substr(a.odobtx,1,10) AS TEXT -- Tables FROM "GLENLIB"/"DSPOBJD" A I suspect that is you are doing it in Query/400 you can do it in Query Manager and then create the SQL you need.

[Diane Mueller]

I'm not a strong SQL person---and this should be rather easy, but I can't seem to find the statement that will let me create a temporary hold field with interactive SQL. For instance, I have a mail code field that I want to extract out the third character into a "holdmail" field; holdmail being created on the fly, not defined in the physical file. I tried "SELECT tmhold(tmcode,3,1) FROM mailfile WHERE tmhold = 'B'" and this won't work for me. I am very familiar with query on the iSeries that allows me to create work fields on the fly--should I lose this technique or is there an SQL keyword that I am missing for this to work? Diane