07-18-2019
Thanks Corona688 . I created a sub query and called separately for those 2 columns and it worked . Thank you for suggestion :-) Cheers
This User Gave Thanks to Perlbaby For This Post:
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi All,
I want to connect two tables in DB2 using shell script and then compare the contents of two tables field by field.and i should return on the screen the un matched records ..
Could any one please help me in connecting database tables using Unix and retriving data from the same. (1 Reply)
Discussion started by: kanakaraju
1 Replies
2. UNIX for Dummies Questions & Answers
Hi, I've got a file that I'd like to uniquely sort based on column 2 (values in column 2 begin with "comp").
I tried sort -t -nuk2,3 file.txtBut got:
sort: multi-character tab `-nuk2,3'
"man sort" did not help me out
Any pointers?
Input:
Output: (5 Replies)
Discussion started by: pathunkathunk
5 Replies
3. Shell Programming and Scripting
Hi,
I want to replace a chain of if-else statement in an old AWK file with values from Db2 table or CSV file. The part of code is below...
if (start_new_rec=="true"){
exclude_user="false";
user=toupper($6);
match(user, "XXXXX.");
if (RSTART ==2 ) {
... (9 Replies)
Discussion started by: asandy1234
9 Replies
4. Shell Programming and Scripting
Hi Gents,
Please can you help me to get the desired output .
In the first column I have some duplicate records, The condition is that all need to reject the duplicate record keeping the last occurrence. But the condition is. If the last occurrence is equal to value 14 or 98 in column 3 and... (2 Replies)
Discussion started by: jiam912
2 Replies
5. Shell Programming and Scripting
Hi,
Basically what I am trying to do is making multiple fields of the same type comma-separated.
i.e. for a data like this:
B00000 abc
B00001 abc,def
B00001 ghi
B00001 jkl
B00002 abc
B00002 def
B00003 xyz
Output should be like:
B00000 abc
B00001 abc,def,ghi,jkl... (20 Replies)
Discussion started by: prohank
20 Replies
6. Shell Programming and Scripting
Hello,
I have a script that is generating a tab delimited output file.
num Name PCA_A1 PCA_A2 PCA_A3
0 compound_00 -3.5054 -1.1207 -2.4372
1 compound_01 -2.2641 0.4287 -1.6120
3 compound_03 -1.3053 1.8495 ... (3 Replies)
Discussion started by: LMHmedchem
3 Replies
7. Shell Programming and Scripting
Hi Gurus,
I have a file(weblog) as below
abc|xyz|123|agentcode=sample code abcdeeess,agentcode=sample code abcdeeess,agentcode=sample code abcdeeess|agentadd=abcd stereet 23343,agentadd=abcd stereet 23343
sss|wwq|999|agentcode=sample1 code wqwdeeess,gentcode=sample1 code... (4 Replies)
Discussion started by: ratheeshjulk
4 Replies
8. Shell Programming and Scripting
Dear folks
I have a map file of around 54K lines and some of the values in the second column have the same value and I want to find them and delete all of the same values. I looked over duplicate commands but my case is not to keep one of the duplicate values. I want to remove all of the same... (4 Replies)
Discussion started by: sajmar
4 Replies
9. Programming
Hi Team
I am using DB2 artisan tool and struck to handle multi values present in columns that are comma(,) separated. I want to convert those column values in separate rows .
For example :
Column 1 Column2
Jan,Feb Hold,Sell,Buy
Expected Result
Column1 ... (3 Replies)
Discussion started by: Perlbaby
3 Replies
10. Programming
Dear Team
I am using DB2 v9 .
I have a condition to check roles based on hierarchies like below example.
1.Ramesh has Roles as "Manager" and "Interviewer"
2.KITS has Roles as "Interviewer"
3.ANAND has Roles as "Manager" and "Interviewer"
select * FROM TESTING
NAME ... (6 Replies)
Discussion started by: Perlbaby
6 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)