Home » SQL & PL/SQL » SQL & PL/SQL » need to different columns (oracle ,10.2,windows95)
need to different columns [message #576176] Fri, 01 February 2013 03:41 Go to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
Hi,
Below is returning rows in single column.I need to display in two different columns
SELECT * FROM TABLE(PLUSER.SPLIT('a,b,c'))
union all
SELECT * FROM TABLE(PLUSER.SPLIT('1,2,3'))
  this returning result like
a
b
c
1
2
3
  but need abc in one column and 1,2 3 in one column
a  1
b  2
c  3

i was tried like
 select * from TABLE(PLUSER.SPLIT('1,2,3')),(select * from TABLE(PLUSER.SPLIT('a,b,c')));
  but it's giving cartesion result like below
1	a
1	b
1	c
2	a
2	b
2	c
3	a
3	b
3	c

how i can

[EDITED by LF: fixed [code] tags and alignment]

[Updated on: Fri, 01 February 2013 04:19] by Moderator

Report message to a moderator

Re: need to different columns [message #576182 is a reply to message #576176] Fri, 01 February 2013 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post the code of SPLIT function as I already told you yesterday.
Please feedback to your yesterday topic, we don't know if in the end what I suggested works or not.
Please try the same suggestion as I made yesterday AND POST THE RESULT.

Regards
Michel
Re: need to different columns [message #576184 is a reply to message #576176] Fri, 01 February 2013 04:11 Go to previous messageGo to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
pls..

create or replace FUNCTION        SPLIT(v_list VARCHAR2,
                                        v_del VARCHAR2:=',') RETURN split_tbl pipelined
IS
    I_idx PLS_INTEGER;
    I_list VARCHAR(32767):=v_list;
    I_value VARCHAR2(32767);
BEGIN
    LOOP
        I_idx:=INSTR(I_list,v_del);
        IF I_idx>0 
        THEN
            pipe ROW(SUBSTR(I_list,1,I_idx-1));
            I_list:=SUBSTR(I_list,I_idx+LENGTH(v_del));
        ELSE
            pipe ROW(I_list);
            EXIT;
        END IF;
    END LOOP;
    
    RETURN;
END SPLIT; 
Re: need to different columns [message #576185 is a reply to message #576184] Fri, 01 February 2013 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace FUNCTION        SPLIT(v_list VARCHAR2,
  2                                          v_del VARCHAR2:=',') RETURN split_tbl pipelined
  3  IS
  4      I_idx PLS_INTEGER;
  5      I_list VARCHAR(32767):=v_list;
  6      I_value VARCHAR2(32767);
  7  BEGIN
  8      LOOP
  9          I_idx:=INSTR(I_list,v_del);
 10          IF I_idx>0 
 11          THEN
 12              pipe ROW(SUBSTR(I_list,1,I_idx-1));
 13              I_list:=SUBSTR(I_list,I_idx+LENGTH(v_del));
 14          ELSE
 15              pipe ROW(I_list);
 16              EXIT;
 17          END IF;
 18      END LOOP;
 19      
 20      RETURN;
 21  END SPLIT; 
 22  /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION SPLIT:
LINE/COL
---------------------------------------------------------------------------------
ERROR
------------------------------------------------------------------------------------------------
0/0
PL/SQL: Compilation unit analysis terminated
2/69
PLS-00201: identifier 'SPLIT_TBL' must be declared


Post ALL what is necessary to run the code.
And once again feedback to your yesterday topic.

Regards
Michel
Re: need to different columns [message #576188 is a reply to message #576185] Fri, 01 February 2013 04:20 Go to previous messageGo to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
create or replace TYPE "SPLIT_TBL" AS TABLE OF VARCHAR2(32767);
and yesterday query working fine for me..
Re: need to different columns [message #576189 is a reply to message #576188] Fri, 01 February 2013 04:24 Go to previous messageGo to next message
Littlefoot
Messages: 19619
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, what did you change since yesterday?
Re: need to different columns [message #576190 is a reply to message #576189] Fri, 01 February 2013 04:26 Go to previous messageGo to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
yesterday i'm sending list to other table,but today same function with two different lsit
Re: need to different columns [message #576216 is a reply to message #576176] Fri, 01 February 2013 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select a.column_value, b.column_value
  2  from (select column_value, rownum rn from table(split('1,2,3'))) a, 
  3       (select column_value, rownum rn from table(split('a,b,c'))) b
  4  where a.rn = b.rn
  5  /
COLUMN_VAL COLUMN_VAL
---------- ----------
1          a
2          b
3          c

3 rows selected.

Regards
Michel
Re: need to different columns [message #576348 is a reply to message #576190] Sun, 03 February 2013 23:13 Go to previous message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
Thank you michel for this silution.
Previous Topic: Logic to compare date with standard timestamp
Next Topic: Read different number of records.
Goto Forum:
  


Current Time: Fri Sep 19 09:18:14 CDT 2014

Total time taken to generate the page: 0.08939 seconds