Midrange News for the IBM i Community

Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Is the SQL SELECT Stmt in CL Needed?
has no ratings.
Published: 24 Oct 2013
Revised: 29 Oct 2013 - 3776 days ago
Last viewed on: 01 Mar 2024 (4900 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Is the SQL SELECT Stmt in CL Needed? Published by: Bob Cozzi on 24 Oct 2013 view comments(2)

Back at least 20 years ago I wrote and published the RUNSQL command. Later I created a low-cost SQL application named "SQL Lite". IBM i (OS/400) shops have been using it for some 20 years now.

Earlier this year or late last year, IBM announced a reduced function RUNSQL command. Basicaly is provided support for the non-SELECT statements. Its introduciton caused a lot of issues for end-users who not only used my SQL Lite RUNSQL command but who had read my articles and written their own version--also naming it RUNSQL. Effectively IBM's RUNSQL was a non-starter because it only did what everybody who needed it, already could do, and it didn't do the SELECT statement.

I was playing around with the COZTOOLS' SQL Service Program and thought I could implement a new, modern SQL SELECT statement that could be run via a command. Turned out that this was easier than I had thought.

I wrote a simple test app, of about 20 lines of code, and was able to produce output from any SELECT statement. My initial tests directed the output to a text file in the IFS so that I could reveiw the results using the DSPF command or by FTP'ing the file to my PC and viewing it.

I thought this was pretty cool to be able to use my own product to produce a CL command that processes the SELECT statement and produces results. But of course "nobody needs it". So I'm wondering if something like the following would make it more useful. What do you think?

Create a CL command that contains the parameters for an SQL SELECT statement. THe parameter for the command would be:

  • FROM
  • JOIN

Similar to the Interactive SQL Prompter, this CL command would accept the components of an SQL SELECT statement as individual parameters. Then it would build the SELECT statement and run it. In addition the JOIN parmaeter would permit the entry of regular command-style qualified file names along with the "ON" parameter to more easily create a JOIN query.

Beyond that, the OUTPUT parameter would have the following options:

  • * - Display the output
  • *PRINT - Create a SPOOL file with the output
  • *OUTFILE - Create a database file from the output
  • *STMF - Create a StreamFile (IFS File) of the output

Of course the OUTFMT parameter would be used with the *STMF option to control the type of output. I'm thinking of the following options for OUTFMT:

  • *TEXT
  • *PDF
  • *CSV

For example:


With the additional parameters:


Do people still have a need for this, or should I just add it to my collection of cool things that nobody wants?






Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: clbirk
Premium member *
Comment on: Is the SQL SELECT Stmt in CL Needed?
Posted: 10 years 4 months 4 days 11 hours 43 minutes ago

while what you say sounds nice, am I missing something here. I got the impression that one could not do a select in CL. I must be missing your point because tonight I used the runsql (ibm's command) and wrote:


RUNSQL SQL('Insert into custsrv/stdxyz select ST4_ORDER#, +
ST4_SET from custsrv.stdtab4 where st4_stop != '' '' +
and st4_shpdat = ''0000-00-00'' order by st4_order#, +
st4_set') COMMIT(*NONE)

and it runs just fine. So are you talking about returning a whole set (i.e. select *) and then being able to do something with that?



Posted by: bobcozzi
Site Admin ****
Comment on: Is the SQL SELECT Stmt in CL Needed?
Posted: 10 years 4 months 4 days 9 hours 20 minutes ago

You're right, you can't do a Select that's what I said. I'm proposing building such a function mainly for interactive use, but it could be run in CL also.