awk - 2 files comparison without for loop - multi-line issue

Shell Programming and Scripting
1

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:

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)

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

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

awk -F '^'   'NR==FNR { mydate[$1]=$2; next} {for (i in mydate) if ($0=i) { print mydate } else {print $0}} < dir/mydate.txt dir/myview.txt > dir/mynewview.txt

This is giving undesired results as below:

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

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.

2 
awk -F '^'   'NR==FNR { mydate[$1]=$2; next} {($0 in mydate) ? { print mydate } :  {print $0}} < dir/mydate.txt dir/myview.txt > dir/mynewview.txt

Just to update the answer, the above one worked.

3

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.