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
expected output:
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
Last edited by Master_Mind; 11-17-2017 at 05:51 AM..
Reason: change in code
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)
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)
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)
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)
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)
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)
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)
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)
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)
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
LEARN ABOUT CENTOS
values
VALUES(7) PostgreSQL 9.2.7 Documentation VALUES(7)NAME
VALUES - compute a set of rows
SYNOPSIS
VALUES ( expression [, ...] ) [, ...]
[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
DESCRIPTION
VALUES computes a row value or set of row values specified by value expressions. It is most commonly used to generate a "constant table"
within a larger command, but it can be used on its own.
When more than one row is specified, all the rows must have the same number of elements. The data types of the resulting table's columns
are determined by combining the explicit or inferred types of the expressions appearing in that column, using the same rules as for UNION
(see Section 10.5, "UNION, CASE, and Related Constructs", in the documentation).
Within larger commands, VALUES is syntactically allowed anywhere that SELECT is. Because it is treated like a SELECT by the grammar, it is
possible to use the ORDER BY, LIMIT (or equivalently FETCH FIRST), and OFFSET clauses with a VALUES command.
PARAMETERS
expression
A constant or expression to compute and insert at the indicated place in the resulting table (set of rows). In a VALUES list appearing
at the top level of an INSERT, an expression can be replaced by DEFAULT to indicate that the destination column's default value should
be inserted. DEFAULT cannot be used when VALUES appears in other contexts.
sort_expression
An expression or integer constant indicating how to sort the result rows. This expression can refer to the columns of the VALUES result
as column1, column2, etc. For more details see ORDER BY Clause.
operator
A sorting operator. For details see ORDER BY Clause.
count
The maximum number of rows to return. For details see LIMIT Clause.
start
The number of rows to skip before starting to return rows. For details see LIMIT Clause.
NOTES
VALUES lists with very large numbers of rows should be avoided, as you might encounter out-of-memory failures or poor performance. VALUES
appearing within INSERT is a special case (because the desired column types are known from the INSERT's target table, and need not be
inferred by scanning the VALUES list), so it can handle larger lists than are practical in other contexts.
EXAMPLES
A bare VALUES command:
VALUES (1, 'one'), (2, 'two'), (3, 'three');
This will return a table of two columns and three rows. It's effectively equivalent to:
SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
More usually, VALUES is used within a larger SQL command. The most common use is in INSERT:
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
In the context of INSERT, entries of a VALUES list can be DEFAULT to indicate that the column default should be used here instead of
specifying a value:
INSERT INTO films VALUES
('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'),
('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT);
VALUES can also be used where a sub-SELECT might be written, for example in a FROM clause:
SELECT f.*
FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t (studio, kind)
WHERE f.studio = t.studio AND f.kind = t.kind;
UPDATE employees SET salary = salary * v.increase
FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase)
WHERE employees.depno = v.depno AND employees.sales >= v.target;
Note that an AS clause is required when VALUES is used in a FROM clause, just as is true for SELECT. It is not required that the AS clause
specify names for all the columns, but it's good practice to do so. (The default column names for VALUES are column1, column2, etc in
PostgreSQL, but these names might be different in other database systems.)
When VALUES is used in INSERT, the values are all automatically coerced to the data type of the corresponding destination column. When it's
used in other contexts, it might be necessary to specify the correct data type. If the entries are all quoted literal constants, coercing
the first is sufficient to determine the assumed type for all:
SELECT * FROM machines
WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43'));
Tip
For simple IN tests, it's better to rely on the list-of-scalars form of IN than to write a VALUES query as shown above. The list of
scalars method requires less writing and is often more efficient.
COMPATIBILITY
VALUES conforms to the SQL standard. LIMIT and OFFSET are PostgreSQL extensions; see also under SELECT(7).
SEE ALSO INSERT(7), SELECT(7)PostgreSQL 9.2.7 2014-02-17 VALUES(7)