cancel
Showing results for 
Search instead for 
Did you mean: 

Cascading LOV issue

former_member203645
Active Participant
0 Kudos

Hi all,

I created a cascading Lov in Information Design Tool which is List of values based on a custom hierarchy.

I can able to view hierarchical lov in the universe. But when I try to run the report then I see the below error. Please advise.

Backend is Sybase IQ.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

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..

former_member203645
Active Participant
0 Kudos

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

Former Member
0 Kudos

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...

Former Member
0 Kudos

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

former_member203645
Active Participant
0 Kudos

@ Mishra, I tried one table before and it is working.

@Vivek , I ran sub query first  ( working ) and copied results to main query ( it is working again ).

But when I run the complete query then it is an issue.

Former Member
0 Kudos

One workaround could be..

Try creating the sub query as a view and then build your main query on that view?