Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Multiple lines into one line when ; is found

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Multiple lines into one line when ; is found
# 1  
Old 02-05-2010
Error Multiple lines into one line when ; is found
I have sql's in a file separated by ";", need to put the sql in one single line till i find a ";" The input file is like this

Code:
SELECT         s.sid, 
                s.serial#, 
                p.spid as "OS PID", 
                s.username, 
                s.module, 
                st.value/100 as "CPU sec" 
FROM         v$sesstat st, 
                v$statname sn, 
                v$session s, 
                v$process p 
WHERE         sn.name                 = 'CPU used by this session' 
AND                 st.statistic#                 = sn.statistic# 
AND                 st.sid                         = s.sid 
AND                 s.paddr                 = p.addr 
AND                 s.last_call_et         < 3600 
AND                 s.logon_time                 > (SYSDATE - 240/1440) 
AND                 st.value/100                 > 10 
ORDER BY         st.value;SELECT         s.sid, 
                s.serial#, 
                p.spid as "OS PID", 
                s.username, 
                s.module, 
                st.value/100 as "CPU sec" 
FROM         v$sesstat st, 
                v$statname sn, 
                v$session s, 
                v$process p 
WHERE         sn.name                 = 'CPU used by this session' 
AND                 st.statistic#                 = sn.statistic# 
AND                 st.sid                         = s.sid 
AND                 s.paddr                 = p.addr 
AND                 s.last_call_et         < 3600 
AND                 s.logon_time                 > (SYSDATE - 240/1440) 
AND                 st.value/100                 > 10 
ORDER BY         st.value;SELECT         s.sid, 
                s.serial#, 
                p.spid as "OS PID", 
                s.username, 
                s.module, 
                se.time_waited, 
                se.event 
FROM         v$session_event se, 
                v$session s, 
                v$process p 
WHERE         s.last_call_et         < 3600 
AND                 s.logon_time                 > (SYSDATE - 240/1440) 
AND                 se.sid                         = s.sid 
AND                 s.paddr                 = p.addr 
AND                 se.time_waited         > 10 
ORDER BY         se.time_waited desc;

I need the output as follows,

SELECT s.sid, s.serial#,p.spid as "OS PID", s.username,s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic#= sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module,se.time_waited, se.event FROM v$session_event se, v$session s, v$process p WHERE s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND se.sid = s.sid AND s.paddr = p.addr AND se.time_waited > 10 ORDER BY se.time_waited desc;
SELECT s.sid, s.serial#,p.spid as "OS PID", s.username,s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic#= sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;

till i see ";" i need to consider that as one single line and in next line i ll get next sql statement till one ;

thanks
gopal
Last edited by vbe; 02-05-2010 at 01:57 PM.. Reason: code tags please!
# 2  
Old 02-05-2010
How about:
Code:
awk '$1=$1' RS=';' ORS=';\n' infile

output:
Code:
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited, se.event FROM v$session_event se, v$session s, v$process p WHERE s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND se.sid = s.sid AND s.paddr = p.addr AND se.time_waited > 10 ORDER BY se.time_waited desc;

# 3  
Old 02-06-2010
Hey thanks... Its a good one. when i used ur command it just executes a "return" when it finds a ";". But i need it in a single line till ";" When I redirected it shows multiple lines there.

Kindly help me.

Ur o/p is contradicting to my o/p.

Thanks
gopal
# 4  
Old 02-06-2010
What output did you get? CAnyou show an example? If you are using Solaris, use /usr/xpg4/bin/awk or nawk.
# 5  
Old 02-06-2010
Quote:
Originally Posted by ilugopal
Hey thanks... Its a good one. when i used ur command it just executes a "return" when it finds a ";". But i need it in a single line till ";" When I redirected it shows multiple lines there.

Kindly help me.

Ur o/p is contradicting to my o/p.

Thanks
gopal
how about
Code:
'paste -s':  echo "a
b" | paste -s

gives "a\tb" output

see 'man paste' for even cooler operations (like specifiyig delimiters).

Finally you can split up your line again at ';' using:

Code:
sed -e 's/;/;\n/g'

In your example (named your.code)

Code:
cat your.code | paste -d" " -s | sed -e 's/;/;\n/g' -e 's/[ \t]\+/ /g' > perfect.txt

Code:
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited, se.event FROM v$session_event se, v$session s, v$process p WHERE s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND se.sid = s.sid AND s.paddr = p.addr AND se.time_waited > 10 ORDER BY se.time_waited desc;

