Visit Our UNIX and Linux User Community


Replace sql with dynamic values


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace sql with dynamic values
# 1  
Old 11-17-2017
Replace sql with dynamic values

Hi Guys,

I am using a function to replace the values dynamically to frame sql query by reading a file. My file will have column names like

Code:
file.txt
col_1
col_2

expected output:

Code:
select id,col_1,col_2 from (
select a.id,
a.col_1,
rank() over (ORDER BY cast(a.col_1 AS double) DESC) AS col_1_rank,
a.col_2,
rank() over (ORDER BY cast(a.col_2 AS double) DESC) AS col_2_rank
from table_name a )t
join 
( 
SELECT count(case when col_1='.' then null else 1 end) AS col_1_count,
count(case when col_2='.' then null else 1 end) AS col_2_count				
FROM   table_name
) f
where t.col_1_rank < 0.0001*f.col_1_count or t.col_2_rank < 0.0001*f.col_2_count;

where ever i am using col_1 and col_2 that should be read from the file and get replaced , there is a possibility that more columns will be added in file.txt but the query structure remains the same

Code:
sql_query {
while read -r col do
printf 'select id,%s,%s from (
select a.id,
a.%s,
rank() over (ORDER BY cast(a.%s AS double) DESC) AS %s_rank,
a.%s,
rank() over (ORDER BY cast(a.%s AS double) DESC) AS %s_rank
from table_name a )t
join 
( 
SELECT count(case when %s='.' then null else 1 end) AS %s_count,
count(case when %s='.' then null else 1 end) AS %s_count				
FROM   table_name
) f
where t.%s_rank < 0.0001*f.%s_count or t.%s_rank < 0.0001*f.%s_count;'
done < $1 # this will be file.txt as parameter

}


Last edited by Master_Mind; 11-17-2017 at 06:51 AM.. Reason: change in code
# 2  
Old 11-17-2017
And the question is?
What if the number of columns is NOT 2, i.e. is 1 or 4? And, isn't there a table_name missing?
# 3  
Old 11-17-2017
Yes the number of columns in the file can increase but the structure of the query remains the same and it gets added for e.g. if col_3 is there in file.txt. Then below will be the output

Code:
select id,col_1,col_2,col_3 from (
select a.id,
a.col_1,
a.col_2,
a.col_3,
rank() over (ORDER BY cast(a.col_1 AS double) DESC) AS col_1_rank,
rank() over (ORDER BY cast(a.col_2 AS double) DESC) AS col_2_rank,
rank() over (ORDER BY cast(a.col_3 AS double) DESC) AS col_3_rank,
from table_name a )t
join 
( 
SELECT count(case when col_1='.' then null else 1 end) AS col_1_count,
count(case when col_2='.' then null else 1 end) AS col_2_count,
count(case when col_3='.' then null else 1 end) AS col_3_count				
FROM   table_name
) f
where t.col_1_rank < 0.0001*f.col_1_count or t.col_2_rank < 0.0001*f.col_2_count or t.col_3_rank < 0.0001*f.col_3_count ;

# 4  
Old 11-17-2017
Quote:
Originally Posted by Master_Mind
I am using a function to replace the values dynamically to frame sql query by reading a file.
So why don't you enhance your function to handle additional column entries?

Post your attempts to resolve this problem and let us know where you are stuck.
# 5  
Old 11-17-2017
Thanks Yoda,

I am struck in my function where the query formation gets duplicated. So struck over there.
# 6  
Old 11-18-2017
Try
Code:
awk '
        {T[NR] = $1
        }

END     {printf "select id"
         for (i=1; i<=NR; i++) printf ",%s", T[i]
         print " from (" ORS "select a.id,"
         for (i=1; i<=NR; i++)  printf "a.%s,%srank() over (ORDER BY cast(a.%s AS double) DESC) AS %s_rank%s%s", T[i], ORS, T[i], T[i], (i<NR)?",":"", ORS
         print "from table_name a )t" ORS "join" ORS "(" ORS "SELECT"
         for (i=1; i<=NR; i++) printf "count(case when %s=%s.%s then null else 1 end) AS %s_count%s%s", T[i], SQ, SQ, T[i], (i<NR)?",":"", ORS
         print "FROM table_name" ORS ") f"
         printf "where"
         for (i=1; i<=NR; i++) printf " t.%s_rank < 0.0001*f.%s_count%s", T[i], T[i], (i==NR)?";" ORS:" or" 
        }
