Help with formatting

Hi, I am new to UNIX and need your help in formatting the below input command to the desire output

Input:

CREATE UNIQUE INDEX XPKTABLE1
 (
                       COL1,
                       COL2
 ) ON TABLE_NM;

Output:

COMMENT ON TABLE DB_NM.TABLE_NM AS 'PK=,COL1,COL2; ';

In the input above, there are two tab spaces before COL1 and COL2.
Appreciate your inputs.

Thanks,
V

Is this a homework assignment?

It is awfully hard to tell from your sample data what the expected input file format is.

1. Are there always exactly 5 lines in your input file?
2. Are there always two elements to be placed in the "PK=" list in the output?
3. Why is there a "," after the "PK=" in the output before "COL1"?
4. You say: "there are two tab spaces before COL1 and COL2". What is a "tab space"? There aren't any tabs in your sample data. Is a "tab space" 11.5 space characters???
5. Could "COL1" appear on the same line as the "("?
6. Could "COL1,COL2" appear on the same line?
7. Could there be a comma or a semicolon after "COL2" in your input file?
8. What OS and shell are you using?

Don,
Please find my answers here, I could have been more specific earlier. I am working on automating few scripts and as part of that, I need to have this.

1. No there could be less or more depending on columns between brackets. In this example, I have 2 columns and so 5 lines. If there are 5 columns, then there will be 8 lines. Line 1, Line 2 and last line will remain same except the table name in last line
2. No, the number of elements changes depends on the number of columns(COL1, COL2, COL3.....)
3. ',' is expected in the output
4. It is tab key. In the sample data, it didn't allow me to put tab key, so I used spaces. There are two tabs before COL1, COL2
5. No, COL1 will always appear in line 3
6. No, COL1, COL2(and even COL3 if there is one) will always be in separate lines
7. Yes there will be a comma if COL3 exists and so on. The last column(COLx) will not have anything
8. I am using ksh in linux

Thanks,
V

Hello, you can try:

awk '/CREATE UNIQUE INDEX/,/)/ {
 if(/CREATE UNIQUE INDEX|\(/) {
  next
 }
 if (!/\)/){
  A=A $NF
 }
 else{
  X=$NF
  gsub(/TABLE_|;/,"",X)
  print "COMMENT ON TABLE DB_" X ".TABLE_" X " AS \047PK=," A "; \047;"
 }
}' file.txt

It work fine with your input example...

Regards.

Thank you disedorgue. It worked the way I wanted.

But one last question. How can I pass a variable for DB_NM instead of hard coding every time?

I tried to set something like this before awk and below is the output.

Input:

export TBL_DB="DATABASE";

awk '/CREATE UNIQUE INDEX/,/)/ {
if(/CREATE UNIQUE INDEX|\(/) {
  next
}
if (!/\)/){
  A=A $NF
}
else{
  X=$NF
  gsub(/TABLE_|;/,"",X)
  print "COMMENT ON TABLE ${TBL_DB}." X " AS \047PK=," A "; \047;"
}
}' input_index.txt > cmnt1.txt

and the output I am getting is

COMMENT ON TABLE ${TBL_DB}.TABLE_NM AS 'PK=,COL1,COL2; ';

But I want the output to be something like this

COMMENT ON TABLE DATABASE.TABLE_NM AS 'PK=,COL1,COL2; ';

Appreciate your help.

Thanks,
V

As (I simplified many part code):

TBL_DB="DATABASE"
awk -v VAL_DB="${TBL_DB}" '/CREATE UNIQUE INDEX/,/)/ {
if(/CREATE UNIQUE INDEX|\(/) {
  next
}
if (!/\)/){
  A=A $NF
}
else{
  X=$NF
  sub(/;/,"",X)
  print "COMMENT ON TABLE "VAL_DB"." X " AS \047PK=," A "; \047;"
}
}' input_index.txt > cmnt1.txt

Regards.

Thank you so much. You rock!!

Hello disedorgue, one last question...

For the same input, if I want multiple comments one with (COL1, COL2) and also the same comment for individual columns, how can I do it.

For example, for the same input, I want my output like this.

COMMENT ON TABLE DATABASE.TABLE_NM AS 'PK=,COL1,COL2; ';
COMMENT ON TABLE DATABASE.TABLE_NM AS 'PK=,COL1; ';
COMMENT ON TABLE DATABASE.TABLE_NM AS 'PK=,COL2; ';

