As part of automating the sql generation, I have the source table name, target table name, join condition stored in a file join_conditions.txt which is a delimited file (I can edit the file if for any reason). The reason I needed to store is I have built SELECT list without FROM clause according to source and target tables taken from another file and stored it in an array and now I need to build the complete sql query of select list and the from list;
Code:
Src_tbl Tgt_tbl join condition
Src_1 @Tgt_1 @FROM ( SELECT SRC_1.* FROM SRC_1 INNER JOIN SRC_TEMP_1 ON
SRC_1.COL1=SRC_TEMP_1.COL1) SRC JOIN (SELECT TGT_1.* FROM TGT_1
INNER JOIN TGT_TEMP_1 ON TGT_1.COL1=TGT_TEMP_1.COL1) TGT ON
SRC.COL1=TGT.COL1;
Src_2 @Tgt_2 ...................
Please note that I have aligned the 3rd column LPAD with spaces for better view; the provided join condition column can span multiple lines;
Similarly there are corresponding join conditions for other source and target tables in the file which is not mentioned here.
Code:
awk -F "@" 'BEGIN{RS=";"}
{
join_cond=$3
a[$1 OFS $2]=join_cond
}
END {
for (i in a) {
print a[i]
}
}' < join_conditions.txt > sql_query.txt
Moderator's Comments:
Please use code tags as required by forum rules!
Expected assignment:
Code:
FROM ( SELECT SRC_1.* FROM SRC_1 INNER JOIN SRC_TEMP_1 ON
SRC_1.COL1=SRC_TEMP_1.COL1) SRC JOIN (SELECT TGT_1.* FROM TGT_1
INNER JOIN TGT_TEMP_1 ON TGT_1.COL1=TGT_TEMP_1.COL1) TGT ON
SRC.COL1=TGT.COL1;
Current assignment:
Code:
FROM ( SELECT SRC_1.* FROM SRC_1 INNER JOIN SRC_TEMP_1 ON
Even though I specified the RS=; I think it is still considering the \n as record separator; How can I assign the multi-line content present in
the join_conditions.txt to awk variable jon_cond for frame the sql query; I am struck up with this as I have no clue on how to proceed;
I can break the file manually to contain the join_condition with source and target repeating multiple times as
Code:
Src_tbl1 Tgt_tbl1 join condition
Src_1 Tgt_1 FROM ( SELECT SRC_1.* FROM SRC_1 INNER JOIN
Src_1 Tgt_1 SRC_TEMP_1 ON SRC_1.COL1=SRC_TEMP_1.COL1) SRC
Src_1 Tgt_1 JOIN (SELECT TGT_1.* FROM TGT_1 INNER JOIN TGT_TEMP_1 ON
Src_1 Tgt_1 TGT_1.COL1=TGT_TEMP_1.COL1) TGT ON SRC.COL1=TGT.COL1;
and then carry out the required operation. But I assume that there is more elegant approach
Many thanks for your time..
---------- Post updated at 11:27 AM ---------- Previous update was at 10:52 AM ----------
Hi RavinderSingh,
I have a input delimited file whose layout is
Code:
Src_tbl@Tgt_tbl@join_condition
Src_1@Tgt_1@FROM ( SELECT SRC_1.* FROM SRC_1 INNER JOIN SRC_TEMP_1 ON SRC_1.COL1=SRC_TEMP_1.COL1) SRC JOIN (SELECT TGT_1.* FROM TGT_1 INNER JOIN TGT_TEMP_1 ON TGT_1.COL1=TGT_TEMP_1.COL1) TGT ON SRC.COL1=TGT.COL1;
The data in join_condition can span multiple lines here.
I need to get the data in the join_condition column and assign it to an array for further operations;
FROM ( SELECT SRC_1.* FROM SRC_1 INNER JOIN SRC_TEMP_1 ON SRC_1.COL1=SRC_TEMP_1.COL1) SRC JOIN (SELECT TGT_1.* FROM TGT_1 INNER JOIN TGT_TEMP_1 ON TGT_1.COL1=TGT_TEMP_1.COL1) TGT ON SRC.COL1=TGT.COL1;
Output what I am seeing:
Code:
FROM ( SELECT SRC_1.* FROM SRC_1 INNER JOIN
How to get the expected output here please.
Last edited by RudiC; 04-19-2016 at 01:48 AM..
Reason: Added tags around CODE (while it was around data...)
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)
I have a set of files of multi-line records with the records separated by a blank line. I needed to add a record number to the front of each line followed by a colon and did the following:
awk 'BEGIN {FS = "\n"; RS = ""}{for (i=1; i<=NF; i++)print NR,":",$i}' ~/Desktop/data98-1-25.txt >... (3 Replies)
Hi all,
I want to search for a data type in a line.For this in a loop i am checking for $DATA_TYPE in a line using grep.But grep is not able to find when i give this.
Can any one tell me how to check string in $DATA_TYPE variable in line usign grep (or) any other way to do the above task.
... (4 Replies)
Hi.
I am reasonably new to awk, but have done quite a lot of unix scripting in the past. I have resolved the issues below with unix scripting but it runs like a dog. Moved to awk for speed and functionality but running up a big learning curve in a hurry, so hope there is some help here.
I... (6 Replies)
Hey there,
I have a table of contents file of the form
1 Title1
1.1 Subtitle1
1.1.1 Subsubtitle1
1.1.2 Subsubtitle2
...
and want to count the number of dots in the first field to find out the level of the section.
I use the gsub function for the job, which works if I pass the pattern... (2 Replies)
I am trying to write a script that will help me put a file into excel with little manipulation. Below is a sample of the file im using.
Group1:*:gid1:user,user
Group2:*:gid2:user,user
Group3:*:gid3:user,user,user,user,user,user,user
Group4:*:gid4:user,user
I marked in red the part that is... (1 Reply)
I want something that would show up basically like:
Menu
-----
1) Option 1
2) Option 2
3) Option 3
Pick one:
I tried menu = " Menu \r\n ----- \r\n 1)Option 1 \r\n..............etc etc etc"
but that didnt work (just got the whole menu one one line, with the... (2 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)
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)
The bash bash below extracts the oldest folder from a directory and stores it in filename
That result will match a line in bold in input. In the matching line there is an_xxx digit in italics that
(once the leading zero is removed) will match a line in link. That is the lint to print in output.... (2 Replies)
04-18-2016