' SQ="'" file

with file containing any number of columns.

Last edited by RudiC; 11-18-2017 at 02:09 PM..
This User Gave Thanks to RudiC For This Post:

Previous Thread | Next Thread
Test Your Knowledge in Computers #828
Difficulty: Medium
JSON and ECMAScript 2018 both allows the Unicode line terminators U+2028 LINE SEPARATOR and U+2029 PARAGRAPH SEPARATOR to appear unescaped in quoted strings.
True or False?

10 More Discussions You Might Find Interesting

1. Linux

Shell Script to generate Dynamic Param file Using SQL Plus Quey

Hi All, Can anyone give me Shell script sample script to generate Param file by Reading Values from SQL Plus query and it should assign those values to variables like.. $$SChema_Name='ORCL' Thanks in Advance... Srav... (4 Replies)
Discussion started by: Sravana Kumar
4 Replies

2. Shell Programming and Scripting

Using dynamic arrays to extract the values

Hi all, We have requirement to generate load timing based on subject areas HOUSEHOLD, BANKING and TRADING. These values are stored in an array SUB_ARR SUB_ARR=("HOUSEHOLD" "BANKING" "TRADING") Based on indicator files produced while processing data for each type, we need to get the stats (using... (2 Replies)
Discussion started by: sanjaydubey2006
2 Replies

3. Shell Programming and Scripting

dynamic values in a row

hi i have an input file in which there are diffrent values for xxxx,yyyyyy,zzzzzzz how can i arrange the dynamic values of x,y&z in a row. input file: xxxxx 1 yyyyyy 4 yyyyyy 5 zzzzzzzz 7 yyyyyy 13 zzzzzzzz 7 zzzzzzzz 6 yyyyyy 14 yyyyyy 12 zzzzzzzz 4 yyyyyy 4 yyyyyy 5 yyyyyy 6... (6 Replies)
Discussion started by: dodasajan
6 Replies

4. Shell Programming and Scripting

Dynamic sql where contents

Hi all, I need to add the contents from a file into a sql stament in the where clause. file1: id 1 2 3 10 11 ... script should look like : select name from tab_user tus where tus.id in (1,2,3,10,11..) any ideas or suggetions will be appreciatte. (5 Replies)
Discussion started by: valigula
5 Replies

5. Shell Programming and Scripting

Dynamic SQL query based on shell script parameters

Hi, I need a script that will run a dynamic Oracle SQL. Dynamic meaning the SQL statement depends on the parameter. For instance, something like this: #!/bin/ksh -x # Set environment . /home/mine/set_vars sqlplus $LOGINID <<! >> /home/mine/log.txt select count(1) from $1 where... (2 Replies)
Discussion started by: laiko
2 Replies

6. UNIX for Advanced & Expert Users

Sql dynamic table / dynamic inserts

I have a file that reads File (X.txt) Contents of record 1: rdrDESTINATION_ADDRESS (String) "91 971502573813" rdrDESTINATION_IMSI (String) "000000000000000" rdrORIGINATING_ADDRESS (String) "d0 movies" rdrORIGINATING_IMSI (String) "000000000000000" rdrTRAFFIC_EVENT_TIME... (0 Replies)
Discussion started by: magedfawzy
0 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. Shell Programming and Scripting

Extracting dynamic values

Hi, I am stuck with extracting values by combining 2 dynamically extracted values. The code goes like this #!/usr/bin/ksh ID1="abcd" i=1 #this is a dynamic value and keeps on changing b="ID" #this is static now i want the value of ID1 variable. like echo $b$i But echo... (1 Reply)
Discussion started by: chaitanyapn
1 Replies

9. UNIX for Dummies Questions & Answers

Dynamic variable values

Bit of a newbie :D with regard to unix scripting and need some advice. Hopefully someone can help with the following: I have a predefined set of variables as follows: AAA_IP_ADD=1.1.1.1 BBB_IP_ADD=2.2.2.2 I have a funnction call which retrieves a value into $SUPPLIER which would be... (3 Replies)
Discussion started by: ronnie_uk
3 Replies

10. Shell Programming and Scripting

Dynamic SQl in KSH

My requirement is to create a KSH to generate the SQL select statement in oracle with all the columns and optional where condition if given the table name as input to the program Have any of you worked with a similar requirement? Can you give me some inputs? Regards, Kousikan (2 Replies)
Discussion started by: kousikan
2 Replies

Featured Tech Videos