I have 2 different templates of sql's in a single file. From each sql I am trying to extract different segments of the sql's.
Please note that the number of fields and conditions would vary based on the sql. It might not adhere to a single format as below(but will adhere to allowable sql formats).
Below is the sample sql from which I have to identify the following
Code:
macroname |REPLACE MACRO macroname
AS
(
/* Comments */
UPDATE tgt.table targ
FROM
(
SELECT
tmp.col1
,tmp.col2
,tmp.col3
FROM src.table tmp
INNER JOIN tgt.table tgt
ON tmp.key1 = tgt.key1 AND
tmp.key2 = tgt.key2
AND tmp.key3 =
tgt.key3
AND
tgt.dt = '2899-12-31'
CROSS JOIN ctltable tdc
--some comments
WHERE
col1 =
col1
col2=col2
col3=
col3
) innertab
SET dt = currentdate
WHERE targ.col1 = innertab.col1 AND
targ.col2 = innertab.col2 AND
targ.col3 = innertab.col3 AND
targ.dt = '2899-12-31'
;
);
macro2 |REPLACE MACRO macro2
AS
(
/*########################################################################*/
/* */
/*########################################################################*/
INSERT INTO tgt.table tab
(
col1,
col2
,col3
,col4
,col5
,col6
)
SELECT
col1
,col2
,col3
,col4
,col5
,col6
FROM
stg.table stg
LEFT OUTER JOIN
tgt.table tgt
ON ( ( stg.col1 = tgt.col1 )
AND (stg.col2 = tgt.col2 ) )
AND tgt.col3 = '2899-12-31'
CROSS JOIN
ctltable tdc
WHERE
( tab.col1 IS NULL
AND tab.col2 IS NULL
);
);
1. whether the sql is an update or an insert query
2. from update query I have to identify the inner query and validate if the query is not having any derivations or any additional joins from the one's given. Basically the joins should always be between 2 tables stg or temp table with the target table only. the target table is identified from the outermost insert or update statement. And stg or temp is identified from the schema names like stg.
3. I have to extract each of the segments from the sql. like insert, update, sub queries, where, set.
can you please help me finding a regular expression to identify the sql's which does not comply to my above definition.
Last edited by hitmansilentass; 09-22-2013 at 11:38 PM..
Reason: CODE tags not QUOTE tags
Well, the sql parser I am not able to understand much with the java code or the instructions. However, I found this link but couldn't implement it as it is time consuming and I am running short of time. So, keeping it for the later tasks. However, I have to get this validation first so I was looking for a sed or awk expressions to validate it.
You may get some traction removing comments and replacing all white space with single spaces, but unless you can get a proper SQL parser you are still likely to be plagued by false positives/negatives.
Perhaps if you get together some plain English pusdo code like:
Code:
"JOIN" must always be followed with <whitespace> ( "tgt" or "ctltable") and then ("ON" or "WHERE")
Someone here could help you with a basic validator.
Well, my current plan is to flag for manual intervention if there is something that is not adhering to the standard templates. I have thousands of sql's with most of the with the standard structure. Currently it requires manual analysis to categorize them, if it can be validated then I could segregate only those which requires manual intervention to process.
Hi,
I need some help.
My task is, to write a "one-line" command, which must use ls and awk.
Task: Write a command-line, which should rename all files in dir from form "value1.dok" to "value2.doc". And value2=value1+1.
For example:
ls | awk -F: '{print "mv "$0" "$1+1".doc"}' | sh
But... (3 Replies)
Hi ,
I am trying to write a mod_header module rule which will look a specific url (https://partner.testing.com) and rewrite it. The header line is given below. where the url comes in between of the line. i know ^ expression can be used for match the beginning of the line. but not sure how to... (3 Replies)
Hello All,
I'm trying to extract the lines between two consecutive elements of an array from a file.
My array looks like:
problem_arr=(PRS111 PRS213 PRS234)
j=0
while } ]
do
k=`expr $j + 1`
sed -n "/${problem_arr}/,/${problem_arr}/p" problemid.txt
---some operation goes... (11 Replies)
Hi all,
How am I read a file, find the match regular expression and overwrite to the same files.
open DESTINATION_FILE, "<tmptravl.dat" or die "tmptravl.dat";
open NEW_DESTINATION_FILE, ">new_tmptravl.dat" or die "new_tmptravl.dat";
while (<DESTINATION_FILE>)
{
# print... (1 Reply)
CA_RELEASE has a value of 6. I need to check if that this is a numeric value. if not error.
source $CA_VERSION_DATA
if * ]
then
echo "CA_RELESE $CA_RELEASE is invalid"
exit -1
fi
+ source /etc/ncgl/ca_version_data
++ CA_PRODUCT_ID=samxts
++ CA_RELEASE=6
++ CA_WEEK_NO=7
++... (3 Replies)
Hi,
I am new to scripting.
please help me in validating the user entered time Pattern
Here is the program
#!/bin/bash
validateTimeFormat()
{
checkTime=$1
timePattern="::"
if ]
then
echo "Valid time pattern"
return 1
else
echo "InValid time pattern"
return -1
fi
}
echo "Please... (2 Replies)
Hi,
i am java guy and new to unix.
I want to validate date pattern using Regex expression
here is the sample program i have written.
#!/bin/sh
checkDate="2010-04-09"
regex="\\d{4}-\\d{2}-\\d{2}\$"
echo $regex
if ]
then
echo "OK"
else
echo "not OK"
fi
But the ouput is... (2 Replies)
Hi there
i have a script which will create unix user accounts. Id like to validate the entered string so that it is specifically 8 characters or less and consists of only ! not
Is there a way to validate a string against a regular expression.. i.e
size=`printf "$var | wc -m`
... (1 Reply)
Is it possible to combine a regular expression with a aritmetical expression? For example, taking a 8-numbers caracter sequece and casting each output of a grep, comparing to a constant.
THX! (2 Replies)
09-22-2013
