• 1 reply
  • Latest Post - ‏2013-07-25T17:13:52Z by krmilligan
20 Posts

Pinned topic Using a Host variable when creating a table

‏2013-07-24T18:22:02Z |

Is it possible to reference a host variable when using  "Declare global temporary table  XYZ  as ..."?

We are getting an error (SQL0090) when we try it and we don't understand why it's not allowed (or what we're doing wrong).  Here is the code from our RPG program;


       // Create Temporary Table to Collect Sales Data.

       exec sql declare global temporary tabletmpISSALES as

              ( selectSHCMP#,SHLOC#,SHTYPE,

                       substr(SHKEY#,1,4) asSHDEPT,

                       substr(SHKEY#,5,2) asSHCLAS,

                       substr(SHKEY#,7,3) asSHSCLS,











                 whereSHYEAR =:prmYear

SQL0090 Host variable not permitted here

                   andSHTYPE = 'N'

                 order bySHCMP#,SHLOC#) ;

We have a workaround, but it would be much more convenient if the example above would work (or something similar).




  • krmilligan
    450 Posts

    Re: Using a Host variable when creating a table


    No, it's not supported.  If you're running 7.1, you could use a global variable.

    However, I'm not a big fan of temporary tables for performance reasons.  Are you sure that you can't accomplish the same thing with a logical data set by using SQL Views or Common Table Expression(CTE)?  A CTE does allow host variable references.