04-10-2008
Use SED command to do the need.
First of all i dont think u need any scripts..
U can do with a single line command along with pipes
Here is the command
sed '/;/G' test | grep "SELECT" | sed -e :a -e '$!N;s/\n/~/g;ta' | tr "~" "\n" >> targetfile
INPUT FILE : test
OUTPUT FILE : targetfile
Assumptions :
*************
1. The input file doesnt have any blank lines.
2. If it has then use the following sed command
sed '/^$/d' <sourcefile> <targetfile>
Here is my input file
cat test
1 SELECT * FROM DUAL;
2 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I ;
3 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );
4 ASDFASDFASDFASDFSAD
5 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );
THINGS THAT ARE TAKEN CARE OFF :
*********************************
1. There might be a single SELECT STATEMENT (no sub-queries).
2. There might be sub-queries which are written continiously (without breaking)
3. There might be sub-queries which are written in different statements.
Now the command :
sed '/;/G' test | grep "SELECT" | sed -e :a -e '$!N;s/\n/~/g;ta' | tr "~" "\n" >> targetfile
Explanation of the commands :
*****************
***sed '/;/G' test ***
*****************
This inserts a new line after the end of a complete SQL statment ie. when it encounters a ";" it inserts a line after it.
*****************
***grep "SELECT" ***
*****************
The output of the command sed '/;/G' test is piped and only the SELECT queries are grepped from that.
********************************
****sed -e :a -e '$!N;s/\n/~/g;ta'******
********************************
The output of the previous command sed '/;/G' test | grep "SELECT" is sent as input to the above command. This is manily done becoz if there are queries which span more than one line which is not continuous. (they might have pressed enter key and continued)
**************
***tr "~" "\n"***
**************
This will replace all "~" characters to "\n"
sed '/;/G' test | grep "SELECT" | sed -e :a -e '$!N;s/\n/~/g;ta' | tr "~" "\n"
1 SELECT * FROM DUAL;
2 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I ;
3 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );
5 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );
Here is the file which u needed....
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi,
I am working on a code to extract the table names out of a select statement. Is there anybody who has worked on something similar? May be you could provide me with the regular expression for the same.
Regards.
Silas (2 Replies)
Discussion started by: silas.john
2 Replies
2. UNIX for Dummies Questions & Answers
Hi All,
I have written 4 sql queries . Now I want to write one SHELL SCRIPTING program for all these queries...
i.e
1.select * from head;
2. select * from detail;
3. delete from head;
4. delete from detail;
Please let me know how to write a shell script...
Thank you (1 Reply)
Discussion started by: user71408
1 Replies
3. Shell Programming and Scripting
Hi guys,
i know how to run a single query using mysql embedded in a shell script as follows:
`mysql -umyuser -pmypass --host myhost database<<SQL
${query};
quit
SQL`
However, how would i be able to run several queries within the same connection?
The reason for this is i am creating... (3 Replies)
Discussion started by: muay_tb
3 Replies
4. Shell Programming and Scripting
count.sh#!/bin/ksh
SQL1=`sqlplus -s usr/pwd @count.sql $1 $2 $3`
SQL2=`sqlplus -s usr/pwd @selectall.sql $1 $2 $3`
LIST="Count Select_All"
select i in $LIST
do
if
then
echo $SQL1
elif
then
echo $SQL2
fi
done (2 Replies)
Discussion started by: killboy
2 Replies
5. Shell Programming and Scripting
Hi,
I have an input file which have many lines,from which i need to extract only the complete sql statements and write this alone to an output file.
please help us in this.
Regards
Meva (7 Replies)
Discussion started by: meva
7 Replies
6. Shell Programming and Scripting
Hi,
Would someone know if I can fire nested sql queries in a shell script? Basically what I am trying to do is as follows:
my_sql=$(sqlplus -s /nolog<<EOF|sed -e "s/Connected. *//g"
connect... (2 Replies)
Discussion started by: shrutihardas
2 Replies
7. UNIX for Advanced & Expert Users
Hi,
First query:
I am trying to execute the below command to pull all the record whose length is not of the expected. But this is not giving the expected results.
$2 is the record length passed in the script as second parameter.$filename is the filename on which the awk is executed.It is... (4 Replies)
Discussion started by: devina
4 Replies
8. Shell Programming and Scripting
Hi all,
I have script, which performing sql queries and put output into file.
When I run this script manually, its working fine, but when I want to schedule it with cron I am getting errors...
I defined LD_LYBRARY_PATH and ,but no result. After I defined it, I am getting error:
# more... (4 Replies)
Discussion started by: nypreH
4 Replies
9. Shell Programming and Scripting
How to run multiple Queries in a ksh Script
I have a KSH script that has one SQL Query and generates and emails output of the query in HTML format. I want to change the script so that it has three SQL queries and the last query generates and emails the HTML output page of just that query.
So far... (5 Replies)
Discussion started by: JolietJake
5 Replies
10. Shell Programming and Scripting
Hi Team,
I am Oracle Databse developer. I am currently working on two databases.
DB1 and DB2.
in DB1 I have a Select query which will return 100 records.
In Db2 I have a Select query which also return 100 records.
In these two tables ( in different Schemas) we have a common column.
... (2 Replies)
Discussion started by: vasuvv
2 Replies
LEARN ABOUT MOJAVE
dbix::class::sqlmaker::limitdialects
DBIx::Class::SQLMaker::LimitDialects(3) User Contributed Perl Documentation DBIx::Class::SQLMaker::LimitDialects(3)
NAME
DBIx::Class::SQLMaker::LimitDialects - SQL::Abstract::Limit-like functionality for DBIx::Class::SQLMaker
DESCRIPTION
This module replicates a lot of the functionality originally found in SQL::Abstract::Limit. While simple limits would work as-is, the more
complex dialects that require e.g. subqueries could not be reliably implemented without taking full advantage of the metadata locked within
DBIx::Class::ResultSource classes. After reimplementation of close to 80% of the SQL::Abstract::Limit functionality it was deemed more
practical to simply make an independent DBIx::Class-specific limit-dialect provider.
SQL LIMIT DIALECTS
Note that the actual implementations listed below never use "*" literally. Instead proper re-aliasing of selectors and order criteria is
done, so that the limit dialect are safe to use on joined resultsets with clashing column names.
Currently the provided dialects are:
LimitOffset
SELECT ... LIMIT $limit OFFSET $offset
Supported by PostgreSQL and SQLite
LimitXY
SELECT ... LIMIT $offset $limit
Supported by MySQL and any SQL::Statement based DBD
RowNumberOver
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER( ORDER BY ... ) AS RNO__ROW__INDEX FROM (
SELECT ...
)
) WHERE RNO__ROW__INDEX BETWEEN ($offset+1) AND ($limit+$offset)
ANSI standard Limit/Offset implementation. Supported by DB2 and MSSQL >= 2005.
SkipFirst
SELECT SKIP $offset FIRST $limit * FROM ...
Suported by Informix, almost like LimitOffset. According to SQL::Abstract::Limit "... SKIP $offset LIMIT $limit ..." is also supported.
FirstSkip
SELECT FIRST $limit SKIP $offset * FROM ...
Supported by Firebird/Interbase, reverse of SkipFirst. According to SQL::Abstract::Limit "... ROWS $limit TO $offset ..." is also
supported.
RowNum
Depending on the resultset attributes one of:
SELECT * FROM (
SELECT *, ROWNUM rownum__index FROM (
SELECT ...
) WHERE ROWNUM <= ($limit+$offset)
) WHERE rownum__index >= ($offset+1)
or
SELECT * FROM (
SELECT *, ROWNUM rownum__index FROM (
SELECT ...
)
) WHERE rownum__index BETWEEN ($offset+1) AND ($limit+$offset)
or
SELECT * FROM (
SELECT ...
) WHERE ROWNUM <= ($limit+1)
Supported by Oracle.
Top
SELECT * FROM
SELECT TOP $limit FROM (
SELECT TOP $limit FROM (
SELECT TOP ($limit+$offset) ...
) ORDER BY $reversed_original_order
) ORDER BY $original_order
Unreliable Top-based implementation, supported by MSSQL < 2005.
CAVEAT
Due to its implementation, this limit dialect returns incorrect results when $limit+$offset > total amount of rows in the resultset.
FetchFirst
SELECT * FROM
(
SELECT * FROM (
SELECT * FROM (
SELECT * FROM ...
) ORDER BY $reversed_original_order
FETCH FIRST $limit ROWS ONLY
) ORDER BY $original_order
FETCH FIRST $limit ROWS ONLY
)
Unreliable FetchFirst-based implementation, supported by IBM DB2 <= V5R3.
CAVEAT
Due to its implementation, this limit dialect returns incorrect results when $limit+$offset > total amount of rows in the resultset.
GenericSubQ
SELECT * FROM (
SELECT ...
)
WHERE (
SELECT COUNT(*) FROM $original_table cnt WHERE cnt.id < $original_table.id
) BETWEEN $offset AND ($offset+$rows-1)
This is the most evil limit "dialect" (more of a hack) for really stupid databases. It works by ordering the set by some unique column, and
calculating the amount of rows that have a less-er value (thus emulating a "RowNum"-like index). Of course this implies the set can only be
ordered by a single unique column.
Also note that this technique can be and often is excruciatingly slow. You may have much better luck using "software_limit" in
DBIx::Class::ResultSet instead.
Currently used by Sybase ASE, due to lack of any other option.
AUTHORS
See "CONTRIBUTORS" in DBIx::Class.
LICENSE
You may distribute this code under the same terms as Perl itself.
perl v5.18.2 2014-01-22 DBIx::Class::SQLMaker::LimitDialects(3)