Join 3 files using key column in a mapping file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Join 3 files using key column in a mapping file
# 8  
Old 07-11-2009
Just came back from vacation. My apologies for late response.
If it's not too late yet, here is a part of code I used:


for i in `cat map| awk 'BEGIN { FS = " " }; { print $2}'`
do
isql -S*** -U*** -P*** -w 900 >> /tmp/tmp.out << EOF
use db1
go
select "$i", count(*)
from table1 tb1
join table2 tb2 on tb2.id2 = tb1.id1
join table3 tb3 on tb3.id3 = tb2.orderid
where tb1.number <> 0
and tb3.name = "$i"
go
EOF
cat /tmp/inv-tmp.out|grep "$i">>/tmp/file.out
done

After I chage the bold line to "for i in `cat map| awk '{print $2}'`" , with awk 'BEGIN...' part removed, it started to go through.

Thanks!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join and merge multiple files with duplicate key and fill void columns

Join and merge multiple files with duplicate key and fill void columns Hi guys, I have many files that I want to merge: file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: (5 Replies)
Discussion started by: yjacknewton
5 Replies

2. Shell Programming and Scripting

Linux - Join 2 csv files with common key

Hi, I am trying to join 2 csv files, to create a 3rd output file with the joined data. Below is an example of my Input Data: Input File 1 NAME, FAV_FOOD, FAV_DRINK, ID, GENDER Bob, Fish, Coke, 1, M Lisa, Rice, Water, 2, F Jenny, Noodle, Tea, 3, F Ken, Pizza, Coffee, 4, M Lisa,... (7 Replies)
Discussion started by: RichZR
7 Replies

3. Shell Programming and Scripting

Join 2nd column of multiple files

Dear All, I have many files formatted like this: file1.txt: 1/2-SBSRNA4 18 A1BG 3 A1BG-AS1 6 A1CF 0 A2LD1 1 A2M 1160 file2.txt 1/2-SBSRNA4 53 A1BG 1 A1BG-AS1 7 A1CF 0 A2LD1 3 A2M 2780 (5 Replies)
Discussion started by: paolo.kunder
5 Replies

4. UNIX for Dummies Questions & Answers

Join 2 files based on certain column

I have file input1.txt 11103|11|OTTAWA|City|AA|CAR|0|0|1|-1|0|8526|2014-09-07 23:00:14 11103|11|OTTAWA|City|BB|TRAIN|0|0|2|-2|6|6359|2014-09-07 23:00:14 11104|11|CANADA|City|CC|CAR|0|0|2|-2|0|5947|2014-09-07 23:00:14 11104|11|CANADA|City|DD|TRAIN|0|0|2|-2|1|4523|2014-09-07 23:00:14... (5 Replies)
Discussion started by: radius
5 Replies

5. UNIX for Dummies Questions & Answers

Join files by second column

I have file input file1 1/1/2013 A 553.0763397 96 16582 1/1/2013 B 459.8333588 195 11992 1/2/2013 A 844.2973022 306 19555 1/2/2013 B 833.9300537 457 20165 1/3/2013 A 563.6917419 396 13879 1/3/2013 B 632.0749969 169 ... (1 Reply)
Discussion started by: radius
1 Replies

6. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

7. Shell Programming and Scripting

join two files based on one column

Hi All, I am trying to join to files based on one common column. Cat File1 ID HID Ab_1 23 Cd 45 df 22 Vv 33 Cat File2 ID pval Ab_1 0.3 Cd 10 Vv 0.0444 (3 Replies)
Discussion started by: newpro
3 Replies

8. Shell Programming and Scripting

Join 3 or more files using matching column

Dear Forum, Full title of the topic would be: "Join 3 or more files using matching column without full list in any of these columns" I have several, typically 3 or 4 files which I need to join, something like FULL JOIN in slq scripts, all combinations of matches should be printed into an... (3 Replies)
Discussion started by: cyz700
3 Replies

9. UNIX for Dummies Questions & Answers

Join 2 files using first column

Hi, I'm trying to compare the first column of two files (tab or whitespace delimited, either way's fine, I`ve got both) and print the lines that are identical for the first column of both files. Something like this: File1 AAA 26 49 7 27 36 33 46 75 73 69 AAAAA 4 10 4 7 10 18 21... (2 Replies)
Discussion started by: vanesa1230
2 Replies

10. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies
Login or Register to Ask a Question
alias(1)						      General Commands Manual							  alias(1)

NAME
alias - Defines or displays aliases SYNOPSIS
alias [-tx] [alias-name[=string...]] Note The C shell has a built-in version of the alias command. If you are using the C shell, and want to guarantee that you are using the com- mand described here, you must specify the full path /usr/bin/alias. See the csh(1) reference page for a description of the built-in com- mand. STANDARDS
Interfaces documented on this reference page conform to industry standards as follows: alias: XCU5.0 Refer to the standards(5) reference page for more information about industry standards and associated tags. OPTIONS
[Tru64 UNIX] Sets or lists tracked aliases. [Tru64 UNIX] Sets or lists exported aliases. Note See the ksh(1) reference page for a description of tracked and exported aliases. OPERANDS
Prints the alias definition on standard output. Assigns the value of string to the alias alias-name. If no options and no operands are specified, all alias definitions are printed on standard output. DESCRIPTION
The alias utility creates or redefines alias definitions or writes the values of existing alias definitions to standard output. An alias definition provides a string value that replaces a command name when it is encountered. An alias definition affects the current shell execution environment and the execution environments of the subshells of the current shell. When used as described, the alias definition will not affect the parent process of the current shell nor any utility environment invoked by the shell. NOTES
[Tru64 UNIX] This reference page describes the creation and maintenance of aliases. See the Command Aliasing section of the ksh(1) or sh(1p) reference pages for the description of alias substitution. RESTRICTIONS
[Tru64 UNIX] If you use either the -t option or the -x option, you must use at least one alias-name or alias-name=string operand. [Tru64 UNIX] Aliasing is performed when scripts are read, not while they are executed. Therefore, for an alias to take effect, the alias definition command has to be executed before the command that references the alias is read. [Tru64 UNIX] Aliases can be used to redefine special built-in commands but cannot be used to redefine the reserved words listed in the ksh(1) reference page. EXIT STATUS
The following exit values are returned: Successful completion. One of the name operands specified did not have an alias definition, or an error occurred. EXAMPLES
Change ls to give annotated output in columns: alias ls="ls -CF" Create a simple redo command to repeat previous entries in the command history file: alias r='fc -s' Cause du to use 1K units instead of the default 512-bytes: alias du=du -k Set up nohup so that it can deal with an argument that is itself an alias name: alias nohup="nohup " ENVIRONMENT VARIABLES
The following environment variables affect the execution of alias: Provides a default value for the internationalization variables that are unset or null. If LANG is unset or null, the corresponding value from the default locale is used. If any of the internationalization vari- ables contain an invalid setting, the utility behaves as if none of the variables had been defined. If set to a non-empty string value, override the values of all the other internationalization variables. Determines the locale for the interpretation of sequences of bytes of text data as characters (for example, single-byte as opposed to multibyte characters in arguments). Determines the locale for the format and contents of diagnostic messages written to standard error. Determines the location of message catalogues for the processing of LC_MES- SAGES. SEE ALSO
Commands: csh(1), ksh(1), Bourne shell sh(1b), POSIX shell sh(1p), unalias(1) Standards: standards(5) alias(1)