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

Our parser has no support for the MODEL clause at this time. The issue was logged on 7/14 when it was reported and the item still has a status of open in our tracking system. I don't have a timeframe on when it will be addressed at this time, but I do know that the parser team is working on many improvements for their next major upgrade so it's possible that they've been holding off for that.

Michael

From: [email protected] [mailto:[email protected]] On Behalf Of Boneist
Sent: Wednesday, October 12, 2011 11:21 AM
To: [email protected]
Subject: [toad] 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
__
_______________________________________