Topic
6 replies Latest Post - ‏2012-10-20T07:41:38Z by B.Hauser
Junius
Junius
17 Posts
ACCEPTED ANSWER

Pinned topic Stored Procedure Error Msg - SQL0090 Host variable not permitted

‏2012-10-16T15:51:44Z |
Hello All,

Does anyone know why I can't use a variable in the stored procedure below. The SP fails at compile time with the message SQL0090 - "Host variable not permitted here". The variable is a date (Str_Dt). If I hard code a date (instead of the variable), the procedure compiles, but not when I use a variable.


Create or Replace Procedure CFO_Daily_sp ( IN Str_Dt Decimal(6,0), IN End_Dt Decimal(6,0) ) Language SQL sp: Begin   Declare Str_date  Numeric(6,0); Declare End_date  Numeric(6,0);   Declare global temporary table LastNight   as (   Select * from nsbflatsav where substr(flat,1,12) in ( select substr(flat,1,12) from nsbflatsav where substr(flat,16,1) = 
'H'  and substr(flat,40,2)||substr(flat,32,2)||substr(flat,35,2) = digits(Str_Dt))) with data  ;   End


The variable in question is "Str_Dt".

Our system is version 7.1 and we're fairly current on the PTFs, except for the lastest technical refresh (TR5) which just came out last week.
Updated on 2012-10-20T07:41:38Z at 2012-10-20T07:41:38Z by B.Hauser
  • B.Hauser
    B.Hauser
    222 Posts
    ACCEPTED ANSWER

    Re: Stored Procedure Error Msg - SQL0090 Host variable not permitted

    ‏2012-10-17T05:20:42Z  in response to Junius
    If you read the detailed message text of SQL0090 you'll discover, Host variables are not allowed in an Create Table Statement. DECLARE Global Temporary Table will create a table within the QTEMP library.

    You may try to build an execute the DECLARE Global Temporary Table Statement Dynamically:

    
    ... Declare  String SQL VarChar(1024); Declare  Str_Dt_Digits Char(6);   Set Str_Dt_Digits = Digits(Str_Dt); Set String = 
    'Declare global temporary table LastNight   as ( Select * from nsbflatsav where substr(flat,1,12) in ( select substr(flat,1,12) from nsbflatsav where substr(flat,16,1) = 
    'H'  and substr(flat,40,2)||substr(flat,32,2)||substr(flat,35,2) = 
    ''
    ' + Str_Dt_Digits + '
    '')) with data With Replace 
    ';   Execute Immediate String; ...
    


    Birgitta
    • krmilligan
      krmilligan
      353 Posts
      ACCEPTED ANSWER

      Re: Stored Procedure Error Msg - SQL0090 Host variable not permitted

      ‏2012-10-17T15:59:37Z  in response to B.Hauser
      My question would be is what's the purpose of creating a temporary table. Why not just return this data-set as a stored procedure result set?
      • Junius
        Junius
        17 Posts
        ACCEPTED ANSWER

        Re: Stored Procedure Error Msg - SQL0090 Host variable not permitted

        ‏2012-10-17T16:26:12Z  in response to krmilligan
        Hi Kent,

        The code I gave was just a preliminary step to a larger procedure. The following steps did a variety of selects and updates to a permanent table using the records in that result set.

        Thanks

        ps. I also enjoyed your session at Common on Query Optimization
        • krmilligan
          krmilligan
          353 Posts
          ACCEPTED ANSWER

          Re: Stored Procedure Error Msg - SQL0090 Host variable not permitted

          ‏2012-10-17T20:08:22Z  in response to Junius
          Glad to hear you enjoyed the query optimization session.
          I don't completely understand your process, but from the info that you shared I still think that a common table expression or SQL view can eliminate the need for creating & populating a temporary table.
    • Junius
      Junius
      17 Posts
      ACCEPTED ANSWER

      Re: Stored Procedure Error Msg - SQL0090 Host variable not permitted

      ‏2012-10-17T16:20:05Z  in response to B.Hauser
      Thank you Birgitta. I could not find any explanation of where host variables could be used (or not used) in the Redbook (on Stored Procedures). I have now searched out and found the IBM site that lists the SQLCODEs and their meanings.

      And thank you for the example of the "execute immediate" command, I was not aware of that technique.

      ps. I enjoy your "Database Modernization" session at Common last May.
      • B.Hauser
        B.Hauser
        222 Posts
        ACCEPTED ANSWER

        Re: Stored Procedure Error Msg - SQL0090 Host variable not permitted

        ‏2012-10-20T07:41:38Z  in response to Junius
        >> ps. I enjoy your "Database Modernization" session at Common last May.

        Thanks I'm always happy if someone likes my sessions and classes and if I could help or give new ideas.

        Birgitta