Home » SQL & PL/SQL » SQL & PL/SQL » Formatting the sql query results using shell scripts
Formatting the sql query results using shell scripts [message #272163] Thu, 04 October 2007 02:46 Go to next message
chithu
Messages: 6
Registered: October 2007
Location: India
Junior Member
Hi,

I am finding problem in formatting of the header line and data.
Here i am attaching the report. Pls go through that and give a solution ASAP.

the code used is..
#Query for extraction and save the result in a file
sqlplus -s / << EOF1 > test.csv
set linesize 450
set pagesize 50000
set feedback off
select max(ci) as iCallNo
      ,','||mphoneid as PhoneId
      ,','||noanp as 10DigitPhone
      ,','||max(start_time) as Start_Time
      ,','|| max(end_time) as End_Time
      ,','|| resc as Result_Code
      ,','||res as Result_Description
      ,','||ext_id
      ,','||ext_name
from (select c.ICALLNO ci
            ,max(r.PHONEID) mphoneid
            ,max(to_char(c.start_dtime, 'DD-MON-YYYY hh24:mi:ss')) START_TIME
            ,max(to_char(c.end_dtime, 'DD-MON-YYYY hh24:mi:ss'))  END_TIME
            ,min(r1.PHONEID_NOANP) noanp
            ,max(rc.irescode) resc
            ,max(rc.RESC_DESC) res
            ,max(ce.EXTENSION_ID) ext_id
            ,max(ce.EXTENSION_NAME) ext_name
      from   calls c
            ,results r
            ,results r1
            ,result_codes rc
            ,campaign_extensions ce
      where c.start_dtime between to_date('$2 00:00:00', 'DD-MON-YYYY hh24:mi:ss') 
                              and to_date('$3 23:59:59', 'DD-MON-YYYY hh24:mi:ss') 
      and   c.icallno = r.icallno(+) 
      and   c.icallno = r1.icallno(+)
      and   r.irescode = rc.irescode 
      and   c.PAID_BY_ICAMPXNO = ce.icampxno
      and   c.paid_by_ICAMPXNO in (select ICAMPXNO 
                                   from   campaign_extensions
                                   where  ICAMPNO in (${campArr[@]})) 
      and   r.PHONEID is not null 
      and   r1.PHONEID is not null
      and   rc.irescode <> 100 
      and   rc.irescode <> 199
      group by c.icallno)
group by ci, mphoneid, noanp, resc, res, ext_id, ext_name
order by ci, mphoneid, noanp, resc, res, ext_id, ext_name;
EOF1
#Send the extracted report
if [ -e "test.csv" ]
then
(echo "Hi Chitra ,  Please find the report below."; uuencode test.csv test.csv) | mailx -s "Test
 Report" `awk '{print $2}' $conFile`
fi


{NOW it's formatted}
  • Attachment: test.csv
    (Size: 6.88KB, Downloaded 170 times)

[Updated on: Thu, 04 October 2007 04:42] by Moderator

Report message to a moderator

Re: Formatting the sql query results using shell scripts [message #272167 is a reply to message #272163] Thu, 04 October 2007 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Formatting the sql query results using shell scripts [message #272171 is a reply to message #272167] Thu, 04 October 2007 03:04 Go to previous messageGo to next message
chithu
Messages: 6
Registered: October 2007
Location: India
Junior Member
the oracle version is Oracle8i Enterprise Edition Release 8.1.7.4.0
Re: Formatting the sql query results using shell scripts [message #272187 is a reply to message #272171] Thu, 04 October 2007 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I still can't read non formatted post. Have a look at Edit button.

Regards
Michel
Re: Formatting the sql query results using shell scripts [message #272203 is a reply to message #272187] Thu, 04 October 2007 03:59 Go to previous messageGo to next message
chithu
Messages: 6
Registered: October 2007
Location: India
Junior Member
Have a look now..
Re: Formatting the sql query results using shell scripts [message #272215 is a reply to message #272203] Thu, 04 October 2007 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not formatted, this is just a mess of letters enclosed between code tags.
Do you program like that?
I understand you can't understand your script.

Regards
Michel
Re: Formatting the sql query results using shell scripts [message #272216 is a reply to message #272203] Thu, 04 October 2007 04:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, have a look NOW.
There is a slight difference between formatting code, and just sticking CODE tags round it.
That piece of formatting took me about 2 minutes. I'f I'd had SQL Developer running, I could have used its formatter and done it in seconds.
Why couldn't you do it?
Re: Formatting the sql query results using shell scripts [message #272225 is a reply to message #272163] Thu, 04 October 2007 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now the post is formatted (thanks JRowbottom), what is the problem, I don't see any?

Regards
Michel

[Updated on: Thu, 04 October 2007 04:52]

Report message to a moderator

Re: Formatting the sql query results using shell scripts [message #272239 is a reply to message #272225] Thu, 04 October 2007 05:10 Go to previous messageGo to next message
chithu
Messages: 6
Registered: October 2007
Location: India
Junior Member
Have a look at the attached file..
the heading is messed up..
Re: Formatting the sql query results using shell scripts [message #272252 is a reply to message #272239] Thu, 04 October 2007 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are tabs in your file.
As far as I know, SQL*Plus does not generate tabs in place of spaces.
What did you do with your file before posting it?

Regards
Michel
Re: Formatting the sql query results using shell scripts [message #272257 is a reply to message #272252] Thu, 04 October 2007 05:54 Go to previous messageGo to next message
chithu
Messages: 6
Registered: October 2007
Location: India
Junior Member
Actually i want to write a shell script to generate a report and send the same report in an e-mail as a attachment.
So i am not doing any changes before posting. just i am attaching to the mail.
Re: Formatting the sql query results using shell scripts [message #272264 is a reply to message #272257] Thu, 04 October 2007 06:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I agree with Michel - that file has been modified by something before it was posted here assuming that it came from your script.
Two things give this away:
1) There are Tab characters in the file. SQL*PLus doesn't (to the best of my knowledge) generate Tab chrs in its output
2) Your script sets the linesize to 450, but that file looks very much like its wrapped at 132 chrs.

Other than that, it looks ok.

Can you tell us clearly and concisely what the problem is?
Re: Formatting the sql query results using shell scripts [message #272271 is a reply to message #272264] Thu, 04 October 2007 06:29 Go to previous messageGo to next message
chithu
Messages: 6
Registered: October 2007
Location: India
Junior Member
I agree with your suggestions.
I will try with less line size.
Even i want to know how the tab characters are added?
i ftped the file from the unix box to my pc, even that file also contains the tab chars.
Re: Formatting the sql query results using shell scripts [message #272281 is a reply to message #272163] Thu, 04 October 2007 07:29 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
chithu wrote on Thu, 04 October 2007 03:46
,max(to_char(c.start_dtime, 'DD-MON-YYYY hh24:mi:ss')) START_TIME



Using MAX on DATEs converted to character strings is bad practice and just plain wrong.

22-JAN-2001 17:49:01 is greater than 04-OCT-2007 08:28:01

[Updated on: Thu, 04 October 2007 07:32]

Report message to a moderator

Previous Topic: Password Encryption
Next Topic: MEMBER OF operator
Goto Forum:
  


Current Time: Sat Dec 10 16:48:58 CST 2016

Total time taken to generate the page: 0.09996 seconds