Thanks,
V

As:

TBL_DB="DATABASE"
awk -v VAL_DB="${TBL_DB}" '/CREATE UNIQUE INDEX/,/)/ {
if(/CREATE UNIQUE INDEX|\(/) {
  next
}
if (!/\)/){
  A=A $NF
}
else{
  X=$NF
  sub(/;/,"",X)
  print "COMMENT ON TABLE "VAL_DB"." X " AS \047PK=," A "; \047;"
  split(A,B,",")
  for (i in B){
   print "COMMENT ON TABLE "VAL_DB"." X " AS \047PK=," B "; \047;"
  }
}
}' input_index.txt > cmnt1.txt

Regards.

Hello Disedorgue, I tried the similar approach for one other script and couldn't able to get the desired result. Could you please check this.

Input:

CREATE  TABLE TBL_NM
(

        COL1               DECIMAL(18,0) NOT NULL,
        COL2               CHAR(2) NOT NULL ,
        COL3               CHAR(5) NOT NULL ,
        COL4               SMALLINT NOT NULL
);

Desired Output:

REPLACE VIEW DB_NM.TBL_NM 
AS
COL1,
COL2,
COL3,
COL4
FROM DBASE.TBL_NM;

Please note in the output, DB_NM and DBASE are both different databases.
Also, in the input, there is a blank line after '('.

Thanks,
V

Hi,
Approach is different because the problem is different:

DBASE="FOO"
DB_NM="BAR"
awk -v VAR1="${DB_NM}" -v VAR2="${DBASE}" '/CREATE *TABLE/,/);/ {
 switch ($0) {
  case /CREATE *TABLE/:
   X=$3
   print "REPLACE VIEW "VAR1"."$3
   break
  case /^\(/:
   print "AS"
   break
  case /^$/:
   break
  case /^\);/:
   print "FROM "VAR2"."X";"
   break
  default:
   gsub(/[^,]/,"",$NF)
   print $1$NF
 }
}' file.txt

Regards.

Hello Disedorgue,
I am getting the following error while I run the above statements

awk: cmd. line:2:  switch ($0) {
awk: cmd. line:2:              ^ syntax error
awk: cmd. line:4:   case /CREATE *TABLE/:
awk: cmd. line:4:                       ^ syntax error
awk: cmd. line:7:   case /^\(/:
awk: cmd. line:7:         ^ syntax error
awk: cmd. line:7:   case /^\(/:
awk: cmd. line:7:          ^ backslash not last character on line

Thanks,
V

Hmmm, it works fine at home, but i use gawk.
So, here an alternative without "switch...case":

DBASE="FOO"
DB_NM="BAR"
awk -v VAR1="${DB_NM}" -v VAR2="${DBASE}" '/CREATE *TABLE/,/);/ {
  if (/CREATE *TABLE/){
   X=$3
   print "REPLACE VIEW "VAR1"."$3
   next
  }
  if (/^\(/){
   print "AS"
   next
  }
  if (/^$/){
   next
  }
  if (/^\);/){
   print "FROM "VAR2"."X";"
   next
  }
   gsub(/[^,]/,"",$NF)
   print $1$NF
}' file.txt

Regards.

Thank you so much disedorgue.

Hello Disedorgue,
In the similar case above, I need to write the output differently if I find a combination of columns. If the input table has a combination of columns(foo & boo), then the output has to be written differently if not just as above.

Here is the sample input and desired output.

CREATE  TABLE TBL_NM
(

        COL1               DECIMAL(18,0) NOT NULL,
        COL2               CHAR(2) NOT NULL ,
        foo                  CHAR(5) NOT NULL ,
        boo                 SMALLINT NOT NULL,
        COL3               SMALLINT
);

Desired Output:

REPLACE VIEW DB_NM.TBL_NM_special 
AS
COL1,
COL2,
dump.date as foo,
COL3
FROM DBASE.TBL_NM ss
JOIN DBASE.TBL2 dump
ON ss.foo = dump.foo;

As you see in the output, "boo" should not be displayed in this case. If these columns combination doesn't exist, then it should be like below

REPLACE VIEW DB_NM.TBL_NM 
AS
COL1,
COL2,
COL3
FROM DBASE.TBL_NM;

Could you please explain how I could do this.

Thanks,
V