1

I am facing an issue where I am using awk to find a line and print two lines above

Inside the file test.txt file is below"

{ TABLE "sacro".issue row size = 149 number of columns = 9 index size = 0 }

{ unload file name = issue00106.unl number of rows = 74 }

create table "sacro".issue
( 
issue_no serial not null constraint "sacro".nnc_issue00,
user_logged varchar(8,1) not null constraint "sacro".nnc_issue01,
issue_status_code integer not null constraint "sacro".nnc_issue02,
issue_cat_code varchar(2) not null constraint "sacro".nnc_issue03,
issue_descr text not null constraint "sacro".nnc_issue04,
issue_feedback text,
user_action varchar(8,1),
date_logged date not null constraint "sacro".nnc_issue05,
date_compl date
);

revoke all on "sacro".issue from "public" as "sacro";

{ TABLE "sacro".issue_category row size = 104 number of columns = 2 index                        size = 8 }

{ unload file name = issue00107.unl number of rows = 5 }

create table "sacro".issue_category
(
issue_cat_code varchar(2) not null constraint "sacro".nnc_iss_cat00,
issue_cat_descr varchar(100),
primary key (issue_cat_code)  constraint "sacro".pkc_issuecategory
);

revoke all on "sacro".issue_category from "public" as "sacro";

{ TABLE "sacro".issue row size = 149 number of columns = 9 index size = 0 }

{ unload file name = issue00106.unl number of rows = 74 }

The line I am searching for is below:

create table "sacro".issue

The command I am running is:

cat test.txt | awk '{a[NR]=$0} $0~s {f=NR} END {for (i=f-B;i<=f+A;i++) print a[i]}' B=2 A=0 s='create table \"sacro\".issue'

When I run the command it finds the line create table "sacro".issue_category. Is there a way to grep for the line I need as if I was using grep -w. Unfortunately the below grep command does not work on HPUX:

cat test.txt | grep -B 2 -w "create table \"sacro\".issue" | head -1

My desired outcome should be (using head -1):

{ unload file name = issue00106.unl number of rows = 74 }
1
sed -e '
   1N                                  # get 2nd line in pattern space
   $!N                                 # get 3rd line in pattern space
   /\ncreate table "sacro"\.issue$/P   # if the last portion of matches, show 1st portion
   D                                   # strip 1st portion & reapply sed code on what
                                       # remains of the pattern space
' test.txt

grep -B 2 'create table "sacro"\.issue$' test.txt | sed q
0

First of all, there's no reason why grep can't do this. Oh, and note that you don't need cat, both grep and awk can take input file names:

$ grep -xB2 'create table "sacro".issue' file
{ unload file name = issue00106.unl number of rows = 74 }

create table "sacro".issue

So, if you just want the first line there, do:

$ grep -xB2 'create table "sacro".issue' file | head -n1
{ unload file name = issue00106.unl number of rows = 74 }

The -x means "match the entire line" which ensures that the create table "sacro".issue_category line isn't printed. The -B2 means "print the 2 previous lines as well.


You can also do it in awk as requested:

$ awk -vs='create table "sacro".issue' '($0==s){print b2}{b2=b1; b1=$0}' file 
{ unload file name = issue00106.unl number of rows = 74 }
  • This awk seems to work, but not with a file that has plenty table structures – Christopher Karsten May 3 '17 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.