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.

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