dynamic output

Shell Programming and Scripting
1

Hi Guys,

i have a input file as below

SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qry
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username ='xxx_yyy_aaa_01'
ORDER    BY LastRespTime DESC

The output needed is just "=" which exist before 'xxx_yyy_aaa_01'

also if the same data file as below having "in"

SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qry
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username in('xxx_yyy_aaa_01')
ORDER    BY LastRespTime DESC

then the output needed is just "in"..

i want to achive the resultant dynamically either = or in based on the data.

Can this be done in single statement or using pearl scripting..

any help over here is appreciated !!

thanks,
nitin

2

I believe you mean Perl scripting. :wink:

$ 
$ 
$ cat file1.sql
SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qry
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username ='xxx_yyy_aaa_01'
ORDER    BY LastRespTime DESC
$ 
$ perl -lne '/^.* (.*?)[(]*.xxx_yyy_aaa_01.*$/ && print $1' file1.sql
=
$ 
$ 
$ cat file2.sql
SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qry
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username in('xxx_yyy_aaa_01')
ORDER    BY LastRespTime DESC
$ 
$ perl -lne '/^.* (.*?)[(]*.xxx_yyy_aaa_01.*$/ && print $1' file2.sql
in
$ 
$

tyler_durden

3

Hi,

Do you want to check , the operator used only on the column "username" ? or any other columns as well ?

4

HI,

if there is a space before 'xxx_yyy_aaa_01' and also if there are no spaces before 'xxx_yyy_aaa_01' then how to modify the code..

SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qry
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username = 'xxx_yyy_aaa_01'
ORDER    BY LastRespTime DESC

thanks

5

try some thing like this:

 
grep username inputfile | sed 's/.*username\([ = in]*\).*/\1/g'
6

Thank You ... But i am looking out in perl

---------- Post updated at 06:11 AM ---------- Previous update was at 06:01 AM ----------

Thank You...

Assume if there are space before xxx_yyy_aaa_01 , can pls provide me the perl script for this.

SEL elapsedtime ,StartTime,QueryText
FROM dbc.qry
WHERE StartTime >= '2010-03-24 20:10:08'
AND username = 'xxx_yyy_aaa_01'
ORDER BY LastRespTime DESC

or

SEL elapsedtime ,StartTime,QueryText
FROM dbc.qry
WHERE StartTime >= '2010-03-24 20:10:08'
AND username in 'xxx_yyy_aaa_01'
ORDER BY LastRespTime DESC
7

Here are a few cases I could think of -

$
$
$ # equal-to (=) operator not followed by space
$ cat file1.sql
SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qry
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username ='xxx_yyy_aaa_01'
ORDER    BY LastRespTime DESC
$
$ perl -lne '/^(\s*\w+){2}\s*(.*?)[\s(]*.xxx_yyy_aaa_01.*$/ && print $2' file1.sql
=
$
$ # equal-to (=) operator followed by a space
$ cat file2.sql
SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qry
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username = 'xxx_yyy_aaa_01'
ORDER    BY LastRespTime DESC
$
$ perl -lne '/^(\s*\w+){2}\s*(.*?)[\s(]*.xxx_yyy_aaa_01.*$/ && print $2' file2.sql
=
$
$ # IN operator followed by bracket "(" not followed by space
$ cat file3.sql
SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qry
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username in('xxx_yyy_aaa_01')
ORDER    BY LastRespTime DESC
$
$ perl -lne '/^(\s*\w+){2}\s*(.*?)[\s(]*.xxx_yyy_aaa_01.*$/ && print $2' file3.sql
in
$
$ # IN operator followed by space followed by bracket "("
$ cat file4.sql
SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qry
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username in ('xxx_yyy_aaa_01')
ORDER    BY LastRespTime DESC
$
$ perl -lne '/^(\s*\w+){2}\s*(.*?)[\s(]*.xxx_yyy_aaa_01.*$/ && print $2' file4.sql
in
$
$ # IN operator followed by space not followed by bracket "("
$ cat file5.sql
SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qry
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username in 'xxx_yyy_aaa_01'
ORDER    BY LastRespTime DESC
$
$ perl -lne '/^(\s*\w+){2}\s*(.*?)[\s(]*.xxx_yyy_aaa_01.*$/ && print $2' file5.sql
in
$
$

HTH,
tyler_durden

8

Thanks a lot Tyler.. Appreciate your qucik reply ... as i have zero knowledge on Perl... hence i would like to understand how actually how the script is working.. Thanks again..

9

In the simplest terms, the script searches for a line that matches the regular expression pattern and prints a chunk from the pattern.

If you have zero knowledge of Perl, then you are lucky as nowadays there's a huge wealth of resources on the Internet and books etc. that can help you. Google is your friend here. Books by O'Reilly are pretty good.

I can tell you what that regex does, but it would be better if you have at least a rudimentary knowledge of terms like "metacharacters", "backreferences", "character-classes", "repetition quantifiers" before that. You may want to check out the book by Jeffrey Friedl, which is probably one of the best on regexes.

HTH,
tyler_durden

10

Thanks Tyler...

Assume i have the input file as below

SEL elapsedtime ,StartTime,QueryText
FROM     dbc.qrylog
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username in ('tst1','xxx_yyy_drv_01')

Now i need the ouptut as in , can this be done using perl ... apprceiate help over here

11

Yes, it can be done using Perl -

$
$
$ cat file6.sql
SELECT elapsedtime ,StartTime,QueryText
FROM     dbc.qrylog
WHERE    StartTime >= '2010-03-24 20:10:08'
        AND      username in ('tst1','xxx_yyy_drv_01')
$
$
$ perl -lne '/^(\s*\w+){2}\s*(=|in)[\s(]*.*$/ && print $2' file6.sql
in
$
$

tyler_durden

12

Originally Posted by nitinrp1
...
Assume i have the input file as below

Code:
SEL elapsedtime ,StartTime,QueryText
FROM dbc.qrylog
WHERE StartTime >= '2010-03-24 20:10:08'
AND username in ('tst1','xxx_yyy_drv_01')
and user = ('abc_aaa')

For the above code if i run the perl script given by its gives out put as in and =

the requried output should be only in

thanks again..
pls help

13

It returns output "in" because "in" is present in the query. Check that last but one line of your query.

It returns output "=" because "=" is present in the query. Check the last line of your query.

(a) Why should it be "in" ?
(b) Why should it not be "=" ?

tyler_durden