Last edited by Franklin52; 02-06-2010 at 10:11 AM.. Reason: Please use code tags!
# 6  
Old 02-10-2010
MySQL the answer
It worked completely but a small change in the paste command
Code:
cat your.code | paste -d" " -s - | sed -e 's/;/;\n/g' -e 's/[ \t]\+/ /g' > perfect.txt

I just included a "-" after -s, it works and also used that awk command given above.

Thanks for all...Smilie
Last edited by Scott; 02-10-2010 at 05:46 PM.. Reason: Code tags, please...
# 7  
Old 02-10-2010
Question
Quote:
Originally Posted by Scrutinizer
How about:
Code:
awk '$1=$1' RS=';' ORS=';\n' infile

output:
Code:
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited, se.event FROM v$session_event se, v$session s, v$process p WHERE s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND se.sid = s.sid AND s.paddr = p.addr AND se.time_waited > 10 ORDER BY se.time_waited desc;

I dont understand what was wrong with Scrutinizer's code it worked very well with me
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Modify text file if found multiple pattern match for every line.

Looking for help, i have input file like below and want to modify to expected output, if can without create additional file, hope can direct modify it. have 2 thing need do. 1st is adding a word (testplan generation off) after ! ! IPG: Tue Aug 07 14:31:17 2018 2nd is adding... (16 Replies)
Discussion started by: kttan
16 Replies

2. UNIX for Beginners Questions & Answers

Multiple lines to single line

I have code as below # create temporary table `temp4277`(key(waybill_no)) select waybill_no,concat_ws('',card_type,card_series_no) cardinfo from rfid_temp_ticket where waybill_no='4277' group by... (4 Replies)
Discussion started by: kaushik02018
4 Replies

3. Shell Programming and Scripting

Deleting all the N lines after the line in which text is found.

Hi! I want to delete N (say 10) lines after the line which text is found in a file "A".Also to delete the line in which the text is found. Only one occurrence of the search string in the file "A" The text to be deleted is in another text file "B". All the search texts in the file "B" are in... (3 Replies)
Discussion started by: shahid1632
3 Replies

4. Shell Programming and Scripting

Deleting Multiple Lines in a File1 using critera found from File 2

Hi Everyone! I would like ask if there's a better way I can delete multiple lines in a file1 by collecting all criteria from file2. file1: a b c d e f file2: a e f The expected value will be: b (3 Replies)
Discussion started by: zzavilz
3 Replies

5. Shell Programming and Scripting

Merge multiple lines to one line when line starts with and ends with

example: comment Now_TB.table column errac is for error messages 1 - first 2 - second 3 -third ; in this example I need to be able to grab the comment as first word and ; as the last word and it might span a few lines. I need it to be put all in one line without line breaks so I can... (4 Replies)
Discussion started by: wambli
4 Replies

6. UNIX for Dummies Questions & Answers

Display n lines after match found and other line

I have a file like this DoctorName Address1 Address2 DOB InsuredName Address1 Address2 DOB PatientName Address1 Address2 DOB ClaimNo1 DoctorName Address1 Address2 DOB InsuredName (2 Replies)
Discussion started by: nsuresh316
2 Replies

7. Shell Programming and Scripting

search and replace, when found, delete multiple lines, add new set of lines?

hey guys, I tried searching but most 'search and replace' questions are related to one liners. Say I have a file to be replaced that has the following: $ cat testing.txt TESTING AAA BBB CCC DDD EEE FFF GGG HHH ENDTESTING This is the input file: (3 Replies)
Discussion started by: DeuceLee
3 Replies

8. Shell Programming and Scripting

Print lines after the search string until blank line is found

All I want is to look for the pattern in the file...If I found it at # places... I want print lines after those pattern(line) until I find a blank line. Log EXAMPLE : MT:Exception caught The following Numbers were affected: 1234 2345 2346 Error java.lang.InternalError:... (3 Replies)
Discussion started by: prash184u
3 Replies

9. Shell Programming and Scripting

Multiple lines into one line

Hi all I have a file with these lines good gone home What code do you need to put this on one line like this good gone home (2 Replies)
Discussion started by: hawaiifiver
2 Replies

10. Shell Programming and Scripting

Splitting the line in multiple lines

Hi Guys, I need a help. I am pretty new to the shell level programing. I was trying to split a long lines to a multiple lines with few conditions. I goggle for the code and found some snippets and tried to modified it but I got few strange problems too. I have to split the lines if line is ... (3 Replies)
Discussion started by: dd_sh
3 Replies
Login or Register to Ask a Question