multiple lines --> into single line using awk - How ?

[beowulfkid]

Gurus,
I have my input file as below:

1 2 3 4 5 lock table xyz.
test in exclusive mode

10 3 4 5 7 insert into table xyz.
test values
(2,3,4)

As you see, the record seperator is just not space.. it can be space or multiple spaces.

I want to be able to treat "lock table ....." and "insert into table ..." as a single field and print using awk or sed.

for example, if i say awk '{print $6}', i should get :

lock table xyz.test in exclusive mode
insert into table xyz.test values (2,3,4)

awk or sed does not matter to me as long as it works!!
Thanks a bunch.

[Azrael2]

Gurus,
I have my input file as below:

1 2 3 4 5 lock table xyz.
test in exclusive mode

10 3 4 5 7 insert into table xyz.
test values
(2,3,4)

As you see, the record seperator is just not space.. it can be space or multiple spaces.

I want to be able to treat "lock table ....." and "insert into table ..." as a single field and print using awk or sed.

for example, if i say awk '{print $6}', i should get :

lock table xyz.test in exclusive mode
insert into table xyz.test values (2,3,4)

awk or sed does not matter to me as long as it works!!
Thanks a bunch.

I think I know what you mean... and 1 2 3 4 5 are just random strings before that? Is it always the same number of strings? Would it be possible for you to post more of your script? What you could use is:

if (index($0, "lock table") > 0 ) {
if ($1=="table") {
$2=tablevar;
}
if ($2=="table") {
$3=tablevar;
}
etc
print ("lock table " tablevar);
}


But it could be much simpler depending on your source

[ghostdog74]

snippet

awk 'BEGIN{RS=""}
{
  for(i=6;i<=NF;i++) printf $(i)" "
  print ""
}
' file

output:

# ./test.sh
lock table xyz. test in exclusive mode
insert into table xyz. test values (2,3,4)

you do the rest

[beowulfkid]

Here is my real data:
I should have posted the real data in my original post:

Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x07800000234E7EA0 0 2 1 1 1 0 DELETE FROM "PZGR2TP
"."TASK_TYPE"
0x07800000235AB340 0 3 1 1 1 0 DELETE FROM "PZGR2TP
"."USER_OLD_PWDS"
0x078000002323DF80 0 9 1 1 1 0 DELETE FROM
"UZGR0TST"."MOTIVATOR_EXTRACT"
0x07800000238A3880 0 4 1 1 1 0 SELECT * FROM "PZGR2TP
"."CONTACT_RELATION"
0x0780000023421060 0 1 1 1 2 1 LOCK TABLE "PZGR2TP
"."PROSPECT_EXTENSION" IN EXCLUSIVE MODE
0x0780000023420E00 0 8 1 1 2 1 LOCK TABLE "PZGR2TP
"."PROD_TYPE_SCRIPT_LINK" IN EXCLUSIVE MODE
0x0780000023ADE6C0 0 6 1 1 1 3 INSERT INTO "PZGR2TP
"."FILTERED_ROLE" ("VIEW_ID","ROLE_ID") VALUES (CAST (? AS DEC(31, 0)) ,CAST (? AS DEC(10, 0))
)
0x07800000239A6FE0 0 7 1 1 1 1 with tinfo(tname, tschema)
as(values (TABLE_NAME('CAMPAIGN_EXTENSION','PZGR2TP '),TABLE_SCHEMA('CAMPAIGN_EXTENSION','PZGR2TP '))) SELECT FID,TYPE FROM
SYSIBM.SYSTABLES, tinfo where NAME=tname AND CREATOR=tschema
0x0780000023ADB960 0 5 1 1 1 1 with tinfo(tname, tschema) as
(values (TABLE_NAME('FILTERED_ROLE','PZGR2TP '),TABLE_SCHEMA('FILTERED_ROLE','PZGR2TP '))) SELECT NAME, IDENTITY, GENERATED
FROM SYSIBM.SYSCOLUMNS, tinfo where TBNAME=tname AND TBCREATOR=tschema AND (GENERATED = 'A' OR IDENTITY = 'Y')

Based on a search condition (when $2 == watever && $3 == watevr), I should be able to extract my sql in the "Text" column ($ in a single line. As you see, "Text" is in multiple lines and not in a single line also, my field seperator is not just a space ..it can be combination of spaces.. Please help as it is urgent !!

[Azrael2]

Try something like this:

BEGIN {
count=0;
temp="ERROR";
var="ERROR";
}
{
if ($8=="DELETE") {
nextline=1;
temp=($8,$9,$10);
}
if (nextline=1) {
var[count]=temp,$0;
nextline=0;
count++;
}

if ($8=="SELECT") {
nextline=1;
temp=($8,$9,$10);
}
if (nextline=1) {
var[count]=temp,$0;
nextline=0;
count++;
}
}
END
{
print var1;
print var2;
print var3;
print var4;
print var5;
print var6;
print var7;
}



I'm not too good with arrays, so there might be a better way of doing this. Basically, it says "if string 8 is select or delete, store it in temp, and make a note to read the next line.
When the next line is read, store temp, and then the whole line of the current string as var1. Then increase the counter for the next variable in the "array" and carry on.

[Azrael2]

oh, also, the square brackets around the count thing might be wrong. You or someone else should be able to correct it, but I don't have time I'm afraid