awk - 2 files comparison without for loop - multi-line issue
Greetings Experts,
I need to handle the views created over monthly retention tables
for which every new table in YYYYMMDD format, there is
equivalent view created and the older table which might be
dropped, the view over it has to be re-created over a dummy
table so that it doesn't fail. eg:
Code:
CREATE OR REPLACE VIEW VIEW_TABLEA_20150731 AS
SELECT COL1, '31-JUL-2015' AS BIZ_DATE,
(CASE WHEN COL3 BETWEEN '01-JUL-2015' AND '31-JUL-2015' THEN COL3*2 ELSE COL3 END) AS DRVD_COL3,
'20150630' AS PREV_DATE
FROM DB_ABC.TABLEA_20150731;
If I pass the view name and the month eg:
VIEW_TABLEA_ 20150831 as parameters, it should generate the view
script accordingly.
I am spooling 2 files from sqlplus with following data. (myview.txt has
the info. relative to the view where the values that are deemed
to changed from view to view are recognized with some specific
text values like CURRENT_YYYYMMDD, CURRENT_FIRST_DAY etc. and separated accordingly)
dir/myview.txt (please note that there can be multiple lines {for
visual, used smileys which indicates continuous lines} that are
to be passed as single line to awk (stored in multiple lines in a
single column of a table); not sure how to do this)
Code:
CREATE OR REPLACE VIEW VIEW_TABLEA_
20150731
AS :cool:
SELECT COL1, '
31-JUL-2015
' AS BIZ_DATE,:cool:
(CASE WHEN COL3 BETWEEN '
01-JUL-2015
' AND '
31-JUL-2015
' THEN COL3*2 ELSE COL3 END) AS DRVD_COL3,'
20150630
' AS PREV_DATE:cool:
FROM DB_ABC.TABLEA_
20150731;
2nd file: dir/mydate.txt
Code:
DATE_PARAM_TBL with 2 columns; data as following (during select the following results are retrieved for 20150831 data with '^' delimiter)
col1 col2
CURRENT_YYYYMMDD 20150831
PREVIOUS_YYYYMMDD 20150731
CURRENT_FIRST_DAY 01-AUG-2015
CURRENT_LAST_DAY 31-AUG-2015
PREVIOUS_FIRST_DAY 01-JUL-2015
PREVIOUS_LAST_DAY 31-JUL-2015
I am trying to learn AWK through forums and could not handle this.
As I cannot copy/paste what I have tried on other machine, typing
the entire content which might not be according to syntax which
did work on other machine. All I have done is
Code:
awk -F '^' 'NR==FNR { mydate[$1]=$2; next} {for (i in mydate) if ($0=i) { print mydate[i] } else {print $0}} < dir/mydate.txt dir/myview.txt > dir/mynewview.txt
This is giving undesired results as below:
Code:
CREATE OR REPLACE VIEW VIEW_TABLEA_
CREATE OR REPLACE VIEW VIEW_TABLEA_
CREATE OR REPLACE VIEW VIEW_TABLEA_
CREATE OR REPLACE VIEW VIEW_TABLEA_
CREATE OR REPLACE VIEW VIEW_TABLEA_
CREATE OR REPLACE VIEW VIEW_TABLEA_
20150831
CURRENT_YYYYMMDD
CURRENT_YYYYMMDD
CURRENT_YYYYMMDD
CURRENT_YYYYMMDD
CURRENT_YYYYMMDD
......
i.e., it is printing 6 times for each line and only one occurrence of
the exact value in file2 is being replaced with file1 values.
Desired output is
Code:
CREATE OR REPLACE VIEW VIEW_TABLEA_20150831 AS
SELECT COL1, '31-AUG-2015' AS BIZ_DATE,
(CASE WHEN COL3 BETWEEN '01-AUG-2015' AND '31-AUG-2015' THEN COL3*2 ELSE COL3 END) AS DRVD_COL3
'20150731' AS PREV_DATE
FROM
DB_ABC.TABLEA_20150831;
Note: for some of the $0 in the second file are spanned to multi-lines. Also, how to handle this multi-lines.
I'm very glad that this is working for you, but I don't understand how it is producing any output. Since you are redirecting standard input from one file and are naming the second file as an operand, awk should not be reading anything from standard input. And, since there is only one operand, there is no second input file to be processed to print anything. And, if there was, and you find a line that matched something in your array, the array index (i) is undefined.
You say that dir/mydate.txt uses circumflex as a field separator, but (other than your awk -F option arguments) I don't see any circumflex characters in any of your files.
I want to read the log file which was generate from other command . And the output was having multi line in log files for job name and server name. But i need to make all the logs on one line
Source file
07/15/2018 17:02:00 TRANSLOG_1700 Server0005_SQL ... (2 Replies)
Greetings experts,
Have 2 input files, of which 1 file has 1 record per line; in 2nd file, multiple lines constitute 1 record; Hence declared the RS=";"
Now in the first file which ends with ";" at each line of the line; But \nis also being considered as part of the data due to which
I am... (1 Reply)
Greetings Experts,
I do have some basic knowledge of Unix. The task I am trying to do through shell script is to generate the view script for all of the tables which is in YYYYMMDD format (I assume I am on Ksh).
I have certain tables that ends in YYYYMMDD format (eg: tbl_20150630)
For each... (1 Reply)
Can someone please help me with a unix logic for below. I tried to get the desired output by using change capture condition in Datastage but its not working properly. i have two files file1, file2 as below.
file1
ROW_NO VEND_NO CODE AIR_D OCEAN_D
----------------------------------------
1 ... (3 Replies)
Hello,
I was wondering if anyone knows a faster way to search and compare strings and dates from 2 files?
I'm currently using "for loop" but seems sluggish as i have to cycle through 10 directories with 10 files each containing thousands of lines.
Given:
-10 directories
-10 files... (4 Replies)
I have a file with data records separated by multiple equals signs, as below.
==========
RECORD 1
==========
RECORD 2
DATA LINE
==========
RECORD 3
==========
RECORD 4
DATA LINE
==========
RECORD 5
DATA LINE
==========
I need to filter out all data from this file where the... (2 Replies)
Hi,
I'm trying to run the following command using sh -c
ie
sh -c "while read EachLine
do
rm -f $EachLine ;
done < file_list.lst;"
It doesn't seem to do anything.
When I run this at the command line, it does remove the files contained in the list so i know the command works
ie... (4 Replies)
I have two files which I would like to compare and then manipulate in a way.
File1:
pictures.txt 1.1 1.3
dance.txt 1.2 1.4
treehouse.txt 1.3 1.5
File2:
pictures.txt 1.5 ref2313 1.4 ref2345 1.3 ref5432 1.2 ref4244
dance.txt 1.6 ref2342 1.5 ref2352 1.4 ref0695 1.3 ref5738 1.2... (1 Reply)
Another frustrating scripting problem from a biologist trying to manipulate a file with several millions line. For each of the line I need to compare the uppercase A or C or G or T with the lowercase a or c or g or t. If there are more uppercases, a + should be added to a new column, otherwise a -... (10 Replies)
I am an Awk newbie and cannot wrap my brain around my problem:
Given multi-line records of varying lengths separated by a blank line I need to skip the first two lines
of every record and extract every-other line in each record unless the first line of the record has the word "(CONT)" in the... (10 Replies)
