Sponsored Content
Top Forums Shell Programming and Scripting How to create SQL statement out of data using shell script? Post 302949599 by Chubler_XL on Monday 13th of July 2015 06:30:25 PM
Old 07-13-2015
How about this using awk, note the inputfile is a tab separated text file matching you posted input.

Code:
awk -F'\t' '
BEGIN { printf "SELECT ROW_KEY_COL,\nCASE\n" }
$8 ~ "[ZN]" {
    if($8=="Z") RL[$1]=$7
    VAR[$1]=$6
    NXT[$1]=$2
    next
}
$8 == "D" { printf "ELSE %c%s%c END\n",39,$5,39}
$8=="Y" {
  wexp=sprintf("THEN %c%s%c\n",39,$5,39)
  rule=$2
  ND=" "
  while(rule in NXT) {
     num=split(RL[rule],rf," ")

     # Parse rule text - this needs some enhancement
     if (num==1) {
        if (index(RL[rule], ",")) wexp=sprintf("%s IN (%s)%s",VAR[rule],RL[rule],ND) wexp
        else
           if (RL[rule]+0 > 0 || RL[rule]+0 == RL[rule]) wexp=sprintf("%s = %s%s",VAR[rule],RL[rule],ND) wexp
           else wexp=sprintf("%s = %c%s%c%s",VAR[rule],39,RL[rule],39,ND) wexp
     }
     if (num==3) wexp=sprintf("%s BETWEEN %s AND %s%s",VAR[rule],rf[1],rf[3],ND) wexp
     if (num==5) wexp=sprintf("(%s BETWEEN %s AND %s OR %s IS NULL)%s",VAR[rule],rf[1],rf[3],VAR[rule],ND) wexp
     # parse done

     rule=NXT[rule]
     ND = " AND "
  }
  printf "WHEN %s", wexp
}
END { printf "FROM TAB1;\n" }' infile

Also note: The parsing of rules is pretty basic and only caters for you posted examples. I expect you will need something more complex to replace these if (num ==x) tests

Last edited by Chubler_XL; 07-13-2015 at 07:42 PM..
This User Gave Thanks to Chubler_XL For This Post:
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

how to assign sql output data to shell script variable

Hi Guys ! I am new to unix and want to find out how we can make sql statement data to shell script variable? Any help/suggestion is greatly appreciated -Chandra (1 Reply)
Discussion started by: kattics
1 Replies

2. Shell Programming and Scripting

using SELECT sql statement in shell script

Hi there I have a database on a remote box and i have been using shell script to insert data into it for example, i could have a script that did this SN=123456 n=server1 m=x4140 sql="UPDATE main SET hostname='$n',model='$m' WHERE serial='$SN';" echo $sql |/usr/sfw/bin/mysql -h... (4 Replies)
Discussion started by: hcclnoodles
4 Replies

3. Shell Programming and Scripting

How to pass tablenames from a file to shell script to execute create statement in DB2

Hi, I am new to Shell Scripting, and I need to create nicknames for 600 tables in db2. I have the file names in a text file and i have to pass these table names to a shell script create nicknames in db2. Can some one please help me in this regard. (1 Reply)
Discussion started by: kamalanaatha
1 Replies

4. Shell Programming and Scripting

How to get full sql table data using shell script

