Home » SQL & PL/SQL » SQL & PL/SQL » SQL CSV Spool with UNION ALL
SQL CSV Spool with UNION ALL [message #417129] Thu, 06 August 2009 04:30 Go to next message
dior
Messages: 25
Registered: April 2009
Junior Member
Hi,

I m spooling some things to a csv file and that works very well, but now i need to spool a file where i have joined 3 tables with union all.

any idea how i can do this?


so far I use a sql file like this:

@echo off 
xxx/yyy@DB.com
set pagesize 0
set heading off
set linesize 1024
set echo off 
set feed off
set tab off
set trimout on
set trims on
spool values.csv

SELECT 'Value1;value2' from dual;

SELECT
PL1.VALUE || ';' ||                     
PL2.VALUE || ';'          
FROM STAT_123 ST
PL_VC    PL1    ,
PL_VC    PL2    
WHERE  ST.CLASS        =    PL1.ID (+)
AND    ST.LEVEL        =    PL2.ID (+);

spool off  
quit  


The SQL Statement I need to put into such a SQL is:

select STT.VALUE, ST.FACN from ESTAT ST, ESTATTYP STT
where ST.FTYPE = STT.VID
union all
select NG1T.VALUE, NG1.FACN from TRAGWGTYP NG1T, TRAGWG NG1
where NG1.STYPE = NG1T.VID
union all
select PT.VALUE, P.FACN from mas, MASTYP PT
where P.PTYPE = PT.VID
Re: SQL CSV Spool with UNION ALL [message #417132 is a reply to message #417129] Thu, 06 August 2009 04:43 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
What have you tried and what errors did you get when you tried it.
Re: SQL CSV Spool with UNION ALL [message #417137 is a reply to message #417132] Thu, 06 August 2009 04:51 Go to previous messageGo to next message
dior
Messages: 25
Registered: April 2009
Junior Member
well, I've tried at first put the whole statement without the || ';' || into the file, but the problem is, the two resultw where put into one column without a seperation char. (so if i open it in excel, al the data where put into one column)

I dont know how to adjust the file that i can write the x values of all 3 tables into one column and the y values into an own column, ...
(I hope I made clear what I need with my little knowledge of the english language Smile
Re: SQL CSV Spool with UNION ALL [message #417140 is a reply to message #417137] Thu, 06 August 2009 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I hope I made clear what I need with my little knowledge of the english language

I think you are unclear but this has nothing to do with english.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: SQL CSV Spool with UNION ALL [message #417142 is a reply to message #417137] Thu, 06 August 2009 04:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does COLSEP(arator) does the job?
SQL> set colsep ';'
SQL> select ename, job from emp
  2  union all
  3  select dname, loc from dept;

ENAME         ;JOB
--------------;-------------
SMITH         ;CLERK
ALLEN         ;SALESMAN
WARD          ;SALESMAN
JONES         ;MANAGER
MARTIN        ;SALESMAN
BLAKE         ;MANAGER
CLARK         ;MANAGER
SCOTT         ;ANALYST
KING          ;PRESIDENT
TURNER        ;SALESMAN
ADAMS         ;CLERK
JAMES         ;CLERK
FORD          ;ANALYST
MILLER        ;CLERK
ACCOUNTING    ;NEW YORK
RESEARCH      ;DALLAS
SALES         ;CHICAGO
OPERATIONS    ;BOSTON

18 rows selected.

SQL>
Re: SQL CSV Spool with UNION ALL [message #417146 is a reply to message #417142] Thu, 06 August 2009 05:05 Go to previous messageGo to next message
dior
Messages: 25
Registered: April 2009
Junior Member
NICE, the colsep ';' do the job!

TY very very much!! Smile

only need to
set colsep ';'

and then i can use the whole sql statement with union all.

with this i do not need to adapt the whole sql statement with || ';' || for every value!
Re: SQL CSV Spool with UNION ALL [message #417162 is a reply to message #417129] Thu, 06 August 2009 05:56 Go to previous messageGo to next message
dior
Messages: 25
Registered: April 2009
Junior Member
One more question!

are there any good webpages where all these syntax like colspan(';') listet?

ty
Re: SQL CSV Spool with UNION ALL [message #417164 is a reply to message #417162] Thu, 06 August 2009 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you mean "colsep", yes, SQL*PlusĀ® User's Guide and Reference:
Chapter 6 Formatting SQL*Plus Reports
Chapter 12 SQL*Plus Command Reference
Section SET System Variable Summary

Regards
Michel
Re: SQL CSV Spool with UNION ALL [message #417354 is a reply to message #417162] Fri, 07 August 2009 10:14 Go to previous messageGo to next message
kumar29
Messages: 6
Registered: July 2009
Junior Member
Lets try this way. Don't prepare your whole result as a single string with separator ";"
Use ',' instead of ";"

set pagesize 0
set heading off
set linesize 1024
set echo off
set feed off
set tab off
set trimout on
set trims on
set colsep ","

spool c:\xxxx01.csv;

select STT.VALUE, ST.FACN from ESTAT ST, ESTATTYP STT
where ST.FTYPE = STT.VID
union all
select NG1T.VALUE, NG1.FACN from TRAGWGTYP NG1T, TRAGWG NG1
where NG1.STYPE = NG1T.VID
union all
select PT.VALUE, P.FACN from mas, MASTYP PT
where P.PTYPE = PT.VID
/
spool off
/
Re: SQL CSV Spool with UNION ALL [message #417358 is a reply to message #417354] Fri, 07 August 2009 10:20 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As far a I can see this does not add anything to the previous answers and it is NOT formated.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: Please validate the update
Next Topic: search string from csv file in blob column
Goto Forum:
  


Current Time: Fri Dec 09 15:30:25 CST 2016

Total time taken to generate the page: 0.18523 seconds