No idea if this is possible in Oracle?! 1

[ftpdoo]

I have just ran some SQL which generates the following results:

Requested Action Code,17,17
Requested Action Code,18,18
Requested Action Code,19,19
Requested Action Code,20,20
Requested Action Code,21,21
Meter Period Number,01,01
Meter Period Number,02,02
Meter Period Number,03,03
Meter Period Number,04,04
Meter Period Number,05,05
Meter Period Number,06,06
Actual/Estimated Indicator,A,A
Actual/Estimated Indicator,C,C
Actual/Estimated Indicator,E,E

Every time the first column changes i want to create a new CSV file containing only the columns 2 and 3. Then naming the new CSV file the value in the first column.

ie: Requested Action Code.csv will contain:

17,17
18,18
19,19
20,20
21,21

Meter Period Number.csv wil contain:

01,01
02,02
03,03
04,04
05,05
06,06

Any help would be appricated, Is this possible?

My original SQL was: (if that helps any?)

select b.item_name||','||+valid_set_value||','||+valid_set_value
from dtc_8_1.valid_set_value a, dtc_8_1.data_item b
WHERE a.item_counter = b.item_counter

I have no idea if this is even possible in Oracle? :O)

 

[sem]

Yes, it's possible but not too simple. You may select distinct item_name values and then generate file in a loop to produce a file for each value. To do it you should run "command" file that creates a script on a fly:

cmd.sql

 set feedback off
 set verify off
 set termout off
 set pages 0

 spool all.sql
 select '@@ind.sql '||item_name from (
 select distinct b.item_name
from dtc_8_1.valid_set_value a, dtc_8_1.data_item b
WHERE a.item_counter = b.item_counter);
 spool off
 @@all.sql

ind.sql

spool &&1.csv
select b.item_name||','||+valid_set_value||','||+valid_set_value
from dtc_8_1.valid_set_value a, dtc_8_1.data_item b
WHERE a.item_counter = b.item_counter and b.item_name='&&1';
spool off

Regards, Dima

http://seminihin.narod.ru

 

[ftpdoo]

Thanks Dima,

That is brilliant! Im using Toad to execute the SQL (not sure if your fimilar with it)

So I execute both pieces of SQL in a seperate SQL windows but a window pops up asking me to enter a value when i run the second one?? Any idea what i've done wrong?

Thanks again..

 

[sem]

These scripts were for sql*plus and the idea was to create both scripts but then run only cmd.sql . It in turn creates dynamically and launches all.sql that calls ind.sql multiple times with correct parameters. It makes no sence to call ind.sql manually.

I don't know whether TOAD is capable to process sql*plus native commands like spool and @@.



Regards, Dima

http://seminihin.narod.ru