Sponsored Content
Top Forums Programming Query SQL get two values differents from the same columns Post 302889959 by durden_tyler on Monday 24th of February 2014 02:23:14 PM
Old 02-24-2014
Code:
SQL>
SQL> --
SQL> select * from t1;
 
       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 TestV1     11/05/2013
 
8 rows selected.
 
SQL>
SQL> --
SQL> select * from t2;
 
VALUE
----------
12345
54321
 
2 rows selected.
 
SQL>
SQL> -- Method 1
SQL> select x.value, z.value
  2    from t1 x
  3         join t2 y on (y.value = x.value)
  4         join t1 z on (z.ins = x.ins and z.name = 'TestV1')
  5  ;
 
VALUE      VALUE
---------- ----------
12345      12/10/2014
54321      11/05/2013
 
2 rows selected.
 
SQL>
SQL> -- Method 2
SQL> select x.value,
  2         y.testv1_value
  3    from t2 x
  4         join (
  5                   select ins,
  6                          max(case when name = 'Test' then value end) as test_value,
  7                          max(case when name = 'TestV1' then value end) as testv1_value
  8                     from t1
  9                    group by ins
 10              ) y
 11         on (x.value = y.test_value)
 12  ;
 
VALUE      TESTV1_VAL
---------- ----------
12345      12/10/2014
54321      11/05/2013
 
2 rows selected.
 
SQL>
SQL>

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

2. Shell Programming and Scripting

Query Oracle tables and return values to shell script that calls the query

Hi, I have a requirement as below which needs to be done viz UNIX shell script (1) I have to connect to an Oracle database (2) Exexute "SELECT field_status from table 1" query on one of the tables. (3) Based on the result that I get from point (2), I have to update another table in the... (6 Replies)
Discussion started by: balaeswari
6 Replies

3. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

4. Shell Programming and Scripting

Adding columns with values dependent on existing columns

Hello I have a file as below chr1 start ref alt code1 code2 chr1 18884 C CAAAA 2 0 chr1 135419 TATACA T 2 0 chr1 332045 T TTG 0 2 chr1 453838 T TAC 2 0 chr1 567652 T TG 1 0 chr1 602541 ... (2 Replies)
Discussion started by: plumb_r
2 Replies

5. UNIX for Dummies Questions & Answers

Getting values of 2 columns from sql query in UNIX variables

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)
Discussion started by: gonchusirsa
1 Replies

6. Shell Programming and Scripting

Query the table and return values to shell script and search result values from another files.

Hi, I need a shell script, which would search the result values from another files. 1)execute " select column1 from table_name" query on the table. 2)Based on the result, need to be grep from .wft files. could please explain about this.Below is the way i am using. #!/bin/sh... (4 Replies)
Discussion started by: Rami Reddy
4 Replies

7. Shell Programming and Scripting

Reading values from sql query

I have sql query in shell script. select distinct account_no from adj order by account_no; This query returns account number daily.Sometimes it may return 90 rows sometime it may return 1 row only and someday it may return 0 rows I am storing the output of this query in sql_output.txt. ... (5 Replies)
Discussion started by: rafa_fed2
5 Replies

8. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Hello All, I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns. So my requirement is to generate a output query which will have 20 columns all the... (7 Replies)
Discussion started by: vikas_trl
7 Replies

9. Shell Programming and Scripting

awk - matching on 2 columns for differents lines

Given this file (I separated them in block to make my explanation clearer): 92157768877;Sof_deme_Fort_Email_am_%yyyy%%mm%%dd%;EMAIL;20/02/2015;1;0;0 92157768877;Sof_trav_Fort_Email_am_%yyyy%%mm%%dd%;EMAIL;20/02/2015;1;0;0 91231838895;Sof_deme_faible_Email_am;EMAIL;26/01/2015;1 0;0... (1 Reply)
Discussion started by: Andy_K
1 Replies

10. Shell Programming and Scripting

Asign to variable ksh some values of sql query

Hi, I'm trying to asign to ksh varible some values of a sql query. The output query would be: xxxx 1 yyyy 2 I do: values=`$PATH_UTI/query_sh " select think1||'------'||think2 from some_table where think3 = '$1'; ... (2 Replies)
Discussion started by: mierdatuti
2 Replies
SQL::Translator::Parser::DBIx::Class(3) 		User Contributed Perl Documentation		   SQL::Translator::Parser::DBIx::Class(3)

NAME
SQL::Translator::Parser::DBIx::Class - Create a SQL::Translator schema from a DBIx::Class::Schema instance SYNOPSIS
## Via DBIx::Class use MyApp::Schema; my $schema = MyApp::Schema->connect("dbi:SQLite:something.db"); $schema->create_ddl_dir(); ## or $schema->deploy(); ## Standalone use MyApp::Schema; use SQL::Translator; my $schema = MyApp::Schema->connect; my $trans = SQL::Translator->new ( parser => 'SQL::Translator::Parser::DBIx::Class', parser_args => { dbic_schema => $schema, add_fk_index => 0, sources => [qw/ Artist CD /], }, producer => 'SQLite', ) or die SQL::Translator->error; my $out = $trans->translate() or die $trans->error; DESCRIPTION
This class requires SQL::Translator installed to work. "SQL::Translator::Parser::DBIx::Class" reads a DBIx::Class schema, interrogates the columns, and stuffs it all in an $sqlt_schema object. Its primary use is in deploying database layouts described as a set of DBIx::Class classes, to a database. To do this, see "deploy" in DBIx::Class::Schema. This can also be achieved by having DBIx::Class export the schema as a set of SQL files ready for import into your database, or passed to other machines that need to have your application installed but don't have SQL::Translator installed. To do this see "create_ddl_dir" in DBIx::Class::Schema. PARSER OPTIONS
dbic_schema The DBIx::Class schema (either an instance or a class name) to be parsed. This argument is in fact optional - instead one can supply it later at translation time as an argument to "translate" in SQL::Translator. In other words both of the following invocations are valid and will produce conceptually identical output: my $yaml = SQL::Translator->new( parser => 'SQL::Translator::Parser::DBIx::Class', parser_args => { dbic_schema => $schema, }, producer => 'SQL::Translator::Producer::YAML', )->translate; my $yaml = SQL::Translator->new( parser => 'SQL::Translator::Parser::DBIx::Class', producer => 'SQL::Translator::Producer::YAML', )->translate(data => $schema); add_fk_index Create an index for each foreign key. Enabled by default, as having indexed foreign key columns is normally the sensible thing to do. sources Arguments: @class_names Limit the amount of parsed sources by supplying an explicit list of source names. SEE ALSO
SQL::Translator, DBIx::Class::Schema AUTHORS
See "CONTRIBUTORS" in DBIx::Class. LICENSE
You may distribute this code under the same terms as Perl itself. perl v5.18.2 2014-01-22 SQL::Translator::Parser::DBIx::Class(3)
All times are GMT -4. The time now is 11:34 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy