Hello all,

When tracing the SQLAGGR we get messages like this:

14.09.26 BT (FOC2565) THE OBJECT OF IF/WHERE CANNOT BE CONVERTED TO SQL
14.09.26 BT (FOC2566) WHERE WH$$$1 CANNOT BE CONVERTED TO SQL
14.09.26 BT (FOC2583) NON-RELATIONAL OPERAND IS INVALID FOR OPERATION OR
14.09.26 BT (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
14.09.26 BT (FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL

**I do not need info about WHY we get these warnings nor how to change the TABLE request to avoid it.**

I'm wondering whether there is some setting to STOP the request.
Our database-appliance can do it in 10 secs. Webfocus will take more than an hour to do the same.

So when we make an report I never want Webfocus to "fix" it.

Sample 1

TABLE FILE ours
SUM TURNOVER
    CNT.DST.CUSTOMER_ID AS 'CUSTOMERS'
BY WEEK
WHERE YEAR EQ 2012 OR 2013
END

This works fine within seconds... ( rows returned from database : 104 )

Sample 2

TABLE FILE ours
SUM TURNOVER
    CNT.DST.CUSTOMER_ID AS 'CUSTOMERS'
BY WEEK
WHERE YEAR EQ (2012) OR (2013)
END

This is something different. This is an expression. According to WebFocus ( or the adaptor ) this can not be translated.

Webfocus fires this query instead ( to be able to do the cnt.dst itself )

TABLE FILE ours
SUM TURNOVER
BY CUSTOMER_ID
BY WEEK
WHERE YEAR EQ 2012 OR 2013
END

This works fine within seconds... ( rows return : 50,000,000+ )
...but then it takes the Agents a very long time to determine the cnt.dst.


Once again I do not need hints about how to change the TABLE request to avoid is. Sometimes it's not so very clear ( parameter in a parameter in a parameter etc. ). It happens by 'accident'. And I do not want to have to test every query seperately with a trace.


I'm looking for something like:

SET HOLDONWARNING = ON

or some documentation about SQL settings? Optimizer hints and such... ?


Greets,
Dave

This message has been edited. Last edited by: Dave,

what happened to "skin a car"
When comparing a field to a constant, use IF not WHERE

Susannah,

Thanks for the reply.

But I'm not looking for an alternative. We have alternatives. I just don't want to have to trace every single query to check whether WebFocus decided it "cannot be converted to SQL".



Off topic on the "IF" statement.
We never use "IF". See help:

Tip: Unless you specifically require IF syntax (for example, to support legacy applications), we recommend using WHERE.

and

Note: The IF phrase alone cannot be used to create compound expressions by connecting simple expressions with AND and OR logical operators. Compound logic requires that the IF phrase be used with the DEFINE command, as described in Using Expressions. You can accomplish this more easily with WHERE.

Greets,
Dave

quote:

I just don't want to have to trace every single query to check whether WebFocus decided it "cannot be converted to SQL".

You don't have to. But you'll have to live with the inefficient SQL code that WebFOCUS may generate.

@Francis

And waiting for a query more then 45 min. instead of 2 seconds?

Not really and option, just because someone accidentally added parenthesis? Which in any other language would not make a difference?

WHERE YEAR EQ (2012) OR (2013)
instead of
WHERE YEAR EQ 2012 OR 2013

that's hard to live with, hope you agree.

I would like to know why you added the parentheses ?, can this test become quite complex, or is it always a list of years ?

If its a list of YEARs, then you can use:
WHERE YEAR EQ 2012 OR 2013
or
IF YEAR EQ 2012 OR 2013
or
WHERE YEAR IN (2012,2013)

If the list is in a file, then you can use:
WHERE YEAR IN FILE {ddname}
or
IF YEAR EQ ({ddname})

I think the parenteses confused the interpreter.

quote:

someone accidentally added parenthesis

IMHO: There are standards and syntactical rules which should be adhered to when coding. Not really room here for "accidental" code, is there? The code may be tightened-up someday where accidental coding may fail.

In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.    Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206

Waz,

as Doug quoted...

Problem is the TABLE usually looks like:

TABLE FILE ...
SUM ... ...
&BY_FIELDS
&GLOBAL_WHERES
&USER_WHERES
END

and those &...._WHERES are made up of several parts done by -INCLUDES etc.

...and again. We know how to fix it. It just took us some time to figure out that this was causing bad performance. Especially because it isn't that obvious that those parenthesis make the parser change it's mind.

Would be nice that we didn't have to find out 'the hard way' ( when in production ( bigger dataset ) -> users complain ).


but thanks for thinking along, seriously.

Perhaps you can stop such queries at the database side?

Most databases allow limiting the amount of resources a user is allowed to use. If such a query from such a user exceeds those limits, the query would be aborted.

I know it's not ideal, but if you wanted to check the SQL without actually executing it then look into SET XRETRIEVAL = OFF.

Don't forget to set it on again if you decide to run your code!

T

Hi Wep5622, that might actually be not such a bad idea... ...thanks.

@Tony A, Yeah that's what we use, thanks.

quote:

Yeah that's what we use

I guessed that would be the case, but still worth mentioning for those newbies who come across this topic in a few years when they use search .... They do use search don't they?

T