Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to extract WHERE clause from a SQL using UNIX???
# 8  
Old 01-12-2011
Quote:
Originally Posted by scottn
That command doesn't even work (you didn't provide a filename).

Even if it did, you can't just go carte blanche converting everything to uppercase!
I was just showing you how to extract the text. If it was in a file it would look like this.


tr [a-z] [A-Z] < mysql.txt | grep -oE 'WHERE .*$'
# 9  
Old 01-12-2011
Quote:
Originally Posted by codecaine
SQL is not case sensitive. I was just showing you how to extract the text. If it was in a file it would look like this.


tr [a-z] [A-Z < mysql.txt | grep -oE 'WHERE .*$'
Fair enough.

But while SQL per se might not be case-sensitive, data is.
# 10  
Old 01-12-2011
You could do like your sed example grep -oE '[Wh][Hh][Ee][Rr][Ee] .*$' < mysql.txt
Last edited by codecaine; 01-12-2011 at 06:06 PM..
# 11  
Old 01-12-2011
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......

Is there any way to create a SELECT COUNT(*) statement using the above mentioned UPDATE statement even if alias are used in the SQL???
# 12  
Old 01-12-2011
Quote:
Originally Posted by scottn
Fair enough.

But while SQL per se might not be case-sensitive, data is.
You are correct I tried to edit it fast but guess it still posted. only tables and fields are not.
# 13  
Old 01-12-2011
A quick "hack" (it assumes your DML is uppercase) while I start Oracle and throw in some tests...
Code:
$ sed "s/ SET / #!#!# /;s/WHERE/!#!#!/;s/UPDATE/SELECT COUNT(1) FROM/;s/#!#!#.*!#!#!/WHERE/" file.sql
SELECT COUNT(1) FROM TFNCLMRSV_INFO old 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);

Last edited by Scott; 01-12-2011 at 06:40 PM..
# 14  
Old 01-12-2011
Hi Scottn...

your resolution is working.....I tried to modify it so that it can work for both small cases and upper cases.....plz check if this looks good:

Code:
sed "s/ [sS][eE][tT] / #!#!# /;s/[wW][hH][eE][rR][eE]/!#!#!/;s/[uU][pP][dD][aA][tT][eE]/SELECT COUNT(*) FROM/;s/#!#!#.*!#!#!/WHERE/" file.sql`


What needs to be done for DELETE statements?? any idea....
Instead of UPDATE statements, if DELETE statements are mentioned then how would we create SELECT COUNT(*) statements using same WHERE clause as mentioned in the DELETE query considering alias
Last edited by ustechie; 01-12-2011 at 07:54 PM..
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... (4 Replies)
Discussion started by: ment0smintz
4 Replies

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 =... (1 Reply)
Discussion started by: atul9806
1 Replies

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... (1 Reply)
Discussion started by: molakal9
1 Replies

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 (4 Replies)
Discussion started by: ustechie
4 Replies

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: ... (4 Replies)
Discussion started by: mirage0809
4 Replies

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... (2 Replies)
Discussion started by: Nareshp
2 Replies

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.... (1 Reply)
Discussion started by: TeSP
1 Replies

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... (1 Reply)
Discussion started by: reptile
1 Replies

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... (2 Replies)
Discussion started by: djehres
2 Replies

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. (1 Reply)
Discussion started by: sprinleo
1 Replies
Login or Register to Ask a Question