"H�bschen, Peter" wrote:
>
> Hello,
>
> I've wrote a dbproc which gets back a cursor to make an
> analysis from my
> database, now I wanted to add a line to my where-clauses and
> ran into error
> 1117 ("Too complicated trigger qualification").
> I have to get back a table where in the first column (c1) is
> a result of a
> count and in the second column I need (c1/count(something) to
> get back a
> number expressed as a percentage. At the moment I need for
> such rows in one
> table, so I've implemented it through a union select
> At the moment I'm doing it as followed:
>
> CREATE DBPROC SP_db_analysis (IN var1 Smallint, IN var2 DATE,
> IN var3 DATE,
> IN var4 CHAR(2), IN var5 CHAR(2))
> RETURNS CURSOR AS
> $CURSOR = 'db_analysis_Cursor';
> DECLARE :$cursor CURSOR FOR
> Select result.t1, result.t1/result2.t2*100 from
> (Select count(foo) AS t1 FROM... WHERE...) result,
> (Select count(bar) AS t2 FROM... WHERE...) result2
>
> union
>
> Select result3.t3, result3.t3/result2.t2*100 from
> (Select count(foobar) AS t3 FROM... WHERE...) result3,
> (Select count(bar) AS t2 FROM... WHERE...) result2
>
> union
> ....
>
> There are 5 Select-Statements which are connected through a
> union and in
> every statement result2.t2 is the same. Can anyone tell me, how can I
> simplify these analysis to avoid the too complicated trigger
> qualification,
> if I add my line to the where clauses, which I need so badly.
>
> I don't know if I explained my problem well, but it's much our
> more difficult to
> describe than I thought.
>
> Thanks in advance for any help
> Peter
Your statement-size exceeds 8KB minus something --> you have
to shorten your statement.
This can be done (should be done because of performance) like this:
DECLARE t2result CURSOR FOR
(Select count(bar) AS t2 FROM... WHERE...)
DECLARE :$cursor CURSOR FOR
Select result.t1, result.t1/t2result.t2*100 from
(Select count(foo) AS t1 FROM... WHERE...) result, t2result
union
Select result3.t3, result3.t3/t2result.t2*100 from
(Select count(foobar) AS t3 FROM... WHERE...) result3, t2result
union
...
I hope this not-having-5-times of the second select will shorten
your statement enough to fit.
And it will not do the same work for every union-part.
Sometimes it is nice that MaxDB has the opportunity to REUSE
select-results in from-clauses of further select.
Good luck
Elke
SAP Labs Berlin
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
