Solved

return concaternate values to each text item

Posted on 2014-12-24
19
185 Views
Last Modified: 2015-01-11
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

0
Comment
Question by:chalie001
  • 9
  • 7
  • 3
19 Comments
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 40523292
can you provide some sample data with table structure and expected result for the same.
0
 

Author Comment

by:chalie001
ID: 40523311
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
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 40523333
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
 

Author Comment

by:chalie001
ID: 40523804
so how will i populate values in my text item
0
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 40523829
Did you changed the SQL what earlier it was doing against the new one ?

How it is getting populated, check once.
0
 

Author Comment

by:chalie001
ID: 40523892
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
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 40523999
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
 

Author Comment

by:chalie001
ID: 40524024
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
 
LVL 20

Expert Comment

by:flow01
ID: 40524443
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:chalie001
ID: 40524562
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
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 40525115
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
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 40525123
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
 

Author Comment

by:chalie001
ID: 40527626
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
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 40530188
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
 
LVL 20

Expert Comment

by:flow01
ID: 40530561
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
 

Author Comment

by:chalie001
ID: 40531076
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
 

Author Comment

by:chalie001
ID: 40531078
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
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 40532050
<<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
 

Author Closing Comment

by:chalie001
ID: 40542951
hi thanks its working now ,you solution was the best way to do it
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now