Skip to Content
author's profile photo Former Member
Former Member

Avoid SQL subquery when use List of values based on a custom hierarchy.

Hi all,

in my Universe i have created a hierarchical prompt (following This Tutorial)

-Step 1: created a List of values based on a custom hierarchy:

-Step 2: associate the LOV at the object that i want to use in my prompt:

-Step 3: To avoid SQL query generation concatenates the hierarchy level fields in the SQL, associate a PRIMARY KEY (or Index Awareness) on the level dimensions that the hierarchy LOV consists:

This is the prompt i get:

Now i am able to select multiple values in all hierarchy level.

The problem is the quary that bo generate for this type of prompt:

SELECT
  ESTA_TERR_ORG_T_ORG_DT.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_DT.ORG_DESC,
  ESTA_TERR_ORG_T_ORG_ES.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_ES.ORG_DESC,
  ESTA_TERR_ORG_T_ORG_ZO.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_ZO.ORG_DESC,
  ESTA_TERR_ORG_T_ORG_GO.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_GO.ORG_DESC,
  ESTA_TERR_ORG_T_ORG_CF.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_CF.ORG_DESC
FROM
  TERR_ORG.T_ORG  ESTA_TERR_ORG_T_ORG_DT,
  TERR_ORG.T_ORG  ESTA_TERR_ORG_T_ORG_ES,
  TERR_ORG.T_ORG  ESTA_TERR_ORG_T_ORG_ZO,
  TERR_ORG.T_ORG  ESTA_TERR_ORG_T_ORG_GO,
  TERR_ORG.T_ORG  ESTA_TERR_ORG_T_ORG_CF,
  TERR_ORG.T_ORG_RELATE  ESTA_TERR_ORG_T_OR_REL_DT,
  TERR_ORG.T_ORG_RELATE  ESTA_TERR_ORG_T_ORG_REL_ES,
  TERR_ORG.T_ORG_RELATE  ESTA_TERR_ORG_T_OR_REL_ZO,
  TERR_ORG.T_ORG_RELATE  ESTA_TERR_ORG_T_OR_REL_CF