i have a Oracle table like col1 col2 ---- ----- a 1 b 2 c 3 when i write a script for it , it gives me all the data in one column. Please give me the solution which gives the exact result like we see in sql editors. for a in `echo " set feedback off; set pagesize 40;... (1 Reply)
Discussion started by: ss135r
1 Replies

5. Programming

create a spool file based on values passed from korn shell to sql script

this is my issue. 4 parameters are passed from korn shell to sql script. parameter_1= varchar2 datatype or no value entered my user. parameter_2= number datatype or no value entered my user. parameter_3= number datatype or no value entered my user. parameter_4= number datatype or no... (5 Replies)
Discussion started by: megha2525
5 Replies

6. Shell Programming and Scripting

Korn shell script - SQL statement challenges

Hi scripting experts. I have some coding challenges that I'm hoping you can help me out. I have one file#1 that contains the following sql statement that spans over multiple lines: sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id... (1 Reply)
Discussion started by: pchang
1 Replies

7. Homework & Coursework Questions

Write a shell script for SQL loader to load data into a staging table

Hi, I'm new to Linux. I'm working on a database, and need to load data in a database table (which I already created) using shell script. The table has two columns - Acct_number (not nullable) and date (timestamp). I'm not able to write a shell script for that. Can any one help me? ... (3 Replies)
Discussion started by: saisudeep
3 Replies

8. Shell Programming and Scripting

Generate sql statement using shell scripting

Can anyone please assist me? I have attached 2 input files and one output file. I need to generate the sql update statements using the above 2 input files. if inputfile2 has 5 rows, then we should generate 5 update statements because column1 is unique. inputfile1 and inputfile2 may contain more... (10 Replies)
Discussion started by: vinus
10 Replies

9. Shell Programming and Scripting

Run sql query in shell script and output data save as delimited text

I want to run sql query in shell script and output data save as delimited text (delimited text would be comma) Code: SPOOL_FILE=/pgedw/dan.txt SQL=/pgedw/dan.sql sqlplus -s username/password@myhost:port/servicename <<EOF set head on set COLSEP , set linesize 32767 SET TRIMSPOOL ON SET... (8 Replies)
Discussion started by: Jaganjag
8 Replies

10. Shell Programming and Scripting

Oop to copy and rename files through SQL Statement in shell Script

#!/bin/sh sqlplus -s "/ as sysdba" << EOF SET HEADING OFF SET FEEDBACK OFF Select pt.user_concurrent_program_name , OUTFILE_NAME FROm apps.fnd_concurrent_programs_tl pt, apps.fnd_concurrent_requests f where pt.concurrent_program_id = f.concurrent_program_id and pt.application_id =... (1 Reply)
Discussion started by: usman_oracle
1 Replies
tcprules(1)						      General Commands Manual						       tcprules(1)

NAME
tcprules - compile rules for tcpserver SYNOPSIS
tcprules rules.cdb rules.tmp OVERVIEW
tcpserver optionally follows rules to decide whether a TCP connection is acceptable. For example, a rule of 18.23.0.32:deny prohibits connections from IP address 18.23.0.32. tcprules reads rules from its standard input and writes them into rules.cdb in a binary format suited for quick access by tcpserver. tcprules can be used while tcpserver is running: it ensures that rules.cdb is updated atomically. It does this by first writing the rules to rules.tmp and then moving rules.tmp on top of rules.cdb. If rules.tmp already exists, it is destroyed. The directories containing rules.cdb and rules.tmp must be writable to tcprules; they must also be on the same filesystem. If there is a problem with the input, tcprules complains and leaves rules.cdb alone. The binary rules.cdb format is portable across machines. RULE FORMAT
A rule takes up one line. A file containing rules may also contain comments: lines beginning with # are ignored. Each rule contains an address, a colon, and a list of instructions, with no extra spaces. When tcpserver receives a connection from that address, it follows the instructions. ADDRESSES
tcpserver starts by looking for a rule with address TCPREMOTEINFO@TCPREMOTEIP. If it doesn't find one, or if TCPREMOTEINFO is not set, it tries the address TCPREMOTEIP. If that doesn't work, it tries shorter and shorter prefixes of TCPREMOTEIP ending with a dot. If none of them work, it tries the empty string. For example, here are some rules: joe@127.0.0.1:first 18.23.0.32:second 127.:third :fourth ::1:fifth If TCPREMOTEIP is 10.119.75.38, tcpserver will follow the fourth instructions. If TCPREMOTEIP is ::1, tcpserver will follow the fifth instructions. Note that you cannot detect IPv4 mapped addresses by matching "::ffff", as those addresses will be converted to IPv4 before looking at the rules. If TCPREMOTEIP is 18.23.0.32, tcpserver will follow the second instructions. If TCPREMOTEINFO is bill and TCPREMOTEIP is 127.0.0.1, tcpserver will follow the third instructions. If TCPREMOTEINFO is joe and TCPREMOTEIP is 127.0.0.1, tcpserver will follow the first instructions. ADDRESS RANGES
tcprules treats 1.2.3.37-53:ins as an abbreviation for the rules 1.2.3.37:ins, 1.2.3.38:ins, and so on up through 1.2.3.53:ins. Similarly, 10.2-3.:ins is an abbreviation for 10.2.:ins and 10.3.:ins. INSTRUCTIONS
The instructions in a rule must begin with either allow or deny. deny tells tcpserver to drop the connection without running anything. For example, the rule :deny tells tcpserver to drop all connections that aren't handled by more specific rules. The instructions may continue with some environment variables, in the format ,VAR="VALUE". tcpserver adds VAR=VALUE to the current envi- ronment. For example, 10.0.:allow,RELAYCLIENT="@fix.me" adds RELAYCLIENT=@fix.me to the environment. The quotes here may be replaced by any repeated character: 10.0.:allow,RELAYCLIENT=/@fix.me/ Any number of variables may be listed: 127.0.0.1:allow,RELAYCLIENT="",TCPLOCALHOST="movie.edu" SEE ALSO
tcprulescheck(1), tcpserver(1), tcp-environ(5) tcprules(1)
All times are GMT -4. The time now is 11:23 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy