When writing complex query blocks and using automatic totaling blocks, you may run into some difficulties with the ordering and grouping that you require and the ordering and grouping applied to the block's query when the break on columns and automatic total columns are defined.
For example, when a simple query is defined for a block and break on columns are selected, the following occurs:
The simple query before the break on columns and automatic totaling looks like this:
select A1,A2,B1,B2,S1,S2
from table a
where P1 = 'x___x'
order by A1, A2
If columns A1 and A2 are defined as break on columns, and column S1 and S2 are defined as automatic totaling columns, the query is modified to be the following when the report is generated:
select A1,A2,B1,B2,sum(S1) as S1,sum(S2) as S2,
grouping_id(A1) as GRP__A1, grouping_id(A2) as GRP__A2
from table a
where P1 = 'x___x'
order by A1, A2
group by rollup(A1,A2)
having grouping_id(A1)=grouping_id(A2)
order by A1 nulls last, A2 nulls last
In this case, the first order by (in bold above) is no longer valid
in its current location and will cause an error if the SQL statement is
run. However, sometimes that defined order by or
group by is necessary for the report data generated. If
this is the case, you can write the complex query so that the additional
code that the Xephr
So, in the example, you would rewrite your statement as
select A1,A2,B1,B2,S1,S2 from
(select A1,A2,B1,B2,S1,S2
from table a
where P1 = 'x___x'
order by A1, A2)
When the additional code is added for the break on columns and automatic totals, it will not affect the original query.
Remember that you must explicitly name the columns that you are selecting from the database in your query.
Xephr is a registered trademark of NDS Systems, LC.
Copyright