Re: RE: Bug with model clause and bind variables...?

 Message from: Boneist 

I can confirm this is still an issue in v11.0.0.116

_______________________________________

Historical Messages 


Author: Boneist
Date: Wed Oct 12 08:20:47 PDT 2011
I can confirm this is still an issue in v11.0.0.116
__

Author: Michael Staszewski
Date: Thu Jul 14 06:24:30 PDT 2011




 

I can reproduce this in the current beta as well. I'll take a peek at it.

Thanks,

Michael


__

Author: Boneist
Date: Thu Jul 14 05:52:22 PDT 2011
 I've got an example query that I'm trying to run in the editor in Toad v10.0:

   with  my_tab  as   (select   1   id,   100  cnt  from  dual  union   all select   2   id,   30  cnt  from  dual  union   all select   3   id,   40  cnt  from  dual  union   all select   4   id,   5  cnt  from  dual  union   all select   5   id,   6  cnt  from  dual  union   all select   6   id,   99  cnt  from  dual  union   all select   7   id,   1  cnt  from  dual  union   all select   8   id,   1  cnt  from  dual )   select  * from  my_tab mt model partition   by   (: threshold threshold ) dimension   by   (id) measures   ( cnt ,   0  cumulative_count ,   1  grp ) ( cumulative_count[ any ]  order   by   id   =  cnt[ cv() ]  +   case   when  cnt[ cv() ]  >   cv( threshold )   then 1 / 0 when  cnt[ cv() ]  +   nvl( cumulative_count[ cv()- 1 ] , 0 )   >   cv( threshold )   then 0 else   nvl( cumulative_count[ cv()- 1 ] , 0 ) end, grp[ any ]  order   by   id   =   nvl( grp[ cv()- 1 ] , 0 )   +   case   when  cumulative_count[ cv() ]  =  cumulative_count[ cv()- 1 ]  +  cnt[ cv() ]  then 0 else   1 end); 

When I run it via execute statement with the "Prompt for substitution variables" option ticked, it pops up the provide values window, but there's no variable listed. 
If I change it from :threshold to &threshold, it works as expected. It doesn't seem to matter if I put a space between the left bracket and the colon, either, yet if I put ":threshold" elsewhere in the query (ie. not in the model clause), it pops up correctly (but the value is not passed through to my original bind variable). 
Is this a bug?


ETA: I should add that when I declare the variable and assign a value to it and then run it through as a script, it works with the bind variable, both in Toad and SQL*Plus, so I think it's a Toad-ism, rather than a problem with the version of the db / client I'm using (10.2.0.4).

Message was edited by: Boneist
__
_______________________________________