Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Append data 1 table to other

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Append data 1 table to other
# 1  
Old 03-16-2011
Power Append data 1 table to other
Hi.
I have 2 create 2 temporary tables.the data will be same with same cols..but after creating 2 tables..i have to merge data in file and send..however the query is after merging data no duplicates shud be present..and only 1 record for a entity must be present..
for eg:
table1 has foll cols and data:
cols:-->
R_ID || L_DATE|| XXX

data:-->
101|| 01/02/2009 || order1
102|| 02/02/2010 || order2

table2 has foll cols and data:
R_ID || L_DATE|| XXX
data:-->

101|| 05/02/2010 || order1
106|| 02/02/2010 || order5

so i need that when i merge data from 1 table to another or into a file,, then
for order 1 record i shud get only 1 entry in table or file and that shud be d latest one i.e:based on L_DATE column va;ue fr this record
plz help me..

Smilie
its urgent...plz reply
# 2  
Old 03-17-2011
Code:
 
select distinct a.* from (
  select * from table1 union select * from table2
 ) a join (
  select r_id, max(l_date) l_date from (
    select * from table1 union select * from table2
   ) b
 ) c on c.r_id=a.r_id and c.l_date = a.l_date

In shell, it is a little different, sorting all by r_id and then l_date descending (not raw strig but year, month, day substring keys) and feed it to a script discarding later l_dates for the same r_id.
Code:
sort -t'|' <4-key-specs> table1 table2 | while read r_id l_date xxx
do
 if [ "$r_id" = "$r_id_last" ]
 then
  continue
 fi
 r_id_last="$r_id"
 echo "$r_id||$l_date||$xxx"
done >table3

Last edited by DGPickett; 03-17-2011 at 05:59 PM..
# 3  
Old 03-22-2011
hi..thanx fr reply..But i need that after i find duplicate,i shud remove d oldest l_date entries for d record and then only latest l_date entry for record shud b present..dont need shell script cmds..wanna do it in oracle/sql
# 4  
Old 03-22-2011
Yes, my SQL finds the highest date for every id "select r_id, max(l_date) l_date from (derived table of both tables union):,
and joins that with the original tables (derived table of both tables union).




You had two problems:
  1. Make the two tables act as one, solution: (derived table of both tables union, e.g., a and b below).
  2. Filter derived table a for just latest l_date, solution: inner join to derived table c of just (r_id and max(l_date) from both tables b).
Derived table is a wonderful mechanism, like an inline view, isolating column and table names, hiding unions or aggregates so you can treat them as one table, allowing aggregates over multiple tables. Oracle will sort-merge both derived tables for the join. Think of it as a cursor cast as a table, or a stream of data.

I forgot group by the first time:

Code:
select distinct a.* 
  from (
      select * from table1 union select * from table2
     ) a
   join (
      select r_id, max(l_date) l_date
       from (
         select * from table1 union select * from table2
        ) b
       group by r_id
     ) c
    on   c.r_id   = a.r_id
     and c.l_date = a.l_date

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Getting data in table form

Hi, I have a csv file from which i am fetching few columns as below: IFILE=/home/home1/Report1.csv OFILE=/home/home1/`date +"%m%d%y%H%M%S"`.dat if #Checks if file exists and readable then awk -F "," '(NR>4) {print $1,$6,$2,$3,$4,$5,$6}' ${IFILE} >> ${OFILE} fi cat $OFILE | mail... (7 Replies)
Discussion started by: Vivekit82
7 Replies

2. Programming

Opening FD and append Data

Hello everybody I'am trying to open a File with an Filedeskriptor. After Opening the file I want to append Data to the File! I have the following code now, but I only overwrite the data from the file and did not append it! void Buffer::writeIntoFile(std::string name, int length, std::string... (2 Replies)
Discussion started by: pk543450
2 Replies

3. Shell Programming and Scripting

How to take data from table?

Hi all , am using unix aix .. Actually i have a table called table 1 in that table year period startdate enddate 2013 1 26/03/2012 29/04/2012 2013 2 30/04/2012 27/05/2012 2013 3 28/05/2012 28/06/2012 2013 4 25/06/2012 ... (10 Replies)
Discussion started by: Venkatesh1
10 Replies

4. Programming

Append data to smallint data in informix4gl?

Hi, I have an smallint variable, say "a", i would like to prefix it with "0" in certain conditions. Is it possible to achieve that with this datatype? For instance, a=9 --> a=09 Many thanks (1 Reply)
Discussion started by: dvah
1 Replies

5. Shell Programming and Scripting

want to append the data in one file to the another

Hi , i have two log files, i need to combine this as a one log file. i need to do this by SED , test1.log sadadadaadfsaf test2.log adadadadadada i need this in a single file from test 1 to test2.log test2.log(expected result) adadadadadada (7 Replies)
Discussion started by: mhdmehraj
7 Replies

6. Shell Programming and Scripting

append data to each line

Hi guys, I need to investigate a memory leak on a solaris server, so what I have done is pmap'd each process on the system with a script which tar'd the directory every hour in cron. Now I need to write a script to process the pmap data. So what I have is about 100 directories # ll... (2 Replies)
Discussion started by: borderblaster
2 Replies

7. Shell Programming and Scripting

Append the data to first column

Hi, The below is the content of the file. 008.03.50.21|ID4|0015a3f01cf3 008.04.20.16|ID3|0015a3f02337 008.04.20.17|ID4_1xVoice|00131180d80e 008.04.20.03|ID3_1xVoice|0015a3694125 008.04.30.05|ID3_1xVoice|0015a3f038af 008.06.30.17|ID3_1xVoice|00159660d454... (2 Replies)
Discussion started by: ravi_rn
2 Replies

8. Shell Programming and Scripting

Data table

Hi there, I get a list of data set by means a script with many echo command that looks like this: CASE 1 A= 4 B= 4 CASE 2 A= 3 B= 5 Is... (3 Replies)
Discussion started by: Giordano Bruno
3 Replies

9. Shell Programming and Scripting

Showing several data in one table

hi again :$ i am creating a script to be able to monitor a machine's performance. the code in the file is: ================q============== sar 2 3 |awk '{print $3}' vmstat 2 3 |awk '{print $19 " " $21}' iostat -cx 2 3 |awk '{print $8 " " $10 " " $13}' ================uq============= the... (4 Replies)
Discussion started by: mohamedh_14
4 Replies

10. UNIX for Dummies Questions & Answers

append data to file

i want to develop a script newdata that writes new data to a file called items the file items has the following headings columns separated by tabs: channel date time programe if i type executable file newdata on the command line with parameters, it should append it to the items files the... (1 Reply)
Discussion started by: fletcher
1 Replies
Login or Register to Ask a Question