Home » SQL & PL/SQL » SQL & PL/SQL » Excel report not formatting correctly in SQL*Plus (multi-merge)
Excel report not formatting correctly in SQL*Plus (multi-merge) [message #234587] Wed, 02 May 2007 10:40 Go to next message
NIckman
Messages: 64
Registered: May 2007
Member
I am using sql plus in UNIX enviroment.

spool test.lst


I need to print date and time on this report which is in excel sheet

I used like this in my script

set linesize 300
column name foramt a35

select TO_CHAR(SysDate,'DD/Mon/YYYY HH:MI P.M.') from dual;

Select first_name "name",
DECODE(STATUS,
'1300','PREPARING',
'1510','START READY',
'1520','TRANSFER READY',
'1561','STOPPED - AUTOMATIC PROCESSING WAS STOPPED SYSTEM',
'UNDEFINED')" JB STATUS",

to_char(new_time(table1_TimeStamp2,'gmt','edt'),'MM/DD/YY HH24:MI:SS')"STARTTIME",

to_char(new_time(table1_TimeStamp4,'gmt','edt'),'MM/DD/YY HH24:MI:SS')"ENDTTIME"

from table1,table2

where table1_idnr = table2_idnr

and
table1_TimeStamp2 >= TO_DATE('2007-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and
table1_TimeStamp4 <= TO_DATE('2007-01-03 23:59:59','YYYY-MM-DD HH24:MI:SS')

and tabel2_Name like '%JOH%'
order by STARTTIME;
spool off




mailx -S 'report' email.com < report.xls

i ran the script.when i opended

when i opened the excel sheet
i got the error.
invalied identifier DUAL

MY question is

1) can i use multitple sql statements in SQL Plus under unix environment.

what is wrong in sql stmt.
select TO_CHAR(SysDate,'DD/Mon/YYYY HH:MI P.M.') from dual;
is there any way to get date and time in the report.
pl help

thx
N.

Re: How to get date and time in SQL plus [message #234592 is a reply to message #234587] Wed, 02 May 2007 10:50 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
You used wrong date format.
you have to concat "p.m" with date format

SQL> ed
Wrote file afiedt.buf

  1  select to_char(sysdate,'dd/mon/yyyy hh:mi:ss')||' '|| 'P.M' DateTime
  2* from dual
  3  /

DATETIME
------------------------
02/may/2007 07:44:26 P.M





Regards
Taj
Re: How to get date and time in SQL plus [message #234610 is a reply to message #234587] Wed, 02 May 2007 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what is wrong in sql stmt.

I don't know, is this what you want:
SQL> select TO_CHAR(SysDate,'DD/Mon/YYYY HH:MI P.M.') from dual;
TO_CHAR(SYSDATE,'DD/MO
----------------------
02/Mai  /2007 06:41 PM

1 row selected.

Regards
Michel
SQL PLUS IN UNIX ENV. EXCEL REPORT NOT FORMATTED CORRECTLY [message #234683 is a reply to message #234587] Wed, 02 May 2007 19:40 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
I am runnin sql plus in unix environemnt.
I am running sql query sending report in excel through mailx command.
when i opened report all the columns not formatted correctly.
pl help me to fix the problem. i am giving down bellow.

this is database table definition

1st col is name is varchar 50
2nd col STATUS is NUM 50(i used deocde statement here) pl see sql bellow
3rd col starttime is date
4th col endtime is date

SET LINESIZE 300
SET PAGESIZE 50
SET FEEDBACK OFF

COLUMN NAME HEADING 'NAME' FORMAT A35
COLUMN STATUS COL 40 HEADING ' STATUS ' FORMAT A30
COLUMN STARTTIME COL 72 HEADING ' START TIME ' FORMAT A30
COLUMN ENDTTIME COL 103 HEADING 'END TIME' FORMAT A35
COLUMN CODE COL200 HEADING 'CODE' FORAMT 999

This is my query

Select first_name "name",
DECODE(STATUS,
'9999','BACKWARD',
'8888','FORWARD',
'7777','TRANSFERING READY',
'6666','STOPPED - NOTWORKING STOPPED SYSTEM',
'UNDEFINED')"STATUS",

to_char(new_time(table1_TimeStamp2,'gmt','edt'),'MM/DD/YY HH24:MI:SS')"STARTTIME",

to_char(new_time(table1_TimeStamp4,'gmt','edt'),'MM/DD/YY HH24:MI:SS')"ENDTTIME"

from table1,table2

where table1_idnr = table2_idnr

and
table1_TimeStamp2 >= TO_DATE('2007-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and
table1_TimeStamp4 <= TO_DATE('2007-01-03 20:59:59','YYYY-MM-DD HH24:MI:SS')

and tabel2_Name like '%JOH%'
order by STARTTIME;
spool off

say for example i got 2 rows from sql query
1st row person name JOHNATHAN is no of chars are 9
2nd row person name JOHN is no of chars are 4


currently i am getting report like this in excel
see 2nd col and 3rd col it is moving away because of name is long
NAME           STATUS                STARTTIME 


JOHN           SYSTEMNOTWORKED       2007-01-01-1.10.10


JOHNATHAN            SYSTEMNOTWORKED         2007-01-01-1.10.10


KRIM           SYSTEMNOTWORKED        2007-01-01-1.10.10




i want report like this in excel 



NAME              STATUS                 STARTTIME 


JOHN              SYSTEMNOTWORKED        2007-01-01-1.10.10


JOHNATHAN         SYSTEMNOTWORKED        2007-01-01-1.10.10


KRIM              SYSTEMNOTWORKED        2007-01-01-1.10.10




i tried left justified on status col, ltrim,lpad,rpad
i am not getting report correctly.
pl tell sql commadns or correct the sql query to format the report the way i want which is shown in the example abobe.


thanks in advance.

N.
Re: How to get date and time in SQL plus [message #234689 is a reply to message #234592] Wed, 02 May 2007 21:49 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
This is I am using in report which is excel.

I am getting the result.

TODAYIS
------------------------
02/may/2007 10:34:51 P.M

down bellow I am getting my sql queey result on excel.

I want in report date and time like this

When open in excel

daily report
02/may/2007 10:34:51 P.M

i do not want see col name

Thx

N.




Excel report not formatted correctly from SQL*Plus environment (merged) [message #235440 is a reply to message #234587] Sat, 05 May 2007 17:54 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
I created sql report using csv file extension .
Send this file in excel through unix mailx command.
I am having 4 sql commands.
I am getting correctly except heading is not coming on the report.
this is my script.pl tell me where is wrong.

SET LINESIZE 300
SET FEEDBACK OFF

col name format a35
col status format a35
SPOOL /test4/out/report.csv

TTITLE LEFT 'DAILY REPORT' CENTER 'PAGE:' SQL.PNO SKIP1 -
SET PAGESIZE 0

select count(distinct name ) || 'total name count' fron dual

select 'Object name,Object type,Timestamp' from dual;

select name|| satus|| starttime
from tab1,tab2
where tab1nr= tab2nr
and status > 20
orderby name;

spool off
exit
i have 3 questions.
1) why heading is not comimng. I think it is beacuse of set pagesize 0. I am using pagesize 0, i am getting two heading from above selct queries. So, I used set pagesize 0.
How can i hget heading at top of the report
and date. I tried the put set pagesize 0 above report heading and bellow the heading.but still heading is not coming.
2) I am getting last sql query result printing twice even though i had only one time.


3) When i send this report in excel as a attachment using mailx command, i go to outlook iopen the attchment of th ereport. as soon as i opend the excel report,
all the columns are merged. i have to manually drag each column to the actual length of column.
and also date column showing as ###### when i opend.
but i when i drag the column it is showing correctly .


pl help why excel report is coming like that
i am automate this report.column of the report should come
autofit width. is the problem in excel settings or
sql plus column setting
i used col format commands correctly
col name format a35
col status format a30

pl help me,

thx

N.








why it is it is running twice.

How to get heading.

Pl hlpe me
thx

S.

Re: SQL plus Report heading and date not getting [message #235449 is a reply to message #235440] Sun, 06 May 2007 03:32 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First of all, so-called "script" you provided is NOT the one you really use. Instead of typing it here, use copy-paste technique so that we could see what you are doing.

SET PAGESIZE 0 will suppress all headings, page breaks, titles and other formatting information. I must admit that I didn't quite understand why you chose to set pagesize to 0 ... could you explain it once again? Whatever it is, if you want to get your headings back, get rid of this command.

The same query gets executed twice only if you tell it to do so. Usually, it is a slash after the query:
select name|| satus|| starttime 
from tab1,tab2 
where tab1nr= tab2nr
and status > 20 
orderby name;
/         --> so, do you have it in your real script?

By the way, where's the purpose in concatenating all those columns? It will be unreadable both in SQL*Plus and MS Excel.
Re: SQL plus Report heading and date not getting [message #235490 is a reply to message #235449] Sun, 06 May 2007 20:02 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
the reason i am using set pagesize 0 is to supress concation sql qury heading query.
i am ing 2 sql query.
I need first query heading only.
i am running sql query. i need to send the report into excel.
When i send the report query results into excel are not formatting corectly.

hre i am getting result.
currently i am getting report like this in excel
see 2nd col and 3rd col it is moving away because of name is long
NAME           STATUS                STARTTIME 


JOHN           SYSTEMNOTWORKED       2007-01-01-1.10.10


JOHNATHAN            SYSTEMNOTWORKED         2007-01-01-1.10.10


KRIM           SYSTEMNOTWORKED        2007-01-01-1.10.10




i want report like this in excel 



NAME              STATUS                 STARTTIME 


JOHN              SYSTEMNOTWORKED        2007-01-01-1.10.10


JOHNATHAN         SYSTEMNOTWORKED        2007-01-01-1.10.10


KRIM              SYSTEMNOTWORKED        2007-01-01-1.10.10


could you pl tell me why how to foramt problem when i send sql select result

here is my code
spool /test/report.lst
set linesize 300
set pagesize 0
set feedback off
col name foramt a35
col status foramt a30
col starttime format a30
col endtime foramt a30
col code foramat 999

select name,
decode(status,
'1100','forward'
'1200','backward'
'9999','none'),
startttime,
endtime,
code,
from tax_tab a, income_tab b

where a.tax_tab.nbr = b.income_tab.nbr
and a.starttime > 0001
and a.endtime < 2359
and b.code > 4
orderby a.starttime;
spool off
exit


if i use above code my report in excel is getting like above example
it is NOT foramtting correctly
so that's why i used report.csv extention to format. even though it is not working. hwen i opend all the columsn are merged
date column showing ######. but i drag the col the it is showing correctly . can you tell me with out using csv extrention
why i am report getting like above example.
i tried ltrim,rpad.No luck..
Thanks,NIckman

Appreciate your reply,. pl help me.


pl see example above

[Updated on: Sun, 06 May 2007 20:11]

Report message to a moderator

Re: SQL plus Report heading and date not getting [message #235626 is a reply to message #235490] Mon, 07 May 2007 08:12 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
http://www.orafaq.com/forum/t/59964/66800/
Re: SQL plus Report heading and date not getting [message #235754 is a reply to message #235490] Mon, 07 May 2007 19:36 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Thanks for reply. I am new to forum and oracel.
I am sending as you said.

The reason i am using set pagesize 0 is to supress concation sql qury heading.
i am using 2 sql query.
I need first query heading only.i do not want 2nd query heading.
i need to send the report into excel. Uisng unix mailx command it will be e-mailed automatically to groups.When i send the report query results into excel are not formatting corectly.
As soon as I opend the excel report,here i am getting result.
like this. Seel First Example.
see 2nd col and 3rd col it is moving away because of name is long




First example 
NAME           STATUS                STARTTIME 


JOHN           SYSTEMNOTWORKED       2007-01-01-1.10.10


JOHNATHAN            SYSTEMNOTWORKED         2007-01-01-1.10.10


KRIM           SYSTEMNOTWORKED        2007-01-01-1.10.10




i want report should be like this in excel 

2nd example 

NAME              STATUS                 STARTTIME 


JOHN              SYSTEMNOTWORKED        2007-01-01-1.10.10


JOHNATHAN         SYSTEMNOTWORKED        2007-01-01-1.10.10


KRIM              SYSTEMNOTWORKED        2007-01-01-1.10.10


could you pl tell me why i am not getting fomatt correctly in exce[ALIGN=left][/ALIGN]l report. 

here is my code


spool /test/report.lst
set linesize 300
set pagesize 0
set feedback off
col name foramt a35
col status foramt a30
col starttime format a30
col endtime foramt a30 
col code foramat 999

select name,
decode(status,
'1100','forward'
'1200','backward'
'9999','none'),
startttime,
endtime,
code,
from tax_tab a, income_tab b 

where a.tax_tab.nbr = b.income_tab.nbr
and a.starttime > 0001
and a.endtime < 2359
and b.code > 4
orderby a.starttime;
spool off
exit




if i use above code my report in excel is getting like above first example, it is NOT foramtting correctly.
I tried with "csv" file type extenstion instead off "lst" to format report automatically in excel.
I am getting another problem.the problem is for when i opend
excel, all the columsn are merged. starttime and endtime columns are showing like this symbol ###### in excel cell.So,
i have to drag manully each cell
depends up on the column length, Then ##### it automatically
change to date. but, why i have to this manually using column
boundaries. I am doing automation process. No manual
intervention should not be there. I need report should be 2nd
example format.why i am noy report getting.

i tried with ltrim,rpad,lpad. No luck..
pl tell me problem.
Thanks,NIckman
Appreciate your reply,. pl help me.

pl see report example above

[Updated on: Tue, 08 May 2007 16:32]

Report message to a moderator

Re: SQL plus Report heading and date not getting [message #235768 is a reply to message #235754] Mon, 07 May 2007 23:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the "Preview Message" before posting.
I can't read lines of 400 characters.

Regards
Michel
excel report not formatted correctly from sql plus environment [message #236369 is a reply to message #235440] Wed, 09 May 2007 14:47 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
I am runnin sql plus in unix environemnt.
I am running sql query sending report in excel through mailx command.
when i opened report all the columns not formatted correctly.
pl help me to fix the problem. i am giving down bellow.

this is database table definition

1st col is name is varchar 50
2nd col STATUS is NUM 50(i used deocde statement here) pl see sql bellow
3rd col starttime is date
4th col endtime is date

SET LINESIZE 300
SET PAGESIZE 50
SET FEEDBACK OFF

COLUMN NAME HEADING 'NAME' FORMAT A35
COLUMN STATUS COL 40 HEADING ' STATUS ' FORMAT A30
COLUMN STARTTIME COL 72 HEADING ' START TIME ' FORMAT A30
COLUMN ENDTTIME COL 103 HEADING 'END TIME' FORMAT A35
COLUMN CODE COL200 HEADING 'CODE' FORAMT 999

This is my query

Select first_name "name",
DECODE(STATUS,
'9999','BACKWARD',
'8888','FORWARD',
'7777','TRANSFERING READY',
'6666','STOPPED - NOTWORKING STOPPED SYSTEM', 
'UNDEFINED')"STATUS",

to_char(new_time(table1_TimeStamp2,'gmt','edt'),'MM/DD/YY HH24:MI:SS')"STARTTIME",

to_char(new_time(table1_TimeStamp4,'gmt','edt'),'MM/DD/YY HH24:MI:SS')"ENDTTIME"

from table1,table2

where table1_idnr = table2_idnr

and 
table1_TimeStamp2 >= TO_DATE('2007-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and 
table1_TimeStamp4 <= TO_DATE('2007-01-03 20:59:59','YYYY-MM-DD HH24:MI:SS')

and tabel2_Name like '%JOH%'
order by STARTTIME;
spool off

say for example i got 2 rows from sql query
1st row person name JOHNATHAN is no of chars are 9
2nd row person name JOHN is no of chars are 4


currently i am getting report like this in excel 
see 2nd col and 3rd col it is moving away because of name is long

NAME           STATUS                STARTTIME 


JOHN           SYSTEMNOTWORKED       2007-01-01-1.10.10


JOHNATHAN            SYSTEMNOTWORKED         2007-01-01-1.10.10


KRIM           SYSTEMNOTWORKED        2007-01-01-1.10.10




i want report like this in excel 



NAME              STATUS                 STARTTIME 


JOHN              SYSTEMNOTWORKED        2007-01-01-1.10.10


JOHNATHAN         SYSTEMNOTWORKED        2007-01-01-1.10.10


KRIM              SYSTEMNOTWORKED        2007-01-01-1.10.10


i tried left justified on status col, ltrim,lpad,rpad
i am not getting report correctly.
pl tell sql commadns or correct the sql query to format the report the way i want which is shown in the example abobe.


thanks in advance.

N.
Re: SQL plus Report heading and date not getting [message #236375 is a reply to message #235768] Wed, 09 May 2007 15:03 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, do not open a new topic for the old problem. Continue discussion in the existing one.

[Updated on: Wed, 09 May 2007 15:05]

Report message to a moderator

Re: excel report not formatted correctly from sql plus environment [message #236376 is a reply to message #236369] Wed, 09 May 2007 15:06 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
If it's formatted properly in sqlplus, then your problem is with excel. excel doesn't just format your output nicely unless you make it a comma-separated file.
Re: excel report not formatted correctly from sql plus environment [message #236383 is a reply to message #236376] Wed, 09 May 2007 15:23 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Joy,
Thanks for your reply.
I already tried with that comma separated query.
still i ahve problem.pl help me. I am desparate.Thanks,N.
pl see sql query bellow.


Select first_name ||','||
DECODE(STATUS,
'9999','BACKWARD',
'8888','FORWARD',
'7777','TRANSFERING READY',
'6666','STOPPED - NOTWORKING STOPPED SYSTEM', 
'UNDEFINED')||','||

to_char(new_time(table1_TimeStamp2,'gmt','edt'),'MM/DD/YY HH24:MI:SS')||','||

to_char(new_time(table1_TimeStamp4,'gmt','edt'),'MM/DD/YY HH24:MI:SS')

from table1,table2

where table1_idnr = table2_idnr

and 
table1_TimeStamp2 >= TO_DATE('2007-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and 
table1_TimeStamp4 <= TO_DATE('2007-01-03 20:59:59','YYYY-MM-DD HH24:MI:SS')

and tabel2_Name like '%JOH%'
order by STARTTIME;
spool off
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #236387 is a reply to message #234587] Wed, 09 May 2007 15:33 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
What is wrong with your last query? What is happening to cause it to fail? A csv file MUST have comma's separating each field. If a particular field contains a comma then it needs to be surrounded by double quotes. See below for correct layout.



123,01-jan-2007 17:23,"smith, john"
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #236644 is a reply to message #236387] Thu, 10 May 2007 08:32 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Well, as mentioned before, you need commas between each column value so that Excel will format it properly. You also need to spool it to a .csv file or a .txt file (which will ask you for the separator column when you open it).

You can also format your columns using the following...

column <col_name> format Axx

where xx is the number of characters in the column.
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #236741 is a reply to message #236387] Thu, 10 May 2007 15:58 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Bill,
Query NOT Failed. The query ran ok.
when i open the excel file(csv extention)
Pl see the excel file attched. All the cells from report are merged.I need to manually drag the each cell to see whole column.when you open excel spread sheet, date is showing like this ###### symbols.when i drag the date column, it automatically change to actual date. I want to where is mistake. why it is happending like that.
why it is not whole column length.

if i manually drag the columns it looks good.
I am automating this report.
I put this script in scheduler. it runs query and e-mail the report. No manual intervention sholud be there.

Thx,
N.
  • Attachment: Report.csv
    (Size: 0.52KB, Downloaded 457 times)
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #236743 is a reply to message #234587] Thu, 10 May 2007 16:05 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
Now I understand your problem. This is not an error, excel has a default column width. When it loads a csv, it doesn't adjust the column width. the "######" on the date column is simply excel telling you that it can't display the date in the width given. The date is in the column, but the column is not wide enough to display it and you have to expand the column manually to see it. The same goes for all the other columns. One way to get around this is to write a file that contains formatting commands. check out the following link.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:769425837805
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #236744 is a reply to message #236743] Thu, 10 May 2007 16:39 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Bill,
Thanks for your quick reply.
I looked the link. I did not understand any thing.
could you please be specific from where to where i need to add the code. what changes i need to make for that code.
I new to oracle and sql plus.

I tried with this.
COLUMN JOB FORMAT A50
COLUMN STATUS FORMAT A50
COLUMN STARTTIME FORMAT A50
COLUMN ENDTTIME FORMAT A50
COLUMN RETCODE FORMAT A50
why not each column length should be 50.
that means you are saying problem is in excel.
after change the fomat a50, still the problem not fixed.

***************************
another way i did this. pl see.

I change the file type from csv to lst
send the file into excel with xls extention.
I am getting the sample report like this.
pl see bellow with examples. I tried with sql query with lapd,rpad, ltrim(on 2nd column). My problem is not solved.
why 2nd col is moving away.
can you tell me where is wrong.
I appreciate your help. i am desparate.
THX

N.



Select first_name "name",
DECODE(STATUS,
'9999','BACKWARD',
'8888','FORWARD',
'7777','TRANSFERING READY',
'6666','STOPPED - NOTWORKING STOPPED SYSTEM', 
'UNDEFINED')"STATUS",

to_char(new_time(table1_TimeStamp2,'gmt','edt'),'MM/DD/YY HH24:MI:SS')"STARTTIME",

to_char(new_time(table1_TimeStamp4,'gmt','edt'),'MM/DD/YY HH24:MI:SS')"ENDTTIME"

from table1,table2

where table1_idnr = table2_idnr

and 
table1_TimeStamp2 >= TO_DATE('2007-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and 
table1_TimeStamp4 <= TO_DATE('2007-01-03 20:59:59','YYYY-MM-DD HH24:MI:SS')

and tabel2_Name like '%JOH%'
order by STARTTIME;
spool off

say for example i got 2 rows from sql query
1st row person name JOHNATHAN is no of chars are 9
2nd row person name JOHN is no of chars are 4


currently i am getting report like this in excel 
see 2nd col and 3rd col it is moving away because of name is long

NAME           STATUS                STARTTIME 


JOHN           SYSTEMNOTWORKED       2007-01-01-1.10.10


JOHNATHAN            SYSTEMNOTWORKED         2007-01-01-1.10.10


KRIM           SYSTEMNOTWORKED        2007-01-01-1.10.10




i want report like this in excel 



NAME              STATUS                 STARTTIME 


JOHN              SYSTEMNOTWORKED        2007-01-01-1.10.10


JOHNATHAN         SYSTEMNOTWORKED        2007-01-01-1.10.10


KRIM              SYSTEMNOTWORKED        2007-01-01-1.10.10
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #236965 is a reply to message #234587] Fri, 11 May 2007 07:51 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
It doesn't matter how wide the columns are in your csv, excel will ALWAYS use the default column width's. The alternative solution I gave you is a stored procedure to generate an SLK file instead of a csv. An SLK file is a non binary spreadsheet file that Excel understands and can contain things line bolding, column widths, fonts, font size, and other formatting commands. It is invoked by running the actual select through the procedure call inside a pl/sql block. After generating your SLK file, you can with a simple command in excel (save as), convert it to an xls file that will all formatting retained. SLK is completely understood by microsoft products. See the following link.

http://netghost.narod.ru/gff/graphics/summary/micsylk.htm

I took the open source base package and enhanced it to handle date columns better. The base package treated dates as a string, I changed mine to return an actual date column. If you would like a copy of the usage documentation and the package, let me know and I will post it in this forum.

[Updated on: Fri, 11 May 2007 08:39]

Report message to a moderator

Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #236979 is a reply to message #236965] Fri, 11 May 2007 08:39 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Bill,
Thanks for your reply.
Now I understand about csv file. Could you pl tell me
i did another way.I posted in my previous post.
I am not using csv file type. pl see examples in my previous post. why my columns are moving away if the name col is long.
But i can see all the columns in excel if use xls extention type file. pl tell me to modify the sql query to foramt correctly using ltrim or lapd with spaces.
thx
N.
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #236985 is a reply to message #234587] Fri, 11 May 2007 08:48 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
Your problem is that you are loading everything into the first column. Excel will show the entire column if there are no additional columns used on the right. Your new query is still not loading your data into individual cells in the spreadsheet. To do that you have three options.

1) make a csv file that will auto import into the sheet.

2) Make a file with each column starting in the same column (fixed length) and use a prn extension.

3) Make a file with an extension that excel doesn't know. This will force it to use the import wizard, which gives you more control.

4) Export in XML which the newer versions of excel can understand and load.

5) Generate an SLK file.
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #237042 is a reply to message #236985] Fri, 11 May 2007 12:55 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Bill,
Excel is loading into correct cells on every column.
the problem is if my first column length varies 2nd col drifts away. I want all the columns are should start same position No matter what 1st col length. i am attaching excel report.
Pl see first table.( currently i am getting like this)
I tried used ltrim on 2nd col. still not working.

and also see what i want. Pl see 2nd table.

I did not understand what you mean by this. pl provide with example.Make a file with each column starting in the same column (fixed length) and use a prn extension.

thx
n.
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #237044 is a reply to message #237042] Fri, 11 May 2007 13:03 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
I like many others will not open attachments.
Cut and paste your output BEFORE you open it in Excel. I don't think you understand. The data needs to be comma separated for Excel to open it properly. sure, it can open a plain text file, but it will all be in a single column.
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #237077 is a reply to message #237044] Fri, 11 May 2007 15:45 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Joy,

i can not see the out put before excel.

I wrote a script in unix environment under sql plus.
i can not see it. I am running this script through scheduler.
when I run script, output is directly goint into excel send it into outlook as an attchment in excel. I go to outlook open the attachment.
I do not have other options to run different way.
I am NOT using csv extention file. it is xls file.



Thx
N.

Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #237078 is a reply to message #237042] Fri, 11 May 2007 15:47 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
You have not listened to any of the suggestions and/or recommendations that people have made on this list. I opened your .xls file in Excel and you did NOT create the data file you loaded properly. You have been repeatedly told that the output you're generating is going into the "A" column of the spreadsheet and not into each column as you expect (per your own example that I opened). If you click on cell A6 of your .xls file, you'll see that THE ENTIRE LINE is in this cell. Excel has no way of knowing what the column delimiters are for your data unless YOU TELL IT. You do that by creating a .csv file with the data separated by commas (csv = Comma Separated Values). OR, you can use a different separator (I use "|" a lot), and save your data in a file that ends in .txt. When you open the .txt file in Excel, it will ASK you what the separator is for your data.

If you want your file to work, your output data MUST LOOK LIKE THIS:

ABCD.MNOPQQRSZPKDBHMD,ENDED_OK,05/01/07 20:27:32,05/01/07 21:27:31,+00:59:59,0
ANDJDKD,EELD.DLDLD,DKD,ENDED_OK,05/01/07 21:00:22,05/01/07 21:37:31,+00 00:37:09,0
HSYSHSJSKSKSKSKSKSKSD,ENDED_OK,05/01/07 21:27:31,05/01/07 21:28:24,+00 00:00:53,0
DJDHDHDHDHDHDHDHDHDHD,ENDED_OK,05/01/07 21:28:24,05/01/07 21:28:29,+00 00:00:05,0
JSJSJSJSJSJSJSJSJSJSD,ENDED_OK,05/01/07 21:28:46,05/01/07 21:44:41,+00 00:15:55,0
SKSKSKSKSKSKSKSKSKSKS,ENDED_OK,05/01/07 21:37:32,05/01/07 22:00:36,+00 00:23:04,0


and must go into a file with a .csv extension. When you open a file with this format in Excel, it will put the data into columns. YOU will then have to expand the columns to fit the data properly.

You had an example early on in this thread, when you got the ##### for the date fields. That file was formatted correctly. All you had to do was expand the columns so that the entire date/time would display.

You have also been provided with references to translate a .csv file into a .slk file by adding the proper coding to format the columns to the proper size as the file loads. With the wealth of information you've received on this topic, you should have absolutely no problem creating what you need and it amazes me that you still don't seem to understand what you've been told.

Maybe you just need to open Excel and create a database link to your table so you can get the information you need directly from the table and forget about generating any kind of file.

Ron
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #237081 is a reply to message #237077] Fri, 11 May 2007 15:59 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Whatever you believe, you are NOT creating a .xls file. If you were to open an actual .xls file with wordpad, you would see many "special" characters the Excel interprets as formatting commands when it reads the file. The file you create may end in .xls, but it is not a properly formatted .xls file as Excel understands it.

Without knowing the Excel formatting commands, the best you're going to get from sql output is delimited text.
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #237084 is a reply to message #237078] Fri, 11 May 2007 16:07 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Ron,
Thanks for qucikc reply.I understand now.I really appreciate it.If there is no other option with out SLK to view the report in excel( with out doing manully drag the columns),
I will try use database link in excel as you told me.
how to setup the DB link into excel.I went excel menu bar, click data, cilck on import wizard data, then import data or New database query. from there onwards, i lost.How to connect the link.Pl help me.
thx.
N.

Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #238724 is a reply to message #236743] Sat, 19 May 2007 13:34 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Bill,
I want use SLK file .
Could u please provide me the actual code i need to put in the
sql query and also what changes to make.
thx
N.
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #239091 is a reply to message #234587] Mon, 21 May 2007 08:10 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
the code for the sylf package follows. The documentation is in a word document that is attached. Good luck.

create or replace
package owa_sylk as
--
  type owaSylkArray is table of varchar2(2000);
--
  procedure show(
      p_file          in utl_file.file_type,
      p_query         in varchar2,
      p_parm_names    in owaSylkArray default owaSylkArray(),
      p_parm_values   in owaSylkArray default owaSylkArray(),
      p_sum_column    in owaSylkArray default owaSylkArray(),
      p_max_rows      in number     default 10000,
      p_show_null_as  in varchar2   default null,
      p_show_grid     in varchar2   default 'YES',
      p_show_col_headers in varchar2 default 'YES',
      p_font_name     in varchar2   default 'Courier New',
      p_widths        in owaSylkArray default owaSylkArray(),
      p_titles        in owaSylkArray default owaSylkArray(),
      p_formats       in owaSylkArray default owaSylkArray(),
      p_types         in owaSylkArray default owaSylkArray(),
      p_strip_html    in varchar2   default 'YES' );
--
  procedure show(
      p_file          in utl_file.file_type,
      p_cursor        in integer,
      p_sum_column    in owaSylkArray  default owaSylkArray(),
      p_max_rows      in number     default 10000,
      p_show_null_as  in varchar2   default null,
      p_show_grid     in varchar2   default 'YES',
      p_show_col_headers in varchar2 default 'YES',
      p_font_name     in varchar2   default 'Courier New',
      p_widths        in owaSylkArray default owaSylkArray(),
      p_titles        in owaSylkArray default owaSylkArray(),
      p_formats       in owaSylkArray default owaSylkArray(),
      p_types         in owaSylkArray default owaSylkArray(),
      p_strip_html    in varchar2   default 'YES' );
--
end owa_sylk;
/

create or replace
package body owa_sylk as
--
  g_cvalue  varchar2(32767);
  g_desc_t dbms_sql.desc_tab;

  type vc_arr is table of varchar2(2000) index by binary_integer;
  g_lengths vc_arr;
  g_sums vc_arr;
--
--

  g_file  utl_file.file_type;


  procedure p( p_str in varchar2 )
  is
  begin
    utl_file.put_line( g_file, p_str );
  exception
    when others then null;
  end;

  function build_cursor(
      q in varchar2,
      n in owaSylkArray,
      v in owaSylkArray ) return integer is
    c integer := dbms_sql.open_cursor;
    i number := 1;
  begin
    dbms_sql.parse (c, q, 1);
    loop
      dbms_sql.bind_variable( c, n(i), v(i) );
      i := i + 1;
    end loop;
    return c;
  exception
    when others then
      return c;
  end build_cursor;
--
--
  function str_html ( line in varchar2 ) return varchar2 is
    x       varchar2(32767) := null;
    in_html boolean         := FALSE;
    s       varchar2(1);
  begin
    if line is null then
      return line;
    end if;
    for i in 1 .. length( line ) loop
      s := substr( line, i, 1 );
      if in_html then
        if s = '>' then
          in_html := FALSE;
        end if;
      else
        if s = '<' then
          in_html := TRUE;
        end if;
      end if;
      if not in_html and s != '>' then
        x := x || s;
      end if;
    end loop;
    return x;
  end str_html;
--
  function ite( b boolean,
                t varchar2,
                f varchar2 ) return varchar2 is
  begin
    if b then
      return t;
    else
      return f;
    end if;
  end ite;
--
  procedure print_comment( p_comment varchar2 ) is
  begin
    return;
    p( ';' || chr(10) || '; ' || p_comment || chr(10) || ';' );
  end print_comment;
--
  procedure print_heading( font in varchar2, 
                           grid in varchar2, 
                           col_heading in varchar2, 
                           titles in owaSylkArray,
                           formats in owaSylkArray,
                           types in owaSylkArray ) 
  is
    l_title varchar2(2000);
    l_format varchar2(2000);
    l_type   varchar2(2000);
    datepic  varchar2(10);
  begin
    p( 'ID;ORACLE' );
    -- Put in a default format for date in case it is needed
    p( 'P;Pm/d/yyyy' );
    print_comment( 'Fonts' );
    p( 'P;F' || font || ';M200' );
    p( 'P;F' || font || ';M200;SB' );
    p( 'P;F' || font || ';M200;SUB' );
    --
    print_comment( 'Global Formatting' );
    p( 'F;C1;FG0R;SM1' || 
           ite( upper(grid)='YES', '', ';G' ) || 
           ite( upper(col_heading)='YES', '', ';H' )  );
    for i in 1 .. g_desc_t.count loop
      begin
        l_format := formats(i);
      exception
        when others then
          l_format := 'GOR';
      end;
      begin
        l_type := types(i);
      exception
        when others then
          l_type := 'T';
      end;
      -- Set the date format if type id D
      if l_type = 'D' then
        datepic := ';P0';
      else
        datepic := null;
      end if;

      p( 'F;C' || to_char(i+1) || datepic || ';F' || l_format || ';SM0' );
    end loop;
    --
    print_comment( 'Title Row' );
    p( 'F;R1;FG0C;SM2' );
    for i in 1 .. g_desc_t.count loop
      g_lengths(i) := g_desc_t(i).col_name_len;
      g_sums(i) := 0;
      begin
        l_title := titles(i);
      exception
        when others then
          l_title := g_desc_t(i).col_name;
      end;
      if i = 1 then
        p( 'C;Y1;X2;K"' || l_title || '"' );
      else
        p( 'C;X' || to_char(i+1) || ';K"' || l_title || '"' );
      end if;
    end loop;
  end print_heading;
--
  function print_rows(
      c            in integer,
      max_rows     in number,
      sum_columns  in owaSylkArray,
      show_null_as in varchar2,
      strip_html   in varchar2,
      types        in owaSylkArray ) return number is
    row_cnt number          := 0;
    line      varchar2(32767) := null;
    n         number;
    l_type    varchar2(2000);
    delimiter varchar2(1);
  begin
    loop
      exit when ( (max_rows <> 0 and row_cnt >= max_rows) or
                  dbms_sql.fetch_rows( c ) <= 0 );
      row_cnt := row_cnt + 1;
      print_comment( 'Row ' || row_cnt );
      --
      p( 'C;Y' || to_char(row_cnt+2) );

      for i in 1 .. g_desc_t.count loop
        begin
          l_type := types(i);
        exception
          when others then
            l_type := 'T';
        end;
        if l_type in ('N','D') then
          delimiter := null;
        else
          delimiter := '"';
        end if;
        dbms_sql.column_value( c, i, g_cvalue );
        g_cvalue := translate( g_cvalue, 
                            chr(10)||chr(9)||';', '   ' );
        g_cvalue := ite( upper( strip_html ) = 'YES',
                             str_html( g_cvalue ),
                             g_cvalue );
        g_lengths(i) := greatest( nvl(length(g_cvalue), 
                                  nvl(length(show_null_as),0)),
                                  g_lengths(i) );
        line := 'C;X' || to_char(i+1);
        line := line || ';K';
        begin
          n := to_number( g_cvalue );
          if upper( sum_columns(i)) = 'Y' then
            g_sums(i) := g_sums(i) + nvl(n,0);
          end if;
        exception
          when others then
            n := null;
        end;
        line := line || 
                 ite( n is null, 
                      ite( g_cvalue is null, 
                               '"'||show_null_as||
                                  '"', delimiter||g_cvalue||delimiter ), 
                             n );
        p( line );
      end loop;
      --
    end loop;
    return row_cnt;
  end print_rows;
--
  procedure print_sums(
      sum_columns  in owaSylkArray,
      row_cnt      in number ) is
  begin
    if sum_columns.count = 0 then
      return;
    end if;
    --
    print_comment( 'Totals Row' );
    p( 'C;Y' || to_char(row_cnt + 4) );
    p( 'C;X1;K"Totals:"' );
    --
    for i in 1 .. g_desc_t.count loop
      begin
        if upper(sum_columns(i)) = 'Y' then
          p( 'C;X' || to_char(i+1) || ';ESUM(R3C:R' || 
                  to_char(row_cnt+2) || 'C)' );
        end if;
      exception
        when others then
          null;
      end;
    end loop;
  end print_sums;
--
  procedure print_widths( widths owaSylkArray ) is
  begin
    print_comment( 'Format Column Widths' );
    p( 'F;W1 1 7' );
    for i in 1 .. g_desc_t.count loop
      begin
        p( 'F;W' || to_char(i+1) || ' ' || 
            to_char(i+1) || ' ' || 
            to_char(to_number(widths(i))) );
      exception
        when others then
          p( 'F;W' || to_char(i+1) || ' ' || 
               to_char(i+1) || ' ' || 
               greatest( g_lengths(i), length( g_sums(i) )));
      end;
    end loop;
    p( 'E' );
  end print_widths;
--
  procedure show(
      p_file          in utl_file.file_type,
      p_cursor        in integer,
      p_sum_column    in owaSylkArray default owaSylkArray(),
      p_max_rows      in number     default 10000,
      p_show_null_as  in varchar2   default null,
      p_show_grid     in varchar2   default 'YES',
      p_show_col_headers in varchar2 default 'YES',
      p_font_name     in varchar2   default 'Courier New',
      p_widths        in owaSylkArray default owaSylkArray(),
      p_titles        in owaSylkArray default owaSylkArray(),
      p_formats       in owaSylkArray default owaSylkArray(),
      p_types         in owaSylkArray default owaSylkArray(),
      p_strip_html    in varchar2   default 'YES' ) is
  --
    l_row_cnt number;
    l_col_cnt number;
    l_status  number;
  begin
    g_file := p_file;
    dbms_sql.describe_columns( p_cursor, l_col_cnt, g_desc_t );
    --
    for i in 1 .. g_desc_t.count loop
      dbms_sql.define_column( p_cursor, i, g_cvalue, 32765);
    end loop;
    --
    print_heading( p_font_name, 
                   p_show_grid, 
                   p_show_col_headers, 
                   p_titles,
                   p_formats,
                   p_types );
    l_status := dbms_sql.execute( p_cursor );
    l_row_cnt := print_rows(
                   p_cursor, 
                   p_max_rows,
                   p_sum_column,
                   p_show_null_as,
                   p_strip_html,
                   p_types );
    print_sums( p_sum_column, l_row_cnt );
    print_widths( p_widths );
  end show;
--
  procedure show(
      p_file          in utl_file.file_type,
      p_query         in varchar2,
      p_parm_names    in owaSylkArray default owaSylkArray(),
      p_parm_values   in owaSylkArray default owaSylkArray(),
      p_sum_column    in owaSylkArray default owaSylkArray(),
      p_max_rows      in number     default 10000,
      p_show_null_as  in varchar2   default null,
      p_show_grid     in varchar2   default 'YES',
      p_show_col_headers in varchar2 default 'YES',
      p_font_name     in varchar2   default 'Courier New',
      p_widths        in owaSylkArray default owaSylkArray(),
      p_titles        in owaSylkArray default owaSylkArray(),
      p_formats       in owaSylkArray default owaSylkArray(),
      p_types         in owaSylkArray default owaSylkArray(),
      p_strip_html    in varchar2   default 'YES' ) is
  begin
    show( p_file => p_file,
          p_cursor => build_cursor( p_query, 
                                    p_parm_names, 
                                    p_parm_values ),
          p_sum_column => p_sum_column,
          p_max_rows => p_max_rows,
          p_show_null_as => p_show_null_as,
          p_show_grid => p_show_grid,
          p_show_col_headers => p_show_col_headers,
          p_font_name => p_font_name,
          p_widths => p_widths,
          p_titles => p_titles,
          p_formats => p_formats,
          p_types => p_types,
          p_strip_html => p_strip_html );
  end show;
--
end owa_sylk;
/
  • Attachment: owa_sylk.doc
    (Size: 42.00KB, Downloaded 1716 times)
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #239280 is a reply to message #239091] Mon, 21 May 2007 22:28 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Bill,
Thank you very much for your great help.
I looked the word the doc. So, I keep everything same in that word doc except change the sql query to my query.am i right.
I will try let you know next week. i am sick.
you are really great help for me.
Thanks,N.

Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #239442 is a reply to message #234587] Tue, 22 May 2007 07:36 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
The owa_SYLK routine uses a utl_file handle to write out the file. You open an output stream then pass the query and handle to the routine. It take care of everything else. Make sure you handle dates the way you were shown.
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #244549 is a reply to message #234587] Wed, 13 June 2007 05:08 Go to previous messageGo to next message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member
Hi,

I created a csv file using SPOOL <filename>. File is created with proper allignment. The problem is that the records in the report are starting from line no 2 where as I want records to start from very first line i.e line no 1 in excel.

Can anyone help me out?

Regards
Satya
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #244558 is a reply to message #244549] Wed, 13 June 2007 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And why it is starting at line 2?
Mine starts at line 1.

Regards
Michel
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #244560 is a reply to message #244549] Wed, 13 June 2007 06:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
satya.das2007 wrote on Wed, 13 June 2007 12:08
Hi,

I created a csv file using SPOOL <filename>. File is created with proper allignment. The problem is that the records in the report are starting from line no 2 where as I want records to start from very first line i.e line no 1 in excel.

Can anyone help me out?

Regards
Satya

You mean when importing in Excel?

Indicate that there is no header row then.
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #244599 is a reply to message #234587] Wed, 13 June 2007 08:38 Go to previous messageGo to next message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member
I have used SQL report lots of time before but never faced such issue. This code is fetching records and formating properly but records starts from 2nd line.Check the attached excel sheet to verify.

Please check the complete code that I am using.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SET TERMOUT OFF;
SET VERIFY OFF;
SET ECHO OFF;
COLUMN TODAY NEW_VALUE TRIM(_DATE) NOPRINT;
COLUMN rowcount NEW_VALUE v_rowcount NOPRINT;
COLUMN v_error_no_var NEW_VALUE v_exit_code HEADING 'Error Code' FORMAT 9999999999 NOPRINT;
SET FEEDBACK OFF;
SET LINESIZE 350;
SET PAGESIZE 6400;
REPHEADER OFF;

WHENEVER SQLERROR EXIT 98;
WHENEVER OSERROR EXIT 99;

VARIABLE gn_error_no NUMBER;
VARIABLE gc_batch_run_date CHAR;
VARIABLE gn_count_rows NUMBER;

VARIABLE v_file_path VARCHAR;

SELECT 0 rowcount FROM DUAL;

SET UNDERLINE OFF
COLUMN creation_date HEADING 'CREATION_DATE,' FORMAT A15 TRUNC PRINT;
COLUMN asn_line_id HEADING 'ASN_LINE_ID,' FORMAT A12 TRUNC PRINT;
COLUMN asn_header_id HEADING 'ASN_HEADER_ID,' FORMAT A23 TRUNC PRINT;
BREAK ON ASN_LINE_ID;

SET TERMOUT ON;
SPOOL shipment_detail_report.csv
SET HEADING ON;
SELECT NVL(ROWNUM,0) ROWCOUNT
,TO_DATE(IOALT.CREATION_DATE,'DD/MM/YYYY') CREATION_DATE
,','||IOALT.ASN_LINE_ID ASN_LINE_ID
,','||IOALT.ASN_HEADER_ID ASN_HEADER_ID
FROM table1 IOALT,
table2 IOAHT,
table3 IOAOT
WHERE IOALT.ASN_HEADER_ID=IOAHT.ASN_HEADER_ID
AND IOALT.ASN_ORDER_ID=IOAOT.ASN_ORDER_ID
AND IOAHT.ASN_HEADER_ID= IOAOT.ASN_HEADER_ID
AND IOALT.WAREHOUSE_CODE='FRT'
AND IOALT.INTERFACE_CODE='AWSD'
AND IOAHT.DELIVERY_CUST_ACCT_NUMBER='123'
AND IOALT.PROCESSED_FLAG='P';
REPHEADER OFF;
SET TERMOUT OFF;
COLUMN v_error_desc_var HEADING 'Error Description' FORMAT A50 NOPRINT;

SET FEEDBACK OFF;
SPOOL OFF;

SELECT DECODE(NVL(&v_rowcount,0),0,3,0) v_error_no_var
, DECODE(TO_CHAR(NVL(&v_rowcount,0))
,'0' ,'Shipment_Detail_Report Exception Report Success (No data returned)'
,'Shipment_Detail_Report Exception Report Success (Data returned)') v_error_desc_var
FROM DUAL;

SET VERIFY ON;
SET ECHO ON;
SET TERMOUT ON;
SET FEEDBACK ON;

WHENEVER SQLERROR CONTINUE;
WHENEVER OSERROR CONTINUE;

EXIT v_exit_code;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Check attached excel file.
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #244609 is a reply to message #234587] Wed, 13 June 2007 08:55 Go to previous messageGo to next message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member
I believe its a different issue. can we login as new incident in discussion forum?

Is it possible?
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #244616 is a reply to message #234587] Wed, 13 June 2007 09:03 Go to previous messageGo to next message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member
Hi,

I have created a new topic i.e. message #244613 for the issue that I am facing.

Sorry for inconvenience.

Regards
Satya
Re: Excel report not formatting correctly in SQL*Plus (multi-merge) [message #245358 is a reply to message #244549] Sat, 16 June 2007 07:07 Go to previous messageGo to previous message
NIckman
Messages: 64
Registered: May 2007
Member
Satya,

how did get proper allignment for all the columns using csv foramt file sending to excel sheet from oracle under unix environment.i send the file to outlook csv format as an attchament. when i opened My excel sheet columns are getting merged. how do you resolve the problem. let me know.thanks,N.


Previous Topic: How to replace Row_number and generate the same result set
Next Topic: date format
Goto Forum:
  


Current Time: Thu Sep 29 15:58:32 CDT 2016

Total time taken to generate the page: 0.20829 seconds