Home » Other » Client Tools » SQL Report Problem
SQL Report Problem [message #244613] Wed, 13 June 2007 08:59 Go 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?

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: SQL Report Problem [message #245028 is a reply to message #244613] Fri, 15 June 2007 00:46 Go to previous messageGo to next message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member
Can anyone help me out?
Re: SQL Report Problem [message #245045 is a reply to message #244613] Fri, 15 June 2007 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For myself I don't read 100 lines of non-formatted code.

Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts andHow to get a quick answer to your question: TIPS AND TRICKS

Regards
Michel

Re: SQL Report Problem [message #245167 is a reply to message #244613] Fri, 15 June 2007 06:33 Go to previous messageGo to next message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member
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;
Re: SQL Report Problem [message #245242 is a reply to message #245167] Fri, 15 June 2007 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Now can you post a small test case that we can reproduce.
Center on the problem.
Change your query to small one using dual or scott tables...

Regards
Michel
Re: SQL Report Problem [message #245532 is a reply to message #244613] Mon, 18 June 2007 00:54 Go to previous messageGo to next message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member
Hi,
I have reduced the code and used dual table but still my output starts from 2nd line.

Please verify & suggest.

SET TERMOUT OFF;
SET VERIFY OFF;
SET ECHO OFF;
SET FEEDBACK OFF;
SET LINESIZE 350;
SET PAGESIZE 6400;

REPHEADER OFF;
SET UNDERLINE OFF

COLUMN creation_date HEADING 'CREATION_DATE,' FORMAT A15 TRUNC PRINT;

SPOOL shipment_detail_report.csv

SET HEADING ON;
SET TERMOUT ON;

SELECT SYSDATE FROM DUAL;

REPHEADER OFF;
SET TERMOUT OFF;
SET FEEDBACK OFF;
SPOOL OFF;
SET VERIFY ON;
SET ECHO ON;
SET TERMOUT ON;
SET FEEDBACK ON;
Re: SQL Report Problem [message #245537 is a reply to message #245532] Mon, 18 June 2007 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I copy and paste in a file what you posted and execute it and got:
SYSDATE
18/06/2007 08:08:10

without any blank line.

Regards
Michel
Re: SQL Report Problem [message #245637 is a reply to message #244613] Mon, 18 June 2007 04:43 Go to previous messageGo to next message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member

Hi Michel,

I have created a sql script(shipment_report.sql), which contain the above codes.

When I execute the shipment_report.sql script in oracle i.e sql>@c:\shipment_report.sql, it generates a .csv file(shipment_detail_report.csv).

Please verify the uploaded shipment_detail_report.csv file.

Is it some issue with my excel?

Regards
Satya






Re: SQL Report Problem [message #245645 is a reply to message #245637] Mon, 18 June 2007 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sorry, I can't reproduce what you have so I can't help you more.
Remove all glogin.sql or login.sql files.
Add "ttile off"
Just ideas, shooting in the dark, don't know of it has anything with your problem.

Regards
Michel
Re: SQL Report Problem [message #252679 is a reply to message #244613] Thu, 19 July 2007 10:58 Go to previous message
ravenmad
Messages: 1
Registered: July 2007
Junior Member
Hi,
The solution is to put the following Set command in your script before you spool the contents of the table to CSV:

SET NEWP NONE

This sets the number of blank lines between the top of each page and the top title.


Regards,
Kieran
Previous Topic: Connecting VB to a database created with SQL Developer
Next Topic: Oracle SQL Developer - description of remote table
Goto Forum:
  


Current Time: Sat Dec 07 03:29:21 CST 2024