Skip to Content
avatar image
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 comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers