| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Return Primary Key with just one string...
Jimmy,
Here is an SQL I created to extract data from a table called SS. It was
designed to look for values found then deliver them to an output statement
as coma delimited
Also as I wanted the file to run at anytime and have a unique name to it,
the name is timestamped so I could know when and which one was the newest.
Mostly all you need is the last set of lines
Like most stuff I do I disavow any knowledge of it and leave it to you to to check and test it against a NON-CRITICAL database.
Ralph Button
rbutton_at_ctel.net
CUT -----------------------------------------------set echo off
create or replace function get_cell_value(ssid INT, colnum INT, rownum INT)
return varchar2 as res varchar2(60);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
select val into res from ss where ss_id=ssid and row_num=rownum and
col_num=colnum;
return res;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return ' ';
END;
/
DECLARE
s1_val varchar2(60); s2_val varchar2(60); s3_val varchar2(60); s4_val varchar2(60); s5_val varchar2(60); s6_val varchar2(60); s7_val varchar2(60);
s10_val varchar2(60); s11_val varchar2(60); s12_val varchar2(60); s13_val varchar2(60); s14_val varchar2(60); s15_val varchar2(60); s16_val varchar2(60); s17_val varchar2(60); s18_val varchar2(60); s19_val varchar2(60); s20_val varchar2(60); s21_val varchar2(60); s22_val varchar2(60); s23_val varchar2(60);
cursor mycursor is select ss.ss_id from ss,sa where sa.ss_tmpl_id =
135686279
and ( ss.ss_id = sa.ss_id) and (ss.row_num = 0 and ss.col_num = 1
and ss.val = 'YES');
cursor mycursor_cur(ssid INT, colnum INT, rownum INT) is select val from ss where ss_id=ssid and row_num=rownum and col_num=colnum;
begin
for ssid_rec in mycursor
LOOP
/* Selecting from B2 for Style Number Primary value */
for ssid_rec2 in mycursor_cur (ssid_rec.ss_id, 1, 1)
loop
s1_val := ssid_rec2.val;
end loop;
/* Selecting from B3 for Style Number Secondary value */
s2_val := get_cell_value(ssid_rec.ss_id, 1, 2);
/* Selecting from F24 for Style Number Color value */
s3_val := get_cell_value(ssid_rec.ss_id, 5, 23);
/* Selecting from B4 for Spec # value */
s4_val := get_cell_value(ssid_rec.ss_id, 1, 4);
/* Selecting from B19 for Print # value */
s5_val := get_cell_value(ssid_rec.ss_id, 1, 18);
/* Selecting from B12 for Season value */
s6_val := get_cell_value(ssid_rec.ss_id, 1, 11);
/* Selecting from B8 for Division value */
s7_val := get_cell_value(ssid_rec.ss_id, 1, 7);
/* Selecting from B5 for Class value */
s8_val := get_cell_value(ssid_rec.ss_id, 1, 4);
/* Selecting from B7 for Type of Product value */
s9_val := get_cell_value(ssid_rec.ss_id, 1, 6);
/* Selecting from D7 for Master Type of Product value */
s10_val := get_cell_value(ssid_rec.ss_id, 3, 6);
/* Selecting from D5 for Master Brand value */
s11_val := get_cell_value(ssid_rec.ss_id, 3, 4);
/* Selecting from B11 for Group value */
s12_val := get_cell_value(ssid_rec.ss_id, 1, 10);
/* Selecting from B20 for Print Type value */
s13_val := get_cell_value(ssid_rec.ss_id, 1, 19);
/* Selecting from B9 for Size Code value */
s14_val := get_cell_value(ssid_rec.ss_id, 1, 8);
/* selecting from B14 for Customer Number value */
s15_val := get_cell_value(ssid_rec.ss_id, 1, 13);
/* Selecting from B24 for Color Ground value */
s16_val := get_cell_value(ssid_rec.ss_id, 1, 23);
/* Selecting from B6 for Design Ref # value */
s17_val := get_cell_value(ssid_rec.ss_id, 1, 5);
/* Selecting from D19 for Print Name value */
s18_val := get_cell_value(ssid_rec.ss_id, 3, 18);
/* Selecting from B22 for Color Pack # value */
s19_val := get_cell_value(ssid_rec.ss_id, 1, 21);
/* Selecting from D2 for Style description value */
s20_val := get_cell_value(ssid_rec.ss_id, 3, 1);
/* Selecting from B15 for Fabric Type values */
s21_val := get_cell_value(ssid_rec.ss_id, 1, 14);
/* Selecting from B16 for Fabric Weight value */
s22_val := get_cell_value(ssid_rec.ss_id, 1, 15);
/* Selecting from D9 for Size Assorated value */
s23_val := get_cell_value(ssid_rec.ss_id, 3, 8);
DBMS_OUTPUT.PUT_LINE
((s1_val,6)||,||(s2_val,6)||,||(s3_val,6)||,||(s4_val,10)||,||(s5_val,10)||, ||(s6_val,2)||,||(s7_val,2)||,||(s8_val,2)||,||(s9_val,4)||,||(s10_val,4)||, ||(s11_val,2)||,||(s12_val,4)||,||(s13_val,10)||,||(s14_val,5)||,||(s15_val,6)||,||(s16_val,30)||,||(s17_val,15)||,||(s18_val,30)||,||(s19_val,2)||,||(s 20_val,30)||,||(s21_val,10)||,||(s22_val,10)||,||(s23_val,10));
end loop;
end;
/
spool off
exit
CUT ------------------------------------------------------------
> Jimmy <c6635500_at_comp.polyu.edu.hk> wrote in message news:3760B418.48B91B14_at_comp.polyu.edu.hk...
> Hello all, > > I want to get the P.K. of a table with the format aaa,bbb,ccc... > (i.e. comma separated with one string). I know how to query the system > dictionary to get the primary key, however, I get the following results: > > column > ------- > aaa > bbb > ccc > > How can I get the result in aaa,bbb,ccc with one row? Is it possible to > that with just one SQL statmenet? > > Thanks > Jimmy >Received on Thu Jun 10 1999 - 21:54:40 CDT
 |
 |