Identifying columns individually in the query to load into array is the problem I see here. Because I need to pick some delimiter for identifying columns. I can not choose ',' or 'AS' as they are used by other sql functions like coalesce and cast.
Also there is no guarantee that the columns will be one per line. They may include multiple columns in the same line.
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)
I have data extracted like this:
A=%123% B=%456% C=%789%
A=%111% B=%222% C=%333%
A=%777% B=%888% C=%999%
Can someone please help me with a script to remove all the % signs and get the totals for A, B and C.
So output will be:
A=1368
B=666
C=2664
Thank you! (2 Replies)
Hi,
I have a huge file with a single line.
But I want to break that line into lines of with each line having five columns.
My file is like this:
code:
"hi","there","how","are","you?","It","was","great","working","with","you.","hope","to","work","you."
I want it like this:
code:... (1 Reply)
Hi Gurus,
This below script adds a column extra to my flat file..But how can i add another column, Say if i just put mention an other column beside the first column..it does get generated but as one column only
while IFS="" read r; do
printf "dummy\t%s\n" "$r"
done < xxx.txt > zzz.txt
... (9 Replies)
I am trying to selectively display several columns from a db2 query, which gives me a fixed-width output (partial output listed here):
--------- -------------------------- ------------ ------
000 0000000000198012 702 29
000 0000000000198013 ... (9 Replies)
Hi Friends,
I have the following input file
cat input
chr1 100 200 0.1 0.2 na 1 na nd
chr1 105 200 0.1 0.2 1 1 na 98
chr1 110 290 nf 1 na nd na 1
chr2 130 150 12 3 na 1 na 1
chr3 450 600 nf nf na 10 na nd
chr4 300 330 1 1 10 11 23 34
My requirement is
1. If $6 is na make $7 nd and... (2 Replies)
Hi,
I have connected to oracle database with sqlplus -s / <<EOF
select ename, age from emp where empid=1234;
EOF
I want to save the values of ename and age in unix shell variables. Any pointers would be welcome..
Thanks in advance!!1
Cheers :):):):) (1 Reply)
Hi all, I know this sounds suspiciously like a homework course; but, it is not.
My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Hi, I have 2 different values in the same column and two different values in other column
Query 1
ins name value
1 Test 12345
1 TestV1 12/10/2014
8 Test 85435
8 TestV1 11/11/2005
9 Test 42232
9 TestV1 19/10/2000
6 Test 54321... (6 Replies)
I have two files, file1 and file2 who have identical number of rows and columns. However, the script is supposed to be used for for different files and I cannot know the format in advance. Also, the number of columns changes within the file, some rows have more and some less columns (they are... (13 Replies)
Discussion started by: maya3
13 Replies
LEARN ABOUT REDHAT
create_view
CREATE VIEW(7) SQL Commands CREATE VIEW(7)NAME
CREATE VIEW - define a new view
SYNOPSIS
CREATE [ OR REPLACE ] VIEW view [ ( column name list ) ] AS SELECT query
INPUTS
view The name (optionally schema-qualified) of a view to be created.
column name list
An optional list of names to be used for columns of the view. If given, these names override the column names that would be deduced
from the SQL query.
query An SQL query (that is, a SELECT statement) which will provide the columns and rows of the view.
Refer to SELECT [select(7)] for more information about valid arguments.
OUTPUTS
CREATE VIEW
The message returned if the view is successfully created.
ERROR: Relation 'view' already exists
This error occurs if the view specified already exists in the database.
WARNING: Attribute 'column' has an unknown type
The view will be created having a column with an unknown type if you do not specify it. For example, the following command gives a
warning:
CREATE VIEW vista AS SELECT 'Hello World'
whereas this command does not:
CREATE VIEW vista AS SELECT text 'Hello World'
DESCRIPTION
CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, a query rewrite rule (an ON SELECT rule) is auto-
matically generated to support SELECT operations on views.
CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. You can only replace a view with a new
query that generates the identical set of columns (i.e., same column names and data types).
If a schema name is given (for example, CREATE VIEW myschema.myview ...) then the view is created in the specified schema. Otherwise it is
created in the current schema (the one at the front of the search path; see CURRENT_SCHEMA()). The view name must be distinct from the
name of any other view, table, sequence, or index in the same schema.
NOTES
Currently, views are read only: the system will not allow an insert, update, or delete on a view. You can get the effect of an updatable
view by creating rules that rewrite inserts, etc. on the view into appropriate actions on other tables. For more information see CREATE
RULE [create_rule(7)].
Use the DROP VIEW statement to drop views.
USAGE
Create a view consisting of all Comedy films:
CREATE VIEW kinds AS
SELECT *
FROM films
WHERE kind = 'Comedy';
SELECT * FROM kinds;
code | title | did | date_prod | kind | len
-------+---------------------------+-----+------------+--------+-------
UA502 | Bananas | 105 | 1971-07-13 | Comedy | 01:22
C_701 | There's a Girl in my Soup | 107 | 1970-06-11 | Comedy | 01:36
(2 rows)
COMPATIBILITY
SQL92
SQL92 specifies some additional capabilities for the CREATE VIEW statement:
CREATE VIEW view [ column [, ...] ]
AS SELECT expression [ AS colname ] [, ...]
FROM table [ WHERE condition ]
[ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
The optional clauses for the full SQL92 command are:
CHECK OPTION
This option is to do with updatable views. All INSERT and UPDATE commands on the view will be checked to ensure data satisfy the
view-defining condition. If they do not, the update will be rejected.
LOCAL Check for integrity on this view.
CASCADE
Check for integrity on this view and on any dependent view. CASCADE is assumed if neither CASCADE nor LOCAL is specified.
CREATE OR REPLACE VIEW is a PostgreSQL language extension.
SQL - Language Statements 2002-11-22 CREATE VIEW(7)