Home » SQL & PL/SQL » SQL & PL/SQL » Converting SQL file to csv file
Converting SQL file to csv file [message #188907] Tue, 22 August 2006 06:17 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have created a sql report file. Its working if run from sql prompt as @c:\order_relationships.sql

But I need to convert the output in csv file. So I need to change the some of the statement.
This is first time I am going to convert report file in csv.
So not really sure as how to convert this. When I run the file its giving the output based on only the last statement
"SELECT anchor_ord||','||
SLCT||','||
wo_nbr||','||
rel_wo_nbr||','||
wostatus||','||
opstatus||','||
loc||','||
Item||','||
qty||','
FROM siviews.si_cancel_workorder
ORDER BY anchor_ord;"

But this is not taking care of the previous formattings.

/******************************************************************************
SQL Script

Filename: order_relationships.sql

Purpose:  Generates Report Data for Order Relationships

Usage:    While in SQLPlus ...

             SQL> @order_relationships.sql &1 &2

             Where &1 = Extract file name used in SPOOL command
                   &2 = Report Identifier
******************************************************************************/
WHENEVER OSERROR EXIT SQL.OSCODE ROLLBACK
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
SET ECHO OFF
SET ESCAPE ON
SET SERVEROUTPUT ON
SET DOCUMENT OFF
SET VERIFY OFF
SET HEADING OFF
SET RECSEP OFF
SET NEWPAGE 0
SET VERIFY OFF
SET CONCAT ON

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
/******************************************************************************
Author: Mona Singh                                                 
Date:   18-Aug-2006

Edit
History:

  18-Aug-2006 Mona Singh Issue #RHEY-6RWLVU
        o Initial version.
******************************************************************************/
DEFINE csvfile =  &1
DEFINE rptid = &2

PROMPT
PROMPT Beginning data extraction.

SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 500
SET TRIMSPOOL ON
SET TERMOUT OFF
COLUMN current_date NEW_VALUE report_date
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') current_date FROM DUAL;
COLUMN current_date NEW_VALUE report_time
SELECT TO_CHAR(SYSDATE,'HH:MI:SS AM') current_date FROM DUAL;

SELECT SYSDATE FROM DUAL;

SET TERMOUT ON

--Setup page headings and footings
TTITLE CENTER 'Order Relationships' RIGHT report_date SKIP 1 RIGHT report_time SKIP 3 'Anchor Order:  '  FORMAT A10 anc_ord_var SKIP 1 FORMAT A10 slct_var SKIP 2

BTITLE LEFT 'No Related WorkOrder' RIGHT 'Page ' FORMAT 999 SQL.PNO

--Format the columns
COLUMN anchor_ord NEW_VALUE anc_ord_var NOPRINT
COLUMN slct NEW_VALUE slct_var NOPRINT
COLUMN wo_nbr           HEADING 'Dependent|Work Order' FORMAT A10 WORD_WRAPPED 
COLUMN rel_wo_nbr       HEADING 'Related|Work Order'   FORMAT A10 WORD_WRAPPED 
COLUMN wostatus         HEADING 'Wostatus'    FORMAT A10 WORD_WRAPPED 
COLUMN opstatus         HEADING 'OpStatus' FORMAT A10 WORD_WRAPPED 
COLUMN loc              HEADING 'Location' FORMAT A10 WORD_WRAPPED 
COLUMN item             HEADING 'Item' FORMAT A10 WORD_WRAPPED 
COLUMN qty              HEADING 'Qty' FORMAT 9999 


BREAK ON anchor_ord SKIP PAGE 1 NODUPLICATES ON slct NODUPLICATES

SET TERMOUT OFF

SPOOL &csvfile

--
--Column Headings
--

--
-- Write out Report Indentifier
--
SELECT '&rptid'||','
  FROM DUAL;

--
-- Column Headings
--
--
-- Detail Information
--
SELECT anchor_ord||','||
       SLCT||','||
       wo_nbr||','||
       rel_wo_nbr||','||
       wostatus||','||
       opstatus||','||
       loc||','||
       Item||','||
       qty||','
  FROM siviews.si_cancel_workorder
 ORDER BY anchor_ord;

SPOOL OFF
SET TERMOUT ON
SET FEEDBACK ON
SET PAGESIZE 0
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF
BTITLE OFF

PROMPT Data extraction complete.
PROMPT


Please advice as what changes I need to do to export the formatting also in the csv file.

Thanks,
Mona
Re: Converting SQL file to csv file [message #189046 is a reply to message #188907] Wed, 23 August 2006 00:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you concatenate all your columns, you can no longer format the individual columns. You have to take care of that yourself.
Word-wrapping is not an issue in csv and to add an appropriate header, select a row with comma-separated header-values from dual:
column ordercolumn noprint
select 'col1,col2,col3'
,      1 as ordercolumn
union  all
select <your selection>
,      2
from   <your tables>
order  by ordercolumn
,      <your order by>

Ordercolumn is used to make sure that the header is the first record shown. The column itself is not printed.

Re: Converting SQL file to csv file [message #189053 is a reply to message #189046] Wed, 23 August 2006 00:53 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Frank, I tried whole day yesterday and came up with the above script using SQL Report. But this is not helping in converting to csv. Now I am going to follow your guidelines.
I have never done the below kind of report through sql query.
I have done for the heading and data values as :
SELECT 'Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,Item,Qty,'     FROM DUAL;
--
-- Detail Information
SELECT anchor_ord||','||
       SLCT||','||
       wo_nbr||','||
       rel_wo_nbr||','||
       wostatus||','||
       opstatus||','||
       loc||','||
       Item||','||
       qty||','
  FROM siviews.si_cancel_workorder
 ORDER BY anchor_ord;

But the other headings and break on Anchor Order is not coming. I also need the date, page number. I have to break the data on Anchor Order,SLCT. Please advice as how can I do the below report using Union with select.
Your advice is very much important as I have to do is fast as I have wasted my time doing the reporting way yesterday. Please advice.
Below is the output which I need.
Thanks Frank,
Soni
                        Order Relationships        8/23/2006
                                                   10:55:45AM
Anchor Order : 1387005
SLCT         : PRIMARY
Dependent Work Order     Work Order Wostatus Opstatus Location  Location  Item  Qty
END                       1387005      122      112     2548      45       30   150
No Related WorkOrder
Page 1 of 712
  

[Updated on: Wed, 23 August 2006 00:55]

Report message to a moderator

Re: Converting SQL file to csv file [message #189076 is a reply to message #189053] Wed, 23 August 2006 02:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, I don't get it.
CSVs are normally used as interface between two systems. Formatting is done by the receiving system, not in the CSV. Pagination should not be done in the csv, as you don't know how the pages will be formatted.
All you should be responsible for is providing the correct data.
Re: Converting SQL file to csv file [message #189090 is a reply to message #189076] Wed, 23 August 2006 03:11 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Frank,
But can I get the records display as below. The records need to be have break on Anchor Order.
Can I use some UNION where there can be 4 select statements each displaying the one line out of the four below and something that can break the data based on Anchor Order and the headings can be repeated again.
If this can be done, my problem will be almost solved. Please give me some hint as how can I do that.

Anchor Order : 1387005
SLCT         : PRIMARY
Dependent Work Order     Work Order Wostatus Opstatus Location  Location  Item  Qty
END                       1387005      122      112     2548      45       30   150


Anchor Order : 1387045
SLCT         : RELATED
Dependent Work Order     Work Order Wostatus Opstatus Location  Location  Item  Qty
END                       1387897      132      117     2549      60       30   170


Thanks,
Mona
Re: Converting SQL file to csv file [message #189109 is a reply to message #189090] Wed, 23 August 2006 04:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is still csv-related, right?
Look at my previous example using the non-printed column as an order-by column. You could indeed use a union-all selecting an id in all four selects in addition to the already shown ordercolumn.
Then order by id, ordercolumn to get the ordering you want.

hth
Re: Converting SQL file to csv file [message #189137 is a reply to message #189109] Wed, 23 August 2006 06:03 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I am sorry to bother you so much. But I didn't really get you as how exactly can I get the above result.
Please see if I am going in the correct direction.

It gives error that in the first line FROM TABLENAME is missing.
column ordercolumn noprint
SELECT 'anchor,SLCT,wo,rel,wostatus', 1 as ordercolumn 
union all
SELECT anchor_ord,
       SLCT,
       wo_nbr,
       rel_wo_nbr,
       wostatus, 2
  FROM siviews.si_cancel_workorder
order by ordercolumn
Re: Converting SQL file to csv file [message #189149 is a reply to message #189137] Wed, 23 August 2006 06:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ah sorry, my mistake.
I should have added a "from dual"

That would have given you one header and the data.
Now to add the "Anchor order: nnnn" thingy and a header per ID it would look something like this (no more selecting from dual):
select 1                                 as ordercolumn
,      your_anchor_order||','||your_slct as datacolumn
,      id
from   your_table
group  by ordercolumn
,      datacolumn
,      id
union  all
select 2                                 as ordercolumn
,      'header1,header2,headerx'         as datacolumn
,      id
from   your_table
group  by ordercolumn
,      datacolumn
,      id
union all
select 3
,      your||','||comma||','||separated||','||data
,      id
from   your_table
order  by 3, 1, 2


This will give you per ID:
1 row containing anchor_order number plus SLCT-value
1 row containing header-titles
n rows containing data regarding that ID.

hth
Re: Converting SQL file to csv file [message #189173 is a reply to message #189149] Wed, 23 August 2006 08:46 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks for your help Frank .
I run the below query
select 1 as ordercolumn
,anchor_ord||','||slct as datacolumn
,      anchor_ord
from   siviews.si_cancel_workorder
group  by ordercolumn
,      datacolumn
,      anchor_ord
union  all
select 2                                as ordercolumn
,      'Opstatus,Location,Item'         as datacolumn
,      anchor_ord
from   siviews.si_cancel_workorder
group  by ordercolumn
,      datacolumn
,      anchor_ord
union all
select 3
,      qty||','||wo_nbr||','||rel_wo_nbr||','||wostatus||','||SLCT
,      anchor_ord
from   siviews.si_cancel_workorder
order  by 3, 1, 2


But it gives error as "datacolumn" is invalid column name.
Other times it gives error that " query block has incorrect number of result columns" .
Re: Converting SQL file to csv file [message #189213 is a reply to message #189173] Wed, 23 August 2006 11:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thought I tested something similar, but it fails at my db at home too.

Use all selected columns in your group by and replace aliases by column-positions:
select 1                     as ordercolumn
,      anchor_ord||','||slct as datacolumn
,      anchor_ord
from   siviews.si_cancel_workorder
group  by 1
,      anchor_ord
,      slct
union  all
select 2
,      'Opstatus,Location,Item'
,      anchor_ord
from   siviews.si_cancel_workorder
group  by 1
,      2
,      anchor_ord
union all
select 3
,      qty||','||wo_nbr||','||rel_wo_nbr||','||wostatus||','||SLCT
,      anchor_ord
from   siviews.si_cancel_workorder
order  by 3, 1, 2


This will only work if you have exactly 1 SLCT per anchor_ord, otherwise you will get more than one record from the first select.
Re: Converting SQL file to csv file [message #189300 is a reply to message #189213] Thu, 24 August 2006 01:51 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Frank,
The following query is close to the result.

select 1 as ordercolumn
 ,      anchor_ord||','||slct as datacolumn
 ,      anchor_ord
 from   siviews.si_cancel_workorder
 group  by 1
 ,      anchor_ord
 ,      slct
 union  all
 select 2
 ,      'Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,Item,Qty'
 ,      anchor_ord
 from   siviews.si_cancel_workorder
 group  by 1
 ,      2
 ,      anchor_ord,slct
 union all
 select 3
 ,      wo_nbr||','||rel_wo_nbr||','||wostatus||','||opstatus||','||loc||','||Item||','||qty
 ,      anchor_ord
 from   siviews.si_cancel_workorder
 order  by 3, 1, 2

But there are two values of SLCT(PRIMARY,RELATED). And the result should come grouped on SLCT too :
Anchor Order :	1410021								
 PRIMARY	
 Dependent Work Order			Work Order	Wostatus	Opstatus	Location	Item	Qty
 1431975	1431976	05		         115	1427832	1,500.00			
 1410021	1411415	90	30	         125	1354298	159.00			
 RELATED
 Dependent Work Order			Work Order	Wostatus	Opstatus	Location	Item	Qty
 1431975	1431943	05		         115	1427832-P	1,500.00			
The result I am getting now is as :
1 1417578	PRIMARY					
1 1417578	RELATED					
2 Dependent Work Order	Related Work Order	Wostatus	OpStatus	Location	Item	Qty
3 1417578	1418898	85	20	115	1428667-P	4667
3 1418898	1417579	85	20	115	1428667-P	4667
3 1418898	1417584	85	20	115	1428667-P	4667

Also I am getting the first column in the csv file as : 1 1417578 or 2 1417578 or 3 1417578.
Please let me know what changes I need to make.
Thanks for all your help,
Mona
Re: Converting SQL file to csv file [message #189312 is a reply to message #189300] Thu, 24 August 2006 02:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
could you please give me a create table statement and some inserts, so that I can replay your scenario?
The values in the columns of your expected results differ from the values in the actual result. Is that right?

to remove the unwanted columns use the "column xxx noprint" as I showed in my first reply
Re: Converting SQL file to csv file [message #189336 is a reply to message #189312] Thu, 24 August 2006 03:37 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Frank,
create table si_cancel_workorder(
anchor_ord varchar2(15)
,slct varchar2(10)
,wo_nbr varchar2(10),rel_wo_nbr varchar2(10),wostatus varchar2(10),
opstatus varchar2(10),loc varchar2(10),Item varchar2(10),qty number(7)
)


insert into si_cancel_workorder values('1417561','PRIMARY','END','1417511','45','30','122','1351566',150);
insert into si_cancel_workorder values('1417578','PRIMARY','1417573','1418823','85','20','115','1428667-P',4667);
insert into si_cancel_workorder values('1417578','RELATED','1418898','1418899','45','30','115','1428667',67);
insert into si_cancel_workorder values('1417578','RELATED','1418898','1417586','85','20','115','1428667-P',135);
insert into si_cancel_workorder values('1417638','PRIMARY','END','1417638','45','30','122','1386377',288);
insert into si_cancel_workorder values('1417647','PRIMARY','1417555','1417347','68','5','122','1408643',295);
insert into si_cancel_workorder values('1417547','PRIMARY','1417556','1417261','45','30','122','1351566',150);
)

Thanks,
Mona
Re: Converting SQL file to csv file [message #189346 is a reply to message #189336] Thu, 24 August 2006 04:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I still think (as I said before) that you should only supply one record per record in your table, and that it would be up to the application processing the csv to split those.

Is this more like it? (Your expected results don't match the provided data)

SQL> column ordercolumn noprint
SQL> column idcolumn    noprint
SQL>
SQL> select 1                     as ordercolumn
  2  ,      anchor_ord||','||slct as datacolumn
  3  ,      anchor_ord||'#'||slct as idcolumn
  4  from   si_cancel_workorder
  5  group  by 1
  6  ,      anchor_ord
  7  ,      slct
  8  union
  9  select 2
 10  ,      'Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,
Item,Qty'
 11  ,      anchor_ord||'#'||slct
 12  from   si_cancel_workorder
 13  group  by 1
 14  ,      2
 15  ,      anchor_ord
 16  ,      slct
 17  union
 18  select 3
 19  ,      wo_nbr||','||rel_wo_nbr||','||wostatus||','||opstatus||','||loc||','
||Item||','||qty
 20  ,      anchor_ord||'#'||slct
 21  from   si_cancel_workorder
 22  order  by 3, 1, 2
 23  /

DATACOLUMN
--------------------------------------------------------------------------------
--------------------------
1417547,PRIMARY
Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,Item,Qty
1417556,1417261,45,30,122,1351566,150
1417561,PRIMARY
Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,Item,Qty
END,1417511,45,30,122,1351566,150
1417578,PRIMARY
Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,Item,Qty
1417573,1418823,85,20,115,1428667-P,4667
1417578,RELATED
Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,Item,Qty
1418898,1417586,85,20,115,1428667-P,135
1418898,1418899,45,30,115,1428667,67
1417638,PRIMARY
Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,Item,Qty
END,1417638,45,30,122,1386377,288
1417647,PRIMARY
Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,Item,Qty
1417555,1417347,68,5,122,1408643,295

19 rows selected.
Re: Converting SQL file to csv file [message #189600 is a reply to message #189346] Fri, 25 August 2006 03:51 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Frank, this is very much close to what I wanted.
I appreciate you for everything you did to make this work out.
ORAFAQ indeed is the best forum where real initiatives are taken.

Thanks again,
Mona
Re: Converting SQL file to csv file [message #200761 is a reply to message #189600] Wed, 01 November 2006 04:20 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Frank,
I am working on a sql file where I need to create a query which will display data along with its total group by one column.

Below is the format of the data :-
ADMIN_GROUP   ITEM   DESCR   LOC   OH   POmonth1   POmonth2   POmonth3   POmonth4
 	      4877  GR138X   175   0      0          0       50000       0
              4877  GR138X   FIN   3600   0          0        0           0
3M - 4103	 	Totals:	   3600   0          0       50000       0	
 	      4968  GR60X60  175   2800   0          0        0          0
 	      4968  GR60X60  FIN   0      0          0        0          0
50" 60SQ	 	Totals:    2800   0          0        0          0

I am writting a sql file to export the data into a csv file.
I have managed the header with a single statement from DUAL table.
The second and third line needs to be in UNION to display the records in grouping with TOTAL.

I need to use the below query to get the above result

SELECT 'ADMIN_GROUP,ITEM,DESCR,LOC,OH,PFtotal1,PFtotal2,PFtotal3,PFtotal4,' FROM DUAL;
--
-- Detail Information
--
SELECT i.item||','||
       i.descr||','||s.loc||','||s.oh||','||
       i.p1||','||i.p2||','||i.p3||','||i.p4,0||','
  FROM stsc.item i, stsc.sku sku, 
       springs.si_skuproj_w w,
       springs.si_skuproj_w w1
 WHERE (i.item=sku.item)
   AND ((sku.item=w.item(+))
 ORDER BY i.admin_group, i.item, s.loc 
UNION 
SELECT i.admin_group||','||''||','||''||','||'Totals:'||','||
       SUM(s.oh)||','||SUM(i.p1)||','||SUM(i.p2)||','||
       SUM(i.p3)||','||SUM(i.p4)||','
  FROM stsc.item i, stsc.sku sku, 
       springs.si_skuproj_w w,
       springs.si_skuproj_w w1
 WHERE (i.item=sku.item)
   AND ((sku.item=w.item(+))
 ORDER BY i.admin_group, i.item, s.loc;   


I am getting correct data from the first query but I am not getting the idea to get the output of the two together in the above format.
Please suggest me a way to get the result on grouping. I can use the BREAK statement to break on admin_group.

Please see the attached sql file.

Thanks,
Mona
Re: Converting SQL file to csv file [message #200775 is a reply to message #200761] Wed, 01 November 2006 05:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It's acutally the same idea as before. Use dummy columns for sorting.
Note: the fact that you create a csv file as output suggests that the receiving party can interpret the data that's in there. Why not provide them with the data and let THEM do the summing etc??
Re: Converting SQL file to csv file [message #200779 is a reply to message #200775] Wed, 01 November 2006 05:16 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Frank,
The recieving party are businessman people and they can't work on that part. So I have to do that part using sql query.
I will try using the sorting.


Thanks,
Mona
Re: Converting SQL file to csv file [message #200989 is a reply to message #200775] Thu, 02 November 2006 02:51 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Frank,
I tried solving this. There are only two rows compared to three rows we did last time.
But this time in the second row, I have SUMmation of the first set of rows along with some text like 'TOTAL' and some columns do not have anything to display.
Below is the format of the data :-

ADMIN_GROUP   ITEM   DESCR   LOC   OH   POmonth1   POmonth2   POmonth3   POmonth4
 	      4877  GR138X   175   0      0          0       50000       0
              4877  GR138X   FIN   3600   0          0        0           0
3M - 4103	 	Totals:	   3600   0          0       50000       0	
 	      4968  GR60X60  175   2800   0          0        0          0
 	      4968  GR60X60  FIN   0      0          0        0          0
50" 60SQ	 	Totals:    2800   0          0        0          0


In the below line :
3M - 4103	 	Totals:	   3600   0          0       50000       0
, There is a gap for three columns and a text 'TOTAL' is there under OH.
Please advice as how to go about this.

Thanks,
Mona
Re: Converting SQL file to csv file [message #201000 is a reply to message #200989] Thu, 02 November 2006 03:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What's your problem? You added the empty columns to create the gap, didn't you? (||','||''||','||''||','||'Totals:')

You should check the outcome in a csv-aware tool, not in sqlplus.
Re: Converting SQL file to csv file [message #201192 is a reply to message #201000] Thu, 02 November 2006 23:37 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Frank,
I am giving you a create table script with insert statements. You can run my query and see the result. I am finding problem in the first 2 columns.

The create statement :
CREATE TABLE CPGGROUP
(
ADMIN_GROUP VARCHAR2(40 BYTE),
ITEM VARCHAR2(50 BYTE),
DESCR VARCHAR2(50 BYTE),
LOC VARCHAR2(10 BYTE),
OH NUMBER ( 8 ),
POMONTH1 NUMBER ( 8 ),
POMONTH2 NUMBER ( 8 ),
POMONTH3 NUMBER ( 8 ),
POMONTH4 NUMBER ( 8 )
)

Insert into cpggroup values('3M - 4103','4877','GR138X','175',0,0,0,50000,0);
Insert into cpggroup values('3M - 4103','4877','GR138X','FIN',0,0,0,0,0);
 Insert into cpggroup values('50-60SQ','4968','GR60X60','175',2800,0,0,0,0);
Insert into cpggroup values('50-60SQ','4968','GR60X60','FIN',0,0,0,0,0);

Below is the query I am running :
 SELECT 1 ordercolumn
    , admin_group,ITEM||','||DESCR||','||LOC||','||      OH||','||POMONTH1||','||POMONTH2||','||POMONTH3||','||POMONTH4
  FROM cpggroup1
  GROUP BY 1,admin_group,ITEM,DESCR,LOC,OH,
        POMONTH1,POMONTH2,POMONTH3,POMONTH4
  UNION
  SELECT 2 ordercolumn,
      admin_group,''||','||''||','||'Totals:'||','||
      SUM(OH)||','||SUM(POMONTH1)||','||SUM(POMONTH2)||','||
      SUM(POMONTH3)||','||SUM(POMONTH4)
  FROM cpggroup1
  GROUP BY admin_group
  order by admin_group,ordercolumn


The output in the first two columns are not coming as required.
The output I require is should be seperated by comma as :
,4877,GR138X,175,0,0,0,50000,0
,4877,GR138X,FIN,0,0,0,0,0
3M - 4103,,,Totals:,0,0,0,50000,0
,4968,GR60X60,175,2800,0,0,0,0
,4968,GR60X60,FIN,0,0,0,0,0
50-60SQ,,,Totals:,2800,0,0,0,0


The csv file for the above should give the below final result :
ADMIN_GROUP   ITEM   DESCR   LOC   OH   POmonth1   POmonth2   POmonth3   POmonth4
 	      4877  GR138X   175   0      0          0       50000       0
              4877  GR138X   FIN   3600   0          0        0           0
3M - 4103	 	Totals:	   3600   0          0       50000       0	
 	      4968  GR60X60  175   2800   0          0        0          0
 	      4968  GR60X60  FIN   0      0          0        0          0
50- 60SQ	 	Totals:    2800   0          0        0          0


The first two columns whose value in first row is "3M - 4103,4877" should come comma seperated but they are not working that way as when I try to use piping between them its not working.
Please suggest me a way to get this result as I have tried but not getting it.

Regards,
Mona

[Updated on: Fri, 03 November 2006 00:13]

Report message to a moderator

Re: Converting SQL file to csv file [message #201196 is a reply to message #201192] Fri, 03 November 2006 00:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
you CAN select admin_group more than once!
You also have to include it in your concatenated string:
SQL> select 1 ordercolumn
  2        ,admin_group
  3        ,','||item || ',' || descr || ',' || loc || ',' || oh || ',' || pomonth1 || ',' ||
  4         pomonth2 || ',' || pomonth3 || ',' || pomonth4
  5  from   cpggroup
  6  group  by 1
  7           ,admin_group
  8           ,item
  9           ,descr
 10           ,loc
 11           ,oh
 12           ,pomonth1
 13           ,pomonth2
 14           ,pomonth3
 15           ,pomonth4
 16  union
 17  select 2 ordercolumn
 18        ,admin_group
 19        ,admin_group|| ',' || ',' || ',' || 'Totals:' || ',' || sum(oh) || ',' ||
 20         sum(pomonth1) || ',' || sum(pomonth2) || ',' || sum(pomonth3) || ',' ||
 21         sum(pomonth4)
 22  from   cpggroup
 23  group  by admin_group
 24  order  by admin_group
 25           ,ordercolumn
 26  /

ORDERCOLUMN ADMIN_GROU ','||ITEM||','||DESCR||','||LO
----------- ---------- ---------------------------------
          1 3M - 4103  ,4877,GR138X,175,0,0,0,50000,0
          1 3M - 4103  ,4877,GR138X,FIN,0,0,0,0,0
          2 3M - 4103  3M - 4103,,,Totals:,0,0,0,50000,0
          1 50-60SQ    ,4968,GR60X60,175,2800,0,0,0,0
          1 50-60SQ    ,4968,GR60X60,FIN,0,0,0,0,0
          2 50-60SQ    50-60SQ,,,Totals:,2800,0,0,0,0


See? You were almost there!!
Re: Converting SQL file to csv file [message #201202 is a reply to message #201196] Fri, 03 November 2006 00:55 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Frank,
I have made one little change in the script. I do not need the first column. Please run the sql file attached.
And you will get a csv file.

Is there a way to get the output without the first column.

Thanks,
Mona
  • Attachment: cpggroup.sql
    (Size: 2.36KB, Downloaded 149 times)
Re: Converting SQL file to csv file [message #201225 is a reply to message #201202] Fri, 03 November 2006 03:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hint: what are these lines for?

--Format the columns
column srno noprint
column idcolumn    noprint
Re: Converting SQL file to csv file [message #201228 is a reply to message #201225] Fri, 03 November 2006 03:48 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

They are not being used anywhere. I forgot to remove them. Earlier I was using them as alias instead of ordercolumn.

Mona
Re: Converting SQL file to csv file [message #201304 is a reply to message #201228] Fri, 03 November 2006 07:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
so, what should you do to not display the ordercolumn?
Re: Converting SQL file to csv file [message #201960 is a reply to message #188907] Tue, 07 November 2006 10:23 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

I have not gone through the post for specific formatting of csv. Are you trying to convert one time csv or make it a procedure for implementation? If you want to make it one time, you can use my application that converts query into csv. Right click on the datagrid or export at the menu that saves as csv.

Jayg
Fun Programming with Oracle & Visual C++

[Updated on: Wed, 08 November 2006 04:07]

Report message to a moderator

Re: Converting SQL file to csv file [message #202052 is a reply to message #201960] Wed, 08 November 2006 00:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Jayg,

I know you don't spam the forums with promotions for your tool (like some did in the past; every question lead to the same 'use this tool' answer), but I don't think this is the right forum to promote your product.
First of all, we have a separate forum for that, and secondly, because one of the main goals of a forum like this newbie-forum is to teach people how to code; not how to point and click.
A lot of new users here can't even use normal sqlplus, but only TOAD or alikes.

No offence meant..
Re: Converting SQL file to csv file [message #202109 is a reply to message #202052] Wed, 08 November 2006 04:04 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

Frank,

Thanks, I understand and I will remove this website, hereafter.

Jayg
Fun Programming with Oracle & Visual C++
Re: Converting SQL file to csv file [message #202163 is a reply to message #202109] Wed, 08 November 2006 07:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hi, I never meant you to take out the link in your sig!
I just meant you explicitly pointed to your product as a possible solution.
Re: Converting SQL file to csv file [message #202416 is a reply to message #202163] Thu, 09 November 2006 12:42 Go to previous message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

Frank,

I understand that we need to share our experience and suggest the possible workaround ..etc. Somehow, I am unable to edit the old post. This forum allows the last posted entry and not the previous ones. If I get a chance, I will remove my comments as well as maintain the same in future.

I appreciate your feedback and suggestion.

Jayg
Jayg - Fun Programming with Oracle & Visual C++, Author of lightsql

[Updated on: Thu, 09 November 2006 12:43]

Report message to a moderator

Previous Topic: rolling standard deviation
Next Topic: Error ORA-06512
Goto Forum:
  


Current Time: Sat Dec 03 16:00:51 CST 2016

Total time taken to generate the page: 0.08621 seconds