return concaternate values to each text item

hi how can i spilt the concaternate return value so ech value can be in its own text item

i have define my textitem datablock Number of records display =15 so i what the concaternate return  value from lov to be attach to each itentext based on the value the query is returning
am in form 11R2

 

hi this how the lov attach values
my lov when button trigger is
BEGIN
      if SHOW_LOV('LOV311') THEN
  go_item('VIEW_OBJECT.ONAME');
  do_key('List_values');
  copy ('0', 'GLOBAL.save_mouse_record');
 
  END IF;
END;

my sql for lov is
   
WITH    by_parent    AS

    (

        SELECT    obj_parent

        ,         LISTAGG (obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  AS obj_children

        FROM      cal_erd_link

        GROUP BY  obj_parent

    )

    ,    by_child    AS

    (

        SELECT    obj_child

        ,         LISTAGG (obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  AS obj_parents

        FROM      cal_erd_link

        GROUP BY  obj_child

    )

    SELECT    NVL (p.obj_parent, c.obj_child)    AS obj_name

    ,         p.obj_children

    ,         c.obj_parents

    FROM             by_child   c

    FULL OUTER JOIN  by_parent  p  ON  p.obj_parent  = c.obj_child

    ;

Open in new window

chalie001Asked:
Who is Participating?
 
flow01Connect With a Mentor Commented:
<<select only obj_child as display item>>
sorry,  I'm  confusing you.  What I did mean is that if you use the wizard you should select all 3 columns of  CAL_ERD_LINK  for the block , but that you only select the obj_child  when making the layout of the child block and obj_parent of the parent_block.  It will be the only visible item of that block (so I choose the "display" ) , but the item should be enterable, queryable, and the records insertable, updateble and deletable.

I advised the wrong joins : it should be just the other way

The parent block should be populated with the records filtered by
select object_parent from cal_erd_link where object_child = :focus.obj_name  
The join condition in the relation-ship attached to the parent_block should be
COL_ERD_LINK.OBJECT_CHILD = CAL_OBJ.OBJ_NAME
In this block COL_ERD_LINK.OBJECT_PARENT should be the visible item

The child block should be populated with the records filtered by
select object_child from cal_erd_link where object_parent = :focus.obj_name  
The join condition in the relation-ship attached to the child block should be
COL_ERD_LINK.OBJECT_PARENT =  CAL_OBJ.OBJ_NAME
In this block COL_ERD_LINK.OBJECT_CHILD shoud be the visible item

ALL LOV'S should be based on the CAL_OBJ table :
you want to select an object to filter the focus block  (only while in query mode in the focus block) (the selected value should be assigned to  :focus.object_name)  : it should mimick the behaviour of  <enter query mode in the focus block> , enter the name of the object you want to select, < execute the query)
or
you want to select an object to add as a parent of a focussed object to the parent block  (insert mode)
or
you want to select an object to add as a child of a focussed object to the parent block  (insert mode)
In both last cases it is likely you want to exclude the value of OBJ_NAME of the focus block from your recordgroup
: select object_name from CAL_OBj WHERE object_name <> :focus.object_name (where focus is the name you choosed for your middle block  
to prevent a child to be its own parent
0
 
Swadhin RaySenior Technical Engineer Commented:
can you provide some sample data with table structure and expected result for the same.
0
 
chalie001Author Commented:
hi this is the sample data and table structure
create table CAL_OBJ 
( 
  obj_name    VARCHAR2(100) not null, 
  obj_type    VARCHAR2(50), 
  obj_title  VARCHAR2(50), 
  description VARCHAR2(500), 
  sub_system  VARCHAR2(100), 
  status      VARCHAR2(20) 
); 
alter table CAL_OBJ 
  add constraint CAL_OBJ_PK primary key (OBJ_NAME) 
   
   
create table CAL_ERD_LINK 
( 
  obj_child    VARCHAR2(100) not null, 
  obj_parent  VARCHAR2(100) not null, 
  obj_rel_type VARCHAR2(50) 
) 
alter table CAL_ERD_LINK 
  add constraint CAL_ERD_LINK primary key (OBJ_CHILD, OBJ_PARENT) 
     
  alter table CAL_ERD_LINK 
  add constraint CAL_CHILD_LINKFK foreign key (OBJ_CHILD) 
  references CAL_OBJ (OBJ_NAME); 
alter table CAL_ERD_LINK 
  add constraint CAL_PARENT_LINK_FK foreign key (OBJ_PARENT) 
  references CAL_OBJ (OBJ_NAME); 
 
insert into CAL_OBJ (obj_name, 
                        obj_type) 
                          values('MainObject', 
                                'Form'); 
                                 
                                 
            insert into CAL_OBJ (obj_name, 
                        obj_type) 
                          values('SecondObject', 
                                'Form'); 
                                 
                                 
            insert into CAL_OBJ (obj_name, 
                        obj_type) 
                          values('ThirdObject', 
                                'Form');   
                                 
                                 
              insert into CAL_OBJ (obj_name, 
                        obj_type) 
                          values('FourthObject', 
                                'Form'); 
                                 
                                 
    insert into CAL_OBJ (obj_name, 
                        obj_type) 
                          values('FirthObject', 
                                'Form'); 
                             
                         
                             
                             
                              insert into cal_erd_link values('SecondObject','MainObject',null); 
                              insert into cal_erd_link values('ThirdObject','SecondObject',null); 
                              insert into cal_erd_link values('MainObject','ThirdObject',null); 
                              insert into cal_erd_link values('FourthObject','ThirdObject',null); 
                              insert into cal_erd_link values('FirthObject','MainObject',null);  

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Swadhin RaySenior Technical Engineer Commented:
check this if this helps:

create table CAL_OBJ 
( 
  obj_name    VARCHAR2(100) not null, 
  obj_type    VARCHAR2(50), 
  obj_title  VARCHAR2(50), 
  description VARCHAR2(500), 
  sub_system  VARCHAR2(100), 
  status      VARCHAR2(20) 
); 
alter table CAL_OBJ 
  add constraint CAL_OBJ_PK primary key (OBJ_NAME) ;
   
   
create table CAL_ERD_LINK 
( 
  obj_child    VARCHAR2(100) not null, 
  obj_parent  VARCHAR2(100) not null, 
  obj_rel_type VARCHAR2(50) 
) ;
alter table CAL_ERD_LINK 
  add constraint CAL_ERD_LINK primary key (OBJ_CHILD, OBJ_PARENT) ;
     
  alter table CAL_ERD_LINK 
  add constraint CAL_CHILD_LINKFK foreign key (OBJ_CHILD) 
  references CAL_OBJ (OBJ_NAME); 
alter table CAL_ERD_LINK 
  add constraint CAL_PARENT_LINK_FK foreign key (OBJ_PARENT) 
  references CAL_OBJ (OBJ_NAME); 
 
insert into CAL_OBJ (obj_name, 
                        obj_type) 
                          values('MainObject', 
                                'Form'); 
                                 
                                 
            insert into CAL_OBJ (obj_name, 
                        obj_type) 
                          values('SecondObject', 
                                'Form'); 
                                 
                                 
            insert into CAL_OBJ (obj_name, 
                        obj_type) 
                          values('ThirdObject', 
                                'Form');   
                                 
                                 
              insert into CAL_OBJ (obj_name, 
                        obj_type) 
                          values('FourthObject', 
                                'Form'); 
                                 
                                 
    insert into CAL_OBJ (obj_name, 
                        obj_type) 
                          values('FirthObject', 
                                'Form'); 
                             
                         
                             
                             
                              insert into cal_erd_link values('SecondObject','MainObject',null); 
                              insert into cal_erd_link values('ThirdObject','SecondObject',null); 
                              insert into cal_erd_link values('MainObject','ThirdObject',null); 
                              insert into cal_erd_link values('FourthObject','ThirdObject',null); 
                              insert into cal_erd_link values('FirthObject','MainObject',null);  
															


WITH by_parent AS
		(SELECT  obj_parent
			,
				LISTAGG (obj_child, ',') WITHIN GROUP (
		ORDER BY obj_child) AS obj_children
						FROM cal_erd_link
		GROUP BY obj_parent
		)
	,
		by_child AS
		(SELECT  obj_child
			,
				LISTAGG (obj_parent, ',') WITHIN GROUP (
		ORDER BY obj_parent) AS obj_parents
						FROM cal_erd_link
		GROUP BY obj_child
		)
		SELECT  NVL (p.obj_parent, c.obj_child) AS obj_name
		,
			p.obj_children
		,
			c.obj_parents
					FROM by_child c
		FULL OUTER JOIN by_parent p
							ON p.obj_parent = c.obj_child ;
							
							
							                  
OBJ_NAME            OBJ_CHILDREN                      OBJ_PARENTS         
------------------- ---------------------------------- -------------------
MainObject          FirthObject,SecondObject           ThirdObject        
SecondObject        ThirdObject                        MainObject         
ThirdObject         FourthObject,MainObject            SecondObject       
FourthObject                                           ThirdObject        
FirthObject                                            MainObject         



create view pc as 
WITH by_parent AS
		(SELECT  obj_parent
			,
				LISTAGG (obj_child, ',') WITHIN GROUP (
		ORDER BY obj_child) AS obj_children
						FROM cal_erd_link
		GROUP BY obj_parent
		)
	,
		by_child AS
		(SELECT  obj_child
			,
				LISTAGG (obj_parent, ',') WITHIN GROUP (
		ORDER BY obj_parent) AS obj_parents
						FROM cal_erd_link
		GROUP BY obj_child
		)
		SELECT  NVL (p.obj_parent, c.obj_child) AS obj_name
		,
			p.obj_children
		,
			c.obj_parents
					FROM by_child c
		FULL OUTER JOIN by_parent p
							ON p.obj_parent = c.obj_child ;
							
							
SELECT distinct OBJ_NAME,  regexp_substr(OBJ_CHILDREN
                                      ,'[^,]+'
                                      ,1
                                      ,LEVEL) as OBJ_CHILDREN , OBJ_PARENTS
                    FROM pc
                  CONNECT BY LEVEL <= regexp_count(OBJ_CHILDREN
                                                  ,',') + 1 ; 
																									
																									
																									
																									

Open in new window

0
 
chalie001Author Commented:
so how will i populate values in my text item
0
 
Swadhin RaySenior Technical Engineer Commented:
Did you changed the SQL what earlier it was doing against the new one ?

How it is getting populated, check once.
0
 
chalie001Author Commented:
yes i did
i create a new lov using this sql
SELECT distinct OBJ_NAME,  regexp_substr(OBJ_CHILDREN
                                      ,'[^,]+'
                                      ,1
                                      ,LEVEL) as OBJ_CHILDREN , OBJ_PARENTS
                    FROM pc
                  CONNECT BY LEVEL <= regexp_count(OBJ_CHILDREN
                                                  ,',') + 1 ;

but is still the same
hi this how is been populated
0
 
Swadhin RaySenior Technical Engineer Commented:
You have the concatenation on "OBJ_CHILDREN" but you are showing the concatenation values on OBJ_PARENTS.

Which column you want to split , for example :
you have a string 'A,B'

then is this what you are expecting :
'A'
'B'

If this is the case then you need to apply for both columns or on only the column you want.

The sample example what you provided was only having the concatenation values on OBJ_CHILDREN
0
 
chalie001Author Commented:
i what to spilt both child and parent column only when i populate in textitem

i what them  to appear like this in textitem
MainObject
SecondObject

not like this
MainObject,SecondObject

i must only spilt if value are like this
MainObject,SecondObject
0
 
flow01Commented:
Hey chalie,  i don't know exactly your requirements but  i have the idea that you are making it more difficult then necessary.
In other questions I saw from you , you are trying to fit all information in 1 row per object and now you ask for splitting that same information into different items.

Is there a reason you don't use the master detail  functionality of oracle forms ?  Using the create block wizard

1 master block  on CAL_OBJ   with a display of 1  
1 detail block on CAL_ERD_LINK  linking to the masterblock by the obj_child column with a display of 15
      selecting all columns , select only obj_child as display item
1 detail block on CAL_ERD_LINK  linking to the masterblock by the obj_parent column with a display of 15
      selecting all columns , select only obj_parent as display item
Hitting the query-key wil select the data of the focus block and the corresponding parents and childs already available.
By scrolling down in the focus block the parent and child block will be refreshed to show the data of the focus item.

Making obj_name of CAL_OBJ a queryble item you can enter search criteria in the name when in enter-query mode.
You could make a button available when in enter_query mode that opens an lov based on CAL_OBJ for fast selection of a focus item.

For adding childs
    create an LOV to the childname-item  based on a record-group selecting CAL_OBJ and returning the object_name in it to the name in the child block.
For adding parents
    create an LOV to the parentame-item  based on a record-group selecting CAL_OBJ and returning the object_name in it to the name in the parent block.
If needed provide a value to the obj_rel_type item in the on-insert_trigger.
0
 
chalie001Author Commented:
my requirement is when i select value from lov they must be populated like this

ParentObject                                       focus Object                                                  child Object
MainObject                                          ThirdObject                                                  
SecondObject

i what value to be populated like this when lov selected,currently is populating like this MainObject,SecondObject
0
 
Swadhin RaySenior Technical Engineer Commented:
Check this function which will split the string into columns try to use the same on your code :

CREATE OR REPLACE TYPE SL_VARCHAR2_ARRAY AS TABLE OF VARCHAR2(4000);

create or replace function split_string (i_string IN VARCHAR2, i_delimiter IN VARCHAR2)
      RETURN SL_VARCHAR2_ARRAY PIPELINED
   IS
      x_idx    PLS_INTEGER;
      x_list   VARCHAR2 (32767) := i_string;
   BEGIN
      LOOP
         x_idx := INSTR (x_list, i_delimiter);

         IF (x_idx > 0)
         THEN
            PIPE ROW (SUBSTR (x_list, 1, x_idx - 1));
            x_list := SUBSTR (x_list, x_idx + LENGTH (i_delimiter));
         ELSE
            PIPE ROW (x_list);
            EXIT;
         END IF;
      END LOOP;

      RETURN;
   END split_string;
   

select * from table(split_string('a,b,c', ',')) ; 

Open in new window


COLUMN_VALUE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
-----------
a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
b                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
c   

Open in new window

0
 
Swadhin RaySenior Technical Engineer Commented:
Also check this :

SELECT OBJ_NAME, REGEXP_SUBSTR(OBJ_CHILDREN,'[^,]+') OBJ_CHILDREN_1,
 REGEXP_SUBSTR(OBJ_CHILDREN,'[^,]+',1,2) OBJ_CHILDREN_2, 
  REGEXP_SUBSTR(OBJ_PARENTS,'[^,]+') OBJ_PARENTS_1,
 REGEXP_SUBSTR(OBJ_PARENTS,'[^,]+',1,2) OBJ_PARENTS_2
 from pc;

Open in new window

0
 
chalie001Author Commented:
this is what i what when lov value is selectedthis is what i what why must i spilt values because not all values will need to be spilt i just what value to be in each own itemtext can you check the picture some values will be in one text item and how will i spilt valies in my query using function this is my sql
select
c.obj_name, d.Obj_type, d.description , obj_childs, obj_parents
from
(select  obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs
 from
 (select distinct c.obj_name,
          oc.obj_name obj_child
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc
   where ec.obj_parent(+) = c.obj_name  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.obj_name(+) -- if no child relation found stil get an empty row to get empty child-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
 )
 group by obj_name
 ) c
,
(select obj_name, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (select distinct c.obj_name,
         op.obj_name obj_parent
   from cal_obj c,
   cal_erd_link ep ,
   cal_obj op
   where ep.obj_child(+)  = c.obj_name  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.obj_name(+) -- if no parent relation found stil get an empty row to get empty parent-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
 )
 group by obj_name
 ) p
, cal_obj d
where c.obj_name = p.obj_name
and   d.obj_name = c.obj_name

Open in new window



i don't what to spilt value returning by sql i only what to spilt when values is attach to textitem so each value can be in its own textitem
returnvalue1233.png
0
 
Swadhin RaySenior Technical Engineer Commented:
I think you should wait for the experts to answers, as I was thinking is the SQL can return you the split values then it would help  but you need to populate the values one it is been chooses on your field.
0
 
flow01Commented:
You can define a control block with a large text-item and  assign the result of the lov to that item.  You can  use the splitfunctions on that new item provided by the experts to assing a value to the different-items you want.
But I still think (see my earlier post) it will lead you nowhere: if the items are database items you will be filling new items with already existing values : primairy key violations will be waiting for you.
0
 
chalie001Author Commented:
hi i did this 1 master block  on CAL_OBJ   with a display of 1  
1 detail block on CAL_ERD_LINK  linking to the masterblock by the obj_child column with a display of 15
      selecting all columns , select only obj_child as display item
1 detail block on CAL_ERD_LINK  linking to the masterblock by the obj_parent column with a display of 15
      selecting all columns , select only obj_parent as display item
but value are in itentext when i put display item am geting this error
FRM-40106: NO NAVIGATION ITEM IN DESTINATION BLOCK

and another thing  am having an object as its own parent and child
value display as child and as its own parentvalued display as its own child this value in databse
0
 
chalie001Author Commented:
how will i do this so far

You could make a button available when in enter_query mode that opens an lov based on CAL_OBJ for fast selection of a focus item.

is this how i must do the lov button which sql must i use for my record group for lov the one i have
0
 
chalie001Author Commented:
hi thanks its working now ,you solution was the best way to do it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.