How to extract WHERE clause from a SQL using UNIX???

Login or Register to Ask a Question and Join Our Community

How to extract WHERE clause from a SQL using UNIX???

Tags

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to extract WHERE clause from a SQL using UNIX???

01-12-2011

Registered User

59, 0

Join Date: Sep 2010

Last Activity: 3 March 2011, 11:32 AM EST

Posts: 59

Thanks Given: 0

Thanked 0 Times in 0 Posts

How to extract WHERE clause from a SQL using UNIX???

Hi,

I need to know how to extract entire WHERE clause from a SQL statement using UNIX command.

For example:
If following is the SQL then how to extract entire WHERE clause:

Code:

UPDATE <TABLE_NAME_1>  SET  <FIELD_NAME> = VALUE  WHERE  CONDITION_1 AND  CONDITION_2 = (SELECT FIELD_NAME FROM TABLE_NAME_2 WHERE CONDITION_XYZ);
                                    or
UPDATE <TABLE_NAME_1>  SET  <FIELD_NAME> = VALUE  WHERE  CONDITION_1 AND  CONDITION_2 = (SELECT FIELD_NAME FROM TABLE_NAME_2 WHERE CONDITION_XYZ and CONDITION_A = (SELECT FIELD_NAME FROM TABLE_NAME_3 WHERE CONDITION_ABC));

Expected Result:

Code:

WHERE  CONDITION_1 AND  CONDITION_2 = (SELECT FIELD_NAME FROM TABLE_NAME_2 WHERE CONDITION_XYZ);
                                     or
WHERE  CONDITION_1 AND  CONDITION_2 = (SELECT FIELD_NAME FROM TABLE_NAME_2 WHERE CONDITION_XYZ and CONDITION_A = (SELECT FIELD_NAME FROM TABLE_NAME_3 WHERE CONDITION_ABC));

I am using following code which is working fine for normal SQLs but not for the above mentioned SQLs:

Code:

sed 's/.*\([wW][hH][eE][rR][eE] .*$\)/\1/' $dml > $temp1

Thanks in advance...

Last edited by ustechie; 01-12-2011 at 04:25 PM..

ustechie

View Public Profile for ustechie

Find all posts by ustechie

01-12-2011

Administrator Emeritus

9,179, 1,331

Join Date: Jun 2009

Last Activity: 26 February 2019, 5:57 PM EST

Posts: 9,179

Thanks Given: 430

Thanked 1,331 Times in 1,120 Posts

Code:

$ awk 'tolower("update") {print substr($0, index(tolower($0), "where"))}' file.sql
WHERE  CONDITION_1 AND  CONDITION_2 = (SELECT FIELD_NAME FROM TABLE_NAME_2 WHERE CONDITION_XYZ);
                                    or
WHERE  CONDITION_1 AND  CONDITION_2 = (SELECT FIELD_NAME FROM TABLE_NAME_2 WHERE CONDITION_XYZ and CONDITION_A = (SELECT FIELD_NAME FROM TABLE_NAME_3 WHERE CONDITION_ABC));

Scott

View Public Profile for Scott

Find all posts by Scott

01-12-2011

Registered User

59, 0

Join Date: Sep 2010

Last Activity: 3 March 2011, 11:32 AM EST

Posts: 59

Thanks Given: 0

Thanked 0 Times in 0 Posts

Hey Thanks a lot Scottn!!!

This is exactly what I was looking for....thanks again.....

I have 1 more doubt.....Now I want to replace the 1st WHERE from the result mentioned above with 'SELECT COUNT(*) FROM TABLE_NAME WHERE'.....

challenge is that we have more than 1 WHERE clause like:

Code:

$ awk 'tolower("update") {print substr($0, index(tolower($0), "where"))}' file.sql
WHERE  CONDITION_1 AND  CONDITION_2 = (SELECT FIELD_NAME FROM TABLE_NAME_2 WHERE CONDITION_XYZ);
                                    or
WHERE  CONDITION_1 AND  CONDITION_2 = (SELECT FIELD_NAME FROM TABLE_NAME_2 WHERE CONDITION_XYZ and CONDITION_A = (SELECT FIELD_NAME FROM TABLE_NAME_3 WHERE CONDITION_ABC));

but it should replace only first where clause.....
any idea about this

ustechie

View Public Profile for ustechie

Find all posts by ustechie

01-12-2011

Administrator Emeritus

9,179, 1,331

Join Date: Jun 2009

Last Activity: 26 February 2019, 5:57 PM EST

Posts: 9,179

Thanks Given: 430

Thanked 1,331 Times in 1,120 Posts

The "1st WHERE" clause from the previous output is the entire where clause:

Code:

WHERE  CONDITION_1 AND  CONDITION_2 = (SELECT FIELD_NAME FROM TABLE_NAME_2 WHERE CONDITION_XYZ);

The red part IS the where clause! It's the result of that on which the update acts.

