1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
| select c.ch,
(LENGTH(c.ch) - LENGTH(REPLACE(c.ch, '.', NULL))) as nb_occ,
c.pos_point,
instr(c.ch, '|', -pos_point, 1) pos_table,
decode(0,
instr(c.ch, '|', pos_point, 1),
length(c.ch) - pos_point,
instr(c.ch, '|', pos_point, 1) - pos_point - 1) as lg_champ,
decode(0,
instr(c.ch, '|', -pos_point, 1),
pos_point - 1,
pos_point - instr(c.ch, '|', -pos_point, 1) - 1) as lg_table
from (select B.ch_a_analyser as ch,
instr(b.ch_a_analyser, '.', 1, 1) as pos_point
from (select a.obj_name,
a.obj_select,
LENGTH(obj_select) as lg,
translate(obj_select, '(),+-=>< ', '|||||||||') as ch_a_analyser,
(LENGTH(obj_select) -
LENGTH(REPLACE(obj_select, '.', NULL))) as nb_occ
from (SELECT UNV_CLASS.CLS_NAME,
UNV_OBJECT.OBJ_NAME,
object_tables.TAB_NAME,
object_select1.OBJ_DATAVALUE ||
object_select2.OBJ_DATAVALUE as obj_select,
object_where1.OBJ_DATAVALUE ||
object_where2.OBJ_DATAVALUE as obj_where
FROM UNV_CLASS,
UNV_OBJECT,
UNV_TABLE object_tables,
UNV_OBJECT_DATA object_select1,
UNV_OBJECT_DATA object_select2,
UNV_OBJECT_DATA object_where1,
UNV_OBJECT_DATA object_where2,
UNV_UNIVERSE,
UNV_OBJ_TAB
WHERE (UNV_CLASS.UNIVERSE_ID(+) =
UNV_UNIVERSE.UNIVERSE_ID)
AND (UNV_OBJECT.UNIVERSE_ID = UNV_CLASS.UNIVERSE_ID)
AND (UNV_OBJECT.CLASS_ID = UNV_CLASS.CLASS_ID)
AND (object_select1.OBJECT_ID(+) =
UNV_OBJECT.OBJECT_ID)
AND (object_select1.UNIVERSE_ID(+) =
UNV_OBJECT.UNIVERSE_ID)
AND (object_select2.UNIVERSE_ID(+) =
object_select1.UNIVERSE_ID)
AND (object_select2.OBJECT_ID(+) =
object_select1.OBJECT_ID)
AND (object_where1.UNIVERSE_ID(+) =
UNV_OBJECT.UNIVERSE_ID)
AND (object_where1.OBJECT_ID(+) =
UNV_OBJECT.OBJECT_ID)
AND (object_where2.UNIVERSE_ID(+) =
object_where1.UNIVERSE_ID)
AND (object_where2.OBJECT_ID(+) =
object_where1.OBJECT_ID)
AND (object_where1.OBJ_DATATYPE(+) = 'W')
AND (object_select1.OBJ_DATATYPE(+) = 'S')
AND (object_select2.OBJ_DATATYPE(+) = 'S')
AND (object_where2.OBJ_DATATYPE(+) = 'W')
AND (object_select1.OBJ_SLICE(+) = 1)
AND (object_where1.OBJ_SLICE(+) = 1)
AND (object_select2.OBJ_SLICE(+) = 2)
AND (object_where2.OBJ_SLICE(+) = 2)
AND (UNV_OBJ_TAB.UNIVERSE_ID(+) =
UNV_OBJECT.UNIVERSE_ID)
AND (UNV_OBJ_TAB.OBJECT_ID(+) =
UNV_OBJECT.OBJECT_ID)
AND (object_tables.UNIVERSE_ID(+) =
UNV_OBJ_TAB.UNIVERSE_ID)
AND (object_tables.TABLE_ID(+) =
UNV_OBJ_TAB.TABLE_ID)
AND (UNV_UNIVERSE.UNI_LONGNAME = 'BOBILAN')) a) b) C |
Partager