on 10-25-2013 9:19 PM
This issue seems to for Sybase IQ only and is a limitation of product for database ranking.
Please check note http://service.sap.com/sap/support/notes/14409771440977 - Error: 'Feature, RANK/NTILE/Window Aggregate nested in a subquery, Is not supported.-- (op...
No resolution yet for this..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Mishra,
That's interesting but the SAP note is explaining about the database ranking. But the issue I see is because of cascading LOV. Here is the query from webi. I am not dealing with any rank here .
Issue
SELECT
SALESPAID.SALESCODE,
RGT.SALESBOSSHIERARCHY_V.SALESBOSS
FROM
RGT.DIMAGENT_V SALESPAID INNER JOIN RGT.SALESBOSSHIERARCHY_V ON (RGT.SALESBOSSHIERARCHY_V.HGT = SALESPAID.HGT AND SALESPAID.AGENTSID = RGT.SALESBOSSHIERARCHY_V.AGENTSID)
WHERE
RGT.SALESBOSSHIERARCHY_V.SALESBOSS +'\'+ SALESPAID.SALESCODE IN ( SELECT
RGT.SALESBOSSHIERARCHY_V.SALESBOSS +'\'+ SALESPAID.SALESCODE
FROM
RGT.DIMAGENT_V SALESPAID INNER JOIN RGT.SALESBOSSHIERARCHY_V ON (RGT.SALESBOSSHIERARCHY_V.HGT = SALESPAID.HGT AND SALESPAID.AGENTSID = RGT.SALESBOSSHIERARCHY_V.AGENTSID)
WHERE
RGT.SALESBOSSHIERARCHY_V.SALESBOSS = '079'
AND
SALESPAID.SALESCODE = 'BFG'
)
COMMIT
----------------------------------------------------------------------------------------------
One more interesting stuff is the similar type of the query is working fine. this is coming from a single table.
Working
SELECT
REVENUE_DIMDATE.YEAR
FROM
NPH.DIMDATE REVENUE_DIMDATE
WHERE
REVENUE_DIMDATE.YEAR +'\'+ REVENUE_DIMDATE.YEARQTR IN ( SELECT
REVENUE_DIMDATE.YEAR +'\'+ REVENUE_DIMDATE.YEARQTR
FROM
NPH.DIMDATE REVENUE_DIMDATE
WHERE
(
REVENUE_DIMDATE.YEARQTR = '2002-Q3'
AND
REVENUE_DIMDATE.YEAR = '2002'
)
)
COMMIT
Very intriguing... both the queries which work and does not work look so similar...
Do they both work if you run on database client??
One other thing I notice not that it should matter; there is a table join the the sub query which does not work. Can you just try to have only one table in the sub query and see if that makes any difference...
Hi,
Just try an option to run the sub query first:-
( SELECT
RGT.SALESBOSSHIERARCHY_V.SALESBOSS +'\'+ SALESPAID.SALESCODE
FROM
RGT.DIMAGENT_V SALESPAID INNER JOIN RGT.SALESBOSSHIERARCHY_V ON (RGT.SALESBOSSHIERARCHY_V.HGT = SALESPAID.HGT AND SALESPAID.AGENTSID = RGT.SALESBOSSHIERARCHY_V.AGENTSID)
WHERE
RGT.SALESBOSSHIERARCHY_V.SALESBOSS = '079'
AND
SALESPAID.SALESCODE = 'BFG'
)
Collect the output and update the main query:-
SELECT
SALESPAID.SALESCODE,
RGT.SALESBOSSHIERARCHY_V.SALESBOSS
FROM
RGT.DIMAGENT_V SALESPAID INNER JOIN RGT.SALESBOSSHIERARCHY_V ON (RGT.SALESBOSSHIERARCHY_V.HGT = SALESPAID.HGT AND SALESPAID.AGENTSID = RGT.SALESBOSSHIERARCHY_V.AGENTSID)
WHERE
RGT.SALESBOSSHIERARCHY_V.SALESBOSS +'\'+ SALESPAID.SALESCODE IN ( OUTPUT FROM THE SUB QUERY)
COMMIT
This will help us to understand if in this way we are able to generate the report.
And the fix which is mentioned in the http://service.sap.com/sap/support/notes/1440977 is delivered in XI3.1 SP6 and BI4.1
We can also try to run the query from both Webi Rich Client as well as Webi from BI Launchpad (try both the modes)
Just wanted to make sure that HTML and RichInternetApplication are not behaving differently.
Thanks,
Vivek
User | Count |
---|---|
80 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.