How to build the SQL statement
LudyNov 16 2009 — edited Nov 16 2009Hi,
Below is the table and data, and i had done the solution using a cursor in pl/sql,
can this be accomplished in a SQL statemnt ?
create table TEMP_DISTINCT
(
A_ID NUMBER not null,
B_ID NUMBER not null,
C_VAL VARCHAR2(30)
)
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(111,2222,'One')
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(111,2222,'Two')
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(112,2222,'Three')
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(112,2222,'Four')
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(112,2221,'Five')
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(112,2221,'Six')
declare
vorgid varchar2(30);
cursor cur is
select distinct A_ID, B_ID from TEMP_DISTINCT
order by A_ID,B_ID;
begin
for curs in cur
loop
select C_VAL
into vorgid from TEMP_DISTINCT
where curs.A_ID = A_ID
and curs.B_ID=B_ID
and rownum = 1;
dbms_output.put_line(curs.A_ID|| ' : ' || curs.B_ID || ':' || substr(vorgid,1,22));
end loop;
end;
Output
-------------
111 : 2222:One
112 : 2221:Five
112 : 2222:Three