LinuxQuestions.org - [SOLVED] Dyanmic query in awk

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - Dyanmic query in awk (https://www.linuxquestions.org/questions/linux-newbie-8/dyanmic-query-in-awk-4175491447/)

Dyanmic query in awk

Hi,

How to write Dynamic query in awk.

Say I have a awk query something like :

count=$(awk -F "|" '!/^#/ && !/^$/ {if( $1=="'$Name'" && $NF=="N")print $0}' $FILE |wc -l)

I want to make the condition inside If statement as dyanamic

Ex: var="'$'1=="C_Calendar" && '$'NF=="N"";
count=$(awk -F "|" '!/^#/ && !/^$/ {if($var)print $0}' $FILE |wc -l) --> This doesn't work is it possible in awk ?

Could you explain a little further as I am not following?

You can utilise bash variables using -v

I want to build a dynamic query using awk,

like today if we have 2 conditions tested here
tomorrow I can just modify the string and Add a third condition to test.

The following query with -v option didnt work

var="$1=="DANNY" && $NF=="N"";
echo $var;
count=echo $(awk -v $var=var -F "|" '!/^#/ && !/^$/ {if($var)print $0}' $FILE |wc -l);
echo $count;

Error Thrown
==DANNY && ==N
awk: fatal: `' is not a legal variable name
bash: 0: command not found...

Firstly, please use [code][/code] tags when showing code or data.

Ok ... I think I get you.

The short answer is no you can't at least not the way you have tried (obviously).

Code:

var="$1=="DANNY" && $NF=="N"";

This will create a string which will still be a string when used in awk, hence your if in the script would be the same as saying:

Code:

if("==DANNY && ==N")

This will always evaluate to true.

If you are wondering why your string is incomplete:

$1 will only have a value if an argument has been passed to the script

$NF is only known to awk unless you create a variable in bash with the same name

Hence both your strings are blank.

The $ symbol in bash is used to return the value assigned to a variable whereas in awk it is used to return the indirect value (ie if NF = 6 then $NF returns the item in the sixth column)
This is why $var has not worked and thrown an error in awk

Your assigment for the awk variable is the wrong way around:

Code:

-v $var=var

Here $var is not a legal name and even if it were, you would be assigning nothing as to call the value of a bash variable you need to use $.
So correct line would be:

Code:

-v var="$var"

This will get us to the situation I explained earlier though for when we now do:

Code:

if(var)

This will test that the variable has a value which is true (always unless $var from bash is empty)

The only way I can think of off the top of my head would be to assign all the values for the tests into a single string and then split into an array and test.
If you then wanted to perform other tests, ie not just equals to, this would be come very complicated as you would need to test for the test and perform each as found??

So this method would be:

Code:

var="1 DANNY NF N"



awk -v var="$var" 'BEGIN{n = split(var, arr);}{<perform tests on each part of array arr testing 1 against 2, 3 against 4 ...>}'

Not sure if that helps. generally I would say you need to rethink your process and what your goal is as perhaps this is not the best method

Not only column value, the column number is also dynamic in this case.

I tend to agree with grail. PERL, while still a scripting language, has a bit more flexibility that you'd be looking for. OR, you could do the selection in a shell script and call an awk script to do the parsing.

With this InFile1 ...

Code:

four score and seven years ago our fathers brought forth

on this continent a new nation conceived in liberty

and dedicated to the proposition that all men are created equal

now we are engaged in a great civil war testing whether that nation

or any nation so conceived and so dedicated can long endure

... and this InFile2 ...

Code:

6 nation

3 chevrolet

4 engaged

1 four

... this awk ...

Code:

awk '{if (FNR==NR){numb[NR]=$1;text[NR]=$2}

      else {for (j in numb)

        if(text[j]==$numb[j]) print}}' $InFile2 $InFile1 >$OutFile

... produced this OutFile ...

Code:

four score and seven years ago our fathers brought forth

on this continent a new nation conceived in liberty

now we are engaged in a great civil war testing whether that nation

Daniel B. Martin

Here is a minor variation of the solution posted earlier.
This makes the matched word STAND OUT.

With this InFile1 ...

Code:

four score and seven years ago our fathers brought forth

on this continent a new nation conceived in liberty

and dedicated to the proposition that all men are created equal

now we are engaged in a great civil war testing whether that nation

or any nation so conceived and so dedicated can long endure

... and this InFile2 ...

Code:

6 nation

3 chevrolet

4 engaged

1 four

... this awk ...

Code:

awk '{if (FNR==NR){numb[NR]=$1;text[NR]=$2}

      else {for (j in numb)

        if(text[j]==$numb[j])

          {$numb[j]=toupper($numb[j]);print}}}'  \

  $InFile2 $InFile1 >$OutFile

... produced this OutFile ...

Code:

FOUR score and seven years ago our fathers brought forth

on this continent a new NATION conceived in liberty

now we are ENGAGED in a great civil war testing whether that nation

Daniel B. Martin

Hi Daniel,

This doesnt looks a solution for the query, it looks like dynamic qury in awk seems a little difficult.

Daniels solution is dynamic as it means you simply add to the second file (InFile2) as many entries as you wish and those will be the rows and words searched for in the main file

Ok I got it. This is for Single column field,
if I need to check multiple column field (each column check having an AND condition)

At any time if I want to check different columns...

My query can be any of these

Code:

count=$(awk -F "|" '!/^#/ && !/^$/ {if( $1=="'$Name'" && $NF=="N")print $0}' $FILE |wc -l)

Code:

count=$(awk -F "|" '!/^#/ && !/^$/ {if( $1=="'$Name'" && $4=="'Place'" $NF=="N")print $0}' $FILE |wc -l)

Code:

count=$(awk -F "|" '!/^#/ && !/^$/ {if( $1=="'$Name'" && $4=="'Place'" && $5=="'$Date'" && $NF=="N")print $0}' $FILE |wc -l)

So loop over all fields and only when all true, proceed

WOW!

you guys are a thing of beauty to watch

InFile1 ...

Code:

four score and seven years ago our fathers brought forth

on this continent a new nation conceived in liberty

and dedicated to the proposition that all men are created equal

now we are engaged in a great civil war testing whether that nation

or any nation so conceived and so dedicated can long endure

InFile2 ...

Code:

6 nation 3 continent

4 engaged 7 civil

1 four

Search InFile1 for all lines which satisfy any of the criteria specified in InFile2.
They are:

Code:

- all lines where the 6th word is nation and the 3rd word is continent

- all lines where the 4th word is engaged and the 7th word is civil

- all lines where the 1st word is four

This awk ...

Code:

awk '{if (FNR==NR) for (i=1;i<=NF;i++) crit[NR,i]=$i

      else for (j=1;j<=NR;j++) {hits=0; misses=0; k=-1;

        while (crit[j,k=k+2]!="") 

          if (crit[j,k+1]!=$crit[j,k]) {misses++; break}

          else {$crit[j,k]=toupper($crit[j,k]); hits++}

      if (hits>0&&misses==0) print}}' $InFile2 $InFile1 >$OutFile

... produced this OutFile ...

Code:

FOUR score and seven years ago our fathers brought forth

on this CONTINENT a new NATION conceived in liberty

Daniel B. Martin

Nice work Daniel. Here is another take using your data:

Code:

awk 'NR==FNR{for(i=1;i<NF;i+=2)arr[NR][$i]=$(i+1)}

          {for(j=1;j <= length(arr);j++){

            for(k in arr[j])

              if( $k == arr[j][k] )a++;

            if(a == length(arr[j]))print;

            a=0

          }}' f2 f1