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

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
    255 Posts

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

    ‏2012-10-17T05:20:42Z  
    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
    450 Posts

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

    ‏2012-10-17T15:59:37Z  
    • B.Hauser
    • ‏2012-10-17T05:20:42Z
    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:

    <pre class="jive-pre"> ... 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; ... </pre>

    Birgitta
    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

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

    ‏2012-10-17T16:20:05Z  
    • B.Hauser
    • ‏2012-10-17T05:20:42Z
    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:

    <pre class="jive-pre"> ... 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; ... </pre>

    Birgitta
    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.
  • Junius
    Junius
    17 Posts

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

    ‏2012-10-17T16:26:12Z  
    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?
    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
    450 Posts

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

    ‏2012-10-17T20:08:22Z  
    • Junius
    • ‏2012-10-17T16:26:12Z
    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
    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.
  • B.Hauser
    B.Hauser
    255 Posts

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

    ‏2012-10-20T07:41:38Z  
    • Junius
    • ‏2012-10-17T16:20:05Z
    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.
    >> 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