Q: For years, I've written static SQL Select statements in embedded SQL. When I know I'll only receive one row, I use Select Into because it's much simpler to code. Now I've been asked to do the same thing with dynamic SQL--but I can't get Select Into to work. Can I use a dynamic select statement without creating a cursor?

A: If you asked me this question a month ago, I'd have told you that a cursor is required. However, recently Birgitta Hauser posted a solution in the System iNetwork forums.

The trick is to wrap your Select Into statement inside a Values statement. So let's say you had the following trivial SQL statement that retrieves an employee name from an employee master file in an embedded SQL program:

   exec SQL   Select EmpName into :EmpName
                 from EmpMast
                where EmpNo=:EmpNo;

Now for some reason, you have to make that into a dynamic SQL statement. Let's say, for the sake of example, the name of the table can change, and that's why you need to use dynamic. (Actually, that's probably not a good reason to use dynamic, but just for the sake of example, let's say you did it . . .)

     D Table           s             21a   inz('EmpMast')
     D EmpNo           S              5p 0 inz(1234)
     D EmpName         s             23a
     D query           s           1000a   varying

      /free

          Query = 'Values( Select EmpName from ' + %Trim(Table) +
                         ' where EmpNo=? +
                         ) Into ?';

          Exec SQL  Prepare Stmt from :Query;
          Exec SQL  Execute Stmt Using :EmpNo, :EmpName;

All I've done is wrap my Select Into statement with the Values() statement. I told it that the employee number is a variable and that the Values() statement should read into a variable.

When I execute the statement, I provide the variables for EmpNo and EmpName, so it knows which variables to get the employee number from and which variable to load the employee name into.

If you like, you can read the thread in the System iNetwork forums, including Birgitta's answer.

Thanks for the information, Birgitta!