[SQL] How can I use this subselect properly in an update?

[John Tuliao]

select
substring(t1.called_number from t2.offset),
t1.called_number
from
calls_join as t1,
john_prefix as t2
where
strpos(t1.called_number, t2.prefix) = '1'
order by length(t2.prefix) desc limit '1' ;

I'm having trouble using this as a sub-select in an UPDATE. Most of the 
time it works. Then sometimes it doesn't.
Is there an issue with possible NULL values that can be found on table1 
(t1)?



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Query question

[John Tuliao]

I seem to have a problem with a specific query:

The inside query seems to work on it's own:

select prefix
from john_prefix
where strpos(jpt_test.number,john_prefix.prefix) = '1'
order by char_length(john_prefix.prefix) desc limit 1

but when I execute it with this:

UPDATE
jpt_test
set
number = substring(number from length(john_prefix.prefix)+1)
from
john_prefix
where
prefix in (
select prefix
from john_prefix
where strpos(jpt_test.number,john_prefix.prefix) = '1'
order by char_length(john_prefix.prefix) desc limit 1
) ;

table contents are as follows

john_prefix table:

prefix
-
123
234

jpt_test table:

number
---
123799
023499 <<< supposed to have no match
234999

Am I missing something here? Any help will be appreciated.

Regards,
JPT


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Query Problem... Left OuterJoin / Tagging Issue

[John Tuliao]

Thank you so much for your prompt reply David. I will consider your 
advice and put it to mind and action. I hope you all don't get tired of 
helping!


For now, I will note down what I need to and do the necessary 
adjustments. Thank you for your time!


On Friday, 13 January, 2012 10:26 PM, David Johnston wrote:
On Jan 12, 2012, at 23:31, John Tuliao <mailto:jptul...@eglobalreach.net>> wrote:



Hi,

I've been working on this for quite awhile now and don't seem to get 
the proper query.


I have basically 4 tables.

1. Table john_test contains the numbers of the calls.
2. Table john_country contains the country with prefix.
3. Table john_clients contains the clients and their sub_id's
(because a client usually has a subclient, [ex. highway-2, 
highway-3]) and their prefix.

4. Table john_test contains the phone numbers.



select * from john_client_rate limit 3;

   name   |country | cali | cana | callrate | dir_id | 
trans_id | svc_id | base | incr | client_id

--++--+--+--++--++--+--+---
 highway  | Afghanistan|  |  |   0.6212 |  0 | 
0||6 |6 | 4
 highway  | Albania|  |  |   0.3945 |  0 | 
1||6 |6 | 4
 highway  | Bahamas|  |  |   0.0513 |  0 | 
1||6 |6 | 4

(3 rows)

select * from john_country limit 3;

country| state |  prefix  | area_code
---+---+--+---
 Afghanistan   |   | 93   |
 Aland Islands |   | 35818120 |
 Albania   |   | 355  |
(3 rows)


select * from john_clients limit 3;

 id | client_id | sub_id | name | prefix  |  type
+---++--+-+
 80 |80 |  0 | highway  | 71081   | client
 80 |80 |  0 | highway  | 7107011 | client
 80 |80 |  0 | highway  | 71091   | client
(3 rows)

select * from john_test limit 3;

client_id |  name   |   phonenum   | calledphonenum  
| phonenumtranslat | direction | duration

--+-+--+-+--+---+--
2 | highway | 83863011351927330133 | 20100147011351927330133 
|  | outbound  |  363
2 | highway | 83863011441179218126 | 1943011441179218126 
|  | outbound  |   83
2 | highway | 83863011441179218126 | 20100147011441179218126 
|  | outbound  |   32

(3 rows)



What I want to do is to remove the prefix, and retain the number 
using the following query:


select
john_test.name,
john_test.gwrxdcdn,
john_test.duration as dur,
john_client_rate.name as name2,
john_client_rate.country,
john_country.prefix,
substring(john_test.gwrxdcdn from length(john_country.prefix)+1) as 
strip,
get_duration(john_test.duration::int,john_client_rate.base,john_client_rate.incr) 
as realdur

from john_test
left outer join john_client_rate
on (prefix in
   (
  select prefix from john_client_rate
  where john_test.phonenum ~ ( '^' || john_country.prefix)
  order by length(john_country.prefix) desc limit '1'
   )
   )
limit 20;


If you have a select within the ON clause of a join it isn't really a 
join.  ON clauses should be simple expressions (almost always 
equality) between fields on the two tables with AND/OR logic.