WHERE
  ( ESTA_TERR_ORG_T_ORG_CF.ORG_ID=ESTA_TERR_ORG_T_OR_REL_CF.ORG_ID_2(+)  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_CF.ORG_ID_1=ESTA_TERR_ORG_T_ORG_GO.ORG_ID(+)  )
  AND  ( ESTA_TERR_ORG_T_ORG_GO.ORG_ID=ESTA_TERR_ORG_T_OR_REL_ZO.ORG_ID_2(+)  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_ZO.ORG_ID_1=ESTA_TERR_ORG_T_ORG_ZO.ORG_ID(+)  )
  AND  ( ESTA_TERR_ORG_T_ORG_ZO.ORG_ID=ESTA_TERR_ORG_T_ORG_REL_ES.ORG_ID_2(+)  )
  AND  ( ESTA_TERR_ORG_T_ORG_REL_ES.ORG_ID_1=ESTA_TERR_ORG_T_ORG_ES.ORG_ID(+)  )
  AND  ( ESTA_TERR_ORG_T_ORG_ES.ORG_ID=ESTA_TERR_ORG_T_OR_REL_DT.ORG_ID_2(+)  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_DT.ORG_ID_1=ESTA_TERR_ORG_T_ORG_DT.ORG_ID(+)  )
  AND  ( ESTA_TERR_ORG_T_ORG_CF.END_DAT= to_date('31/12/2999','DD/MM/YYYY') AND ESTA_TERR_ORG_T_ORG_CF.ORG_TYPE = 'CF'  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_CF.END_DAT=  to_date('31/12/2999','DD/MM/YYYY')  )
  AND  ( ESTA_TERR_ORG_T_ORG_GO.END_DAT=to_date('31/12/2999','DD/MM/YYYY') AND ESTA_TERR_ORG_T_ORG_GO.ORG_TYPE = 'GO'  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_ZO.END_DAT=to_date('31/12/2999','DD/MM/YYYY')  )
  AND  ( ESTA_TERR_ORG_T_ORG_ZO.END_DAT= to_date('31/12/2999','DD/MM/YYYY') AND ESTA_TERR_ORG_T_ORG_ZO.ORG_TYPE = 'ZO'  )
  AND  ( ESTA_TERR_ORG_T_ORG_REL_ES.END_DAT = to_date('31/12/2999','DD/MM/YYYY')  )
  AND  ( ESTA_TERR_ORG_T_ORG_ES.END_DAT = to_date('31/12/2999','DD/MM/YYYY') AND ESTA_TERR_ORG_T_ORG_ES.ORG_TYPE = 'ES'  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_DT.END_DAT = to_date('31/12/2999','DD/MM/YYYY')  )
  AND  ( ESTA_TERR_ORG_T_ORG_DT.END_DAT = to_date('31/12/2999','DD/MM/YYYY') AND ESTA_TERR_ORG_T_ORG_DT.ORG_TYPE = 'DT'  )
  AND  
  ( ESTA_TERR_ORG_T_ORG_CF.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_CF.ORG_DESC )  IN  ( SELECT
  ( ESTA_TERR_ORG_T_ORG_CF.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_CF.ORG_DESC )
FROM
  TERR_ORG.T_ORG  ESTA_TERR_ORG_T_ORG_CF,
  TERR_ORG.T_ORG  ESTA_TERR_ORG_T_ORG_ES,
  TERR_ORG.T_ORG  ESTA_TERR_ORG_T_ORG_DT,
  TERR_ORG.T_ORG  ESTA_TERR_ORG_T_ORG_ZO,
  TERR_ORG.T_ORG_RELATE  ESTA_TERR_ORG_T_ORG_REL_ES,
  TERR_ORG.T_ORG_RELATE  ESTA_TERR_ORG_T_OR_REL_ZO,
  TERR_ORG.T_ORG  ESTA_TERR_ORG_T_ORG_GO,
  TERR_ORG.T_ORG_RELATE  ESTA_TERR_ORG_T_OR_REL_CF,
  TERR_ORG.T_ORG_RELATE  ESTA_TERR_ORG_T_OR_REL_DT
WHERE
  ( ESTA_TERR_ORG_T_ORG_CF.ORG_ID=ESTA_TERR_ORG_T_OR_REL_CF.ORG_ID_2(+)  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_CF.ORG_ID_1=ESTA_TERR_ORG_T_ORG_GO.ORG_ID(+)  )
  AND  ( ESTA_TERR_ORG_T_ORG_GO.ORG_ID=ESTA_TERR_ORG_T_OR_REL_ZO.ORG_ID_2(+)  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_ZO.ORG_ID_1=ESTA_TERR_ORG_T_ORG_ZO.ORG_ID(+)  )
  AND  ( ESTA_TERR_ORG_T_ORG_ZO.ORG_ID=ESTA_TERR_ORG_T_ORG_REL_ES.ORG_ID_2(+)  )
  AND  ( ESTA_TERR_ORG_T_ORG_REL_ES.ORG_ID_1=ESTA_TERR_ORG_T_ORG_ES.ORG_ID(+)  )
  AND  ( ESTA_TERR_ORG_T_ORG_ES.ORG_ID=ESTA_TERR_ORG_T_OR_REL_DT.ORG_ID_2(+)  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_DT.ORG_ID_1=ESTA_TERR_ORG_T_ORG_DT.ORG_ID(+)  )
  AND  ( ESTA_TERR_ORG_T_ORG_CF.END_DAT= to_date('31/12/2999','DD/MM/YYYY') AND ESTA_TERR_ORG_T_ORG_CF.ORG_TYPE = 'CF'  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_CF.END_DAT=  to_date('31/12/2999','DD/MM/YYYY')  )
  AND  ( ESTA_TERR_ORG_T_ORG_GO.END_DAT=to_date('31/12/2999','DD/MM/YYYY') AND ESTA_TERR_ORG_T_ORG_GO.ORG_TYPE = 'GO'  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_ZO.END_DAT=to_date('31/12/2999','DD/MM/YYYY')  )
  AND  ( ESTA_TERR_ORG_T_ORG_ZO.END_DAT= to_date('31/12/2999','DD/MM/YYYY') AND ESTA_TERR_ORG_T_ORG_ZO.ORG_TYPE = 'ZO'  )
  AND  ( ESTA_TERR_ORG_T_ORG_REL_ES.END_DAT = to_date('31/12/2999','DD/MM/YYYY')  )
  AND  ( ESTA_TERR_ORG_T_ORG_ES.END_DAT = to_date('31/12/2999','DD/MM/YYYY') AND ESTA_TERR_ORG_T_ORG_ES.ORG_TYPE = 'ES'  )
  AND  ( ESTA_TERR_ORG_T_OR_REL_DT.END_DAT = to_date('31/12/2999','DD/MM/YYYY')  )
  AND  ( ESTA_TERR_ORG_T_ORG_DT.END_DAT = to_date('31/12/2999','DD/MM/YYYY') AND ESTA_TERR_ORG_T_ORG_DT.ORG_TYPE = 'DT'  )
  AND  
  (
   (
    ESTA_TERR_ORG_T_ORG_ES.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_ES.ORG_DESC  =  'FF20 - C.C. Catalunya'
    AND
    ESTA_TERR_ORG_T_ORG_DT.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_DT.ORG_DESC  =  'FF00 - Filial HFL'
   )
   OR
   (
    ESTA_TERR_ORG_T_ORG_ZO.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_ZO.ORG_DESC  =  'FF2A - Zona Lleida HLF'
    AND
    ESTA_TERR_ORG_T_ORG_ES.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_ES.ORG_DESC  =  'FF20 - C.C. Catalunya'
    AND
    ESTA_TERR_ORG_T_ORG_DT.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_DT.ORG_DESC  =  'FF00 - Filial HFL'
   )
  )
 )

As you see the query present a IN clause referred to a subquery:

AND  
  ( ESTA_TERR_ORG_T_ORG_CF.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_CF.ORG_DESC )  IN  ( SELECT
  ( ESTA_TERR_ORG_T_ORG_CF.ORG_COD||' - '||ESTA_TERR_ORG_T_ORG_CF.ORG_DESC )
FROM

This approach cause additional load. This is not efficient SQL to be run against the data source.

Is there a way to prevent BO from generating the query in this way? It's possible to avoid the use of subqueries? or alternatively how i can force BO to generate a more powerful SQL code?


Thanks for support.

untitled.jpg (138.4 kB)
untitled.jpg (136.1 kB)
2.jpg (199.6 kB)
3.jpg (94.3 kB)
3.jpg (128.4 kB)
4.jpg (137.5 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

0 Answers

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.