Changing the "1st" one as you suggest would not seem to me to generate valid SQL.

Can you be more specific about the output you expect?

Scott

View Public Profile for Scott

Find all posts by Scott

01-12-2011

Registered User

68, 5

Join Date: Nov 2010

Last Activity: 28 June 2014, 1:27 AM EDT

Location: Houston, Tx

Posts: 68

Thanks Given: 8

Thanked 5 Times in 5 Posts

tr [a-z] [A-Z] | grep -oE 'WHERE .*$'

codecaine

View Public Profile for codecaine

Find all posts by codecaine

01-12-2011

Registered User

59, 0

Join Date: Sep 2010

Last Activity: 3 March 2011, 11:32 AM EST

Posts: 59

Thanks Given: 0

Thanked 0 Times in 0 Posts

sure...

This is the sql mentioned in the input file:

Code:

UPDATE TFNCLMRSV_INFO old SET old.cur_rec_ind = 'Y', old.CLM_RSV_UPDT_TS = current timestamp WHERE old.cur_rec_ind = 'N' AND old.CLM_RSV_INSRT_TS = (SELECT max(new.CLM_RSV_INSRT_TS) FROM TFNCLMRSV_INFO AS new WHERE old.CLM_NUM = new.CLM_NUM AND old.CVR_NUM = new.CVR_NUM AND old.SRC_SYS_CD = new.SRC_SYS_CD);

Before executing this SQL thru UNIX, I want to check the count affected by this SQL...

So I tried to extract the WHERE clause and create a SELECT COUNT(*) query by using that where clause....but for the SQL mentioned above, it didnt work as alias was used for the table name:

Code:

UPDATE TFNCLMRSV_INFO old SET......

ustechie

View Public Profile for ustechie

Find all posts by ustechie

01-12-2011

Administrator Emeritus

9,179, 1,331

Join Date: Jun 2009

Last Activity: 26 February 2019, 5:57 PM EST

Posts: 9,179

Thanks Given: 430

Thanked 1,331 Times in 1,120 Posts

Quote:

Originally Posted by codecaine

tr [a-z] [A-Z] | grep -oE 'WHERE .*$'

That command doesn't even work (you didn't provide a filename, and those options don't work on my OS for one).

Even if it did work, you can't just go carte blanche converting everything to uppercase!

Scott

View Public Profile for Scott

Find all posts by Scott

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

[Solved] Using AWK to extract SQL result

I just wish to extract the result onli... but I dont which edit to perform to start from (**) :wall: Current display result SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 24 14:14:49 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g...

2. Shell Programming and Scripting

How to extract the sql from file?

Hi Firends I have this type of file Rows read = 4823832 Statement text = SELECT QUEUE, REPLY_QUEUE, REPLY_QMGR FROM HUB_REF.CORNERSTONE.HUB_REF_QUEUE AS RQ WHERE (RQ.SERVICE_NAME = 'modifyParty' OR RQ.SERVICE_NAME = '*') AND RQ.SERVICE_TYPE =...

3. Shell Programming and Scripting

Extract text from a SQL Script

Hi All, I want to extact text which start with "Group" case insenstive and end with ";" and in between there should be "partition"(case insenstive) . I write a script, but that script also giving me FILE2.txt and file3.txt as outputs. I shouldn't get anything from file2 and file3. Beacuse...

4. Shell Programming and Scripting

How to grep the where clause of a SQL?

Hi UNIX Gurus, I want to use extract the where clause of a SQL present in a file. Please suggest me how can I do it. Select * from emp where emp_id>10; cat <file_name> | grep -i "where" returns whole SQL. how can I extract only "where emp_id>10;" Thanks in advance

5. UNIX for Advanced & Expert Users

Extract Oracle DB Connect and SQL execution log

Hi, I am trying to write a generic script as a part of a framework which will establish Oracle DB connection once and loops in to check for some files which gives the SQL statements to execute. The script is running but I am stuck with capturing errors ( ORA and SP) and outputs. Example: ...

6. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s...

7. Shell Programming and Scripting

Dynamic SQL for where clause

Hi, I have an app which user can query the database based on 4 criteria, that is Field1, Field2, Field3 and Field4 Mya I know how to write a dynamic SQL where I can choose to retrieve data based on their selected value. eg. where Field1=AAA eg. where Field1=AAA and Field2=BBB eg....

8. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete...

9. Shell Programming and Scripting

unix variables from sql / pl/sql

How do I dynamically assign the below output to unix shell variables so I can build a menu in a shell script? Example: var1 = 1 var2= SYSTEM var3 = 2 var4= UNDOTBS1 and so on, then in the shell script I can use the variables to build a menu. set serveroutput on declare...

10. Shell Programming and Scripting

Need to extract .sql files

Hi, I am trying to extract all .sql files present in a particular directory and its sub directories. How can i do this using shell script or awk? any help would be earnestly appreciated. thanks.

Login or Register to Ask a Question