I have achieved this already, now I want to identify which country 
it's supposed to be for.
Problem is sometimes the "stripped" number that is retained shows: 
8661234567 or 8889876543
This would indicate that the call is already toll free without me 
being able to identify the country.

How can I get over this?

Further, I am planning to use multiple joins since I have several 
tables and so as to identify missing countries. On this questions 
which query is better?


Query 1:

Select table1.column,table2.column,table3.column from table1 left 
outer join table 2 on (table1.column=table2.column) left outer join 
table3 on (table2.column=table3.column) ;


or Query 2:

Select table1.column,table2.column,table3.column from 
table1,table2,table3 where [conditions] ;




Query 1 is an outer join, query 2 is an inner join; totally different 
semantics so the question is more "which one will work" versus "which 
one is better".  Do you at least understand the difference?


Ultimately, I want to run one query that will satisfy th

[SQL] Query Problem... Left OuterJoin / Tagging Issue

[John Tuliao]

Hi,

I've been working on this for quite awhile now and don't seem to get the 
proper query.


I have basically 4 tables.

1. Table john_test contains the numbers of the calls.
2. Table john_country contains the country with prefix.
3. Table john_clients contains the clients and their sub_id's
(because a client usually has a subclient, [ex. highway-2, 
highway-3]) and their prefix.

4. Table john_test contains the phone numbers.



select * from john_client_rate limit 3;

   name   |country | cali | cana | callrate | dir_id | trans_id 
| svc_id | base | incr | client_id

--++--+--+--++--++--+--+---
 highway  | Afghanistan|  |  |   0.6212 |  0 | 0
||6 |6 | 4
 highway  | Albania|  |  |   0.3945 |  0 | 1
||6 |6 | 4
 highway  | Bahamas|  |  |   0.0513 |  0 | 1
||6 |6 | 4

(3 rows)

select * from john_country limit 3;

country| state |  prefix  | area_code
---+---+--+---
 Afghanistan   |   | 93   |
 Aland Islands |   | 35818120 |
 Albania   |   | 355  |
(3 rows)


select * from john_clients limit 3;

 id | client_id | sub_id | name | prefix  |  type
+---++--+-+
 80 |80 |  0 | highway  | 71081   | client
 80 |80 |  0 | highway  | 7107011 | client
 80 |80 |  0 | highway  | 71091   | client
(3 rows)

select * from john_test limit 3;

client_id |  name   |   phonenum   | calledphonenum  | 
phonenumtranslat | direction | duration

--+-+--+-+--+---+--
2 | highway | 83863011351927330133 | 20100147011351927330133 
|  | outbound  |  363
2 | highway | 83863011441179218126 | 1943011441179218126 
|  | outbound  |   83
2 | highway | 83863011441179218126 | 20100147011441179218126 
|  | outbound  |   32

(3 rows)



What I want to do is to remove the prefix, and retain the number using 
the following query:


select
john_test.name,
john_test.gwrxdcdn,
john_test.duration as dur,
john_client_rate.name as name2,
john_client_rate.country,
john_country.prefix,
substring(john_test.gwrxdcdn from length(john_country.prefix)+1) as strip,
get_duration(john_test.duration::int,john_client_rate.base,john_client_rate.incr) 
as realdur

from john_test
left outer join john_client_rate
on (prefix in
   (
  select prefix from john_client_rate
  where john_test.phonenum ~ ( '^' || john_country.prefix)
  order by length(john_country.prefix) desc limit '1'
   )
   )
limit 20;



I have achieved this already, now I want to identify which country it's 
supposed to be for.
Problem is sometimes the "stripped" number that is retained shows: 
8661234567 or 8889876543
This would indicate that the call is already toll free without me being 
able to identify the country.

How can I get over this?

Further, I am planning to use multiple joins since I have several tables 
and so as to identify missing countries. On this questions which query 
is better?


Query 1:

Select table1.column,table2.column,table3.column from table1 left outer 
join table 2 on (table1.column=table2.column) left outer join table3 on 
(table2.column=table3.column) ;


or Query 2:

Select table1.column,table2.column,table3.column from 
table1,table2,table3 where [conditions] ;


Ultimately, I want to run one query that will satisfy these things and 
help me insert into a table that will have it "TAGGED" properly with the 
right Country, Client(name), prefix, and Rate for computation with Duration.


Hope you can reply to me asap. This is of urgent importance. Thank you 
and any help would be greatly appreciated!


- JT