|
|
|
Re: Compare two tables and get a new table [message #350187 is a reply to message #350178] |
Wed, 24 September 2008 05:38   |
raed_237
Messages: 19 Registered: August 2008
|
Junior Member |
|
|
i already have the tables I am just looking for the third table as i mentioned before, if you want to know the data types here they are:
for the first table - the Prices Table:
Destination= varchar2(500)
Code= number
Price= float
for the second Table - Phone calls and Total Minutes:
Called Number= number
Minutes= float
hope this will help, the result i need is in the third table and u can also find it in the attached file
|
|
|
|
Re: Compare two tables and get a new table [message #350193 is a reply to message #350174] |
Wed, 24 September 2008 05:52   |
 |
rajavu1
Messages: 1574 Registered: May 2005 Location: Bangalore , India
|
Senior Member |

|
|
But Like Operator May not give the desired output in this case.
Because ...
Afghanistan-Mobile 9370 0.1691
Afghanistan-Mobile 9377 0.1691
Afghanistan-Other 93 0.2005
If the called Number is something like 93727875676465 or 934567867689 the Actual destination will be Afghanistan-Other. Like Operator has all the chance to fail here.
The workaround (SQL) will be ..
1. For each called number , generate 7 possible destination Numbers ( like 9372787, 937278, 93727, 9372, 937, 93, 9 in case of 93727875676465 ).
2, Join genarated numbers with First table .
3. And get the destination with largest fraction ( 1- 7)
Btw, Test case means , Crate Table and insert table script to generate the issue case , so that respondent has no need to think much to reproduce the data. It is for our convenience.

Rajuvan.
|
|
|
|
|
|
|
Re: Compare two tables and get a new table [message #350391 is a reply to message #350174] |
Wed, 24 September 2008 22:36   |
 |
rajavu1
Messages: 1574 Registered: May 2005 Location: Bangalore , India
|
Senior Member |

|
|
Solution using Row generator technique is
SELECT Called_Number,
Minutes,
Destination,
Code,
Price
FROM (
select CT.*,
PT.*,
RANK() OVER (PARTITION BY CALLED_NUMBER,MINUTES ORDER BY XT.l DESC ) RNk
from call_time CT,
( select level l from dual connect by level <8) XT ,
PRICE_TAB PT
WHERE SUBSTR(CT.CALLED_NUMBER,1,l) = PT.Code)
WHERE RNK=1

Rajuvan.
[Updated on: Wed, 24 September 2008 22:39] Report message to a moderator
|
|
|
|
|
|
Re: Compare two tables and get a new table [message #350485 is a reply to message #350235] |
Thu, 25 September 2008 03:40   |
raed_237
Messages: 19 Registered: August 2008
|
Junior Member |
|
|
thanks Alesandro
but it gave an error with the (by prefix desc)
i don't know why, it says Prefix: invalid identifier
Alessandro Rossi wrote on Wed, 24 September 2008 16:21 | In this way I used analytics to avoid anti-self-join.
select Called_Number, destination, Minutes, charge
from (
select Called_Number, destination, Minutes, price*minutes as charge,
row_number() over (partition by called_number order by prefix desc) as rn
from Phone_calls
join Prices_table on (called_number like code||'%')
)
where rn = 1
/
Bye Alessandro
|
|
|
|
Re: Compare two tables and get a new table [message #350486 is a reply to message #350232] |
Thu, 25 September 2008 03:40   |
ctr8130
Messages: 2 Registered: January 2008
|
Junior Member |
|
|
There should be relation between the two tables that you are using.
Primary key and foreign key concept is missing .Pls let us know the primary key and foreign key of the first two tables that has been used.
Regards
Rajesh
|
|
|
|
|
Re: Compare two tables and get a new table [message #350489 is a reply to message #350486] |
Thu, 25 September 2008 03:44   |
raed_237
Messages: 19 Registered: August 2008
|
Junior Member |
|
|
the relation is by using the Code in the Prices table and the Called_number table
ctr8130 wrote on Thu, 25 September 2008 11:40 | There should be relation between the two tables that you are using.
Primary key and foreign key concept is missing .Pls let us know the primary key and foreign key of the first two tables that has been used.
Regards
Rajesh
|
|
|
|
|
|
Re: Compare two tables and get a new table [message #350494 is a reply to message #350485] |
Thu, 25 September 2008 03:50   |
 |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
raed_237 wrote on Thu, 25 September 2008 10:40 |
it gave an error with the (by prefix desc)
i don't know why, it says Prefix: invalid identifier
|
I wrote it on the fly and without a test case I couldn't test it. You should change prefix with name of the column for the area-codes ( number prefixes ).
And remember that you get the total cost of the call like Rajuvan just pointed out. To get just the cost per minute don't multiply the price column for minutes, but jut use the price column.
Bye Alessandro
[Updated on: Thu, 25 September 2008 03:51] Report message to a moderator
|
|
|
Re: Compare two tables and get a new table [message #350497 is a reply to message #350391] |
Thu, 25 September 2008 03:52   |
raed_237
Messages: 19 Registered: August 2008
|
Junior Member |
|
|
and this one works as well ... many thanks rajavu1, though i didn't know what XT stands for
rajavu1 wrote on Thu, 25 September 2008 06:36 | Solution using Row generator technique is
SELECT Called_Number,
Minutes,
Destination,
Code,
Price
FROM (
select CT.*,
PT.*,
RANK() OVER (PARTITION BY CALLED_NUMBER,MINUTES ORDER BY XT.l DESC ) RNk
from call_time CT,
( select level l from dual connect by level <8) XT ,
PRICE_TAB PT
WHERE SUBSTR(CT.CALLED_NUMBER,1,l) = PT.Code)
WHERE RNK=1

Rajuvan.
|
|
|
|
|
|
|
|
|
Re: Compare two tables and get a new table [message #350546 is a reply to message #350174] |
Thu, 25 September 2008 06:28   |
raed_237
Messages: 19 Registered: August 2008
|
Junior Member |
|
|
alright now i have starting time-we still can have more than one call at the same time
so here is the new format
Start_Time Called_Number total-minutes
-----------------------------------------------------------------
9/23/2008 23:53 9647808181238 2
9/23/2008 23:53 9647808121888 3
9/23/2008 23:56 9647808181877 3.4
9/23/2008 23:56 2347808181888 0.78
9/23/2008 23:52 9444780816888 7.89
9/23/2008 23:52 9647808181888 3.67
|
|
|
|
|
Compare Two Tables and generate the differences in the third (merged 3) [message #384346 is a reply to message #350174] |
Tue, 03 February 2009 08:28   |
raed_237
Messages: 19 Registered: August 2008
|
Junior Member |
|
|
Hi all,
i have two tables each contains destination, code and price as follows:
Table A
Destination ----- Code ---- Price
UK-----------------44--------0.2
USA----------------1---------0.3
UK-Mobile----------4476------0.28
USA-Mobile---------122-------034
Table B
Destination ----- Code ---- Price
UK-----------------44--------0.2
USA----------------1---------0.3
UK-Mobile----------4476------0.28
USA-Mobile---------122-------0.34
India-Mobile--------33-------0.21
so each table has different data, what i need is to find what destinations are not in table A but in Table B, and put the result into table C as follows:
Table C
Destination ----- Code ---- Price
India-Mobile--------33-------0.21
as the names could be in different formats, we need to depend on the Code as our primary key.
many thanks in advance.
|
|
|
|
|
|
|
|
|
|
|