Thread: Problem with sql
i have four same tables in postgres and oracle.I'm
using the postgres 7.1 version.
listed below
GROUPSMS=# select * from group_smu_trans ;
sub_id | con_phone |
message |
--------+--------------+------------------------------------------------------------------+---------
2 | 919810058237 | hi checking
| 2001-05-01
2 | 919810299102 | hi checking
| 2001-05-01
2 | 919810058237 | hi checking
| 2001-05-01
2 | 919810299102 | hi checking
| 2001-05-01
2 | 919810058237 | hi checking
| 2001-05-01
2 | 919810299102 | hi checking
| 2001-05-01
2 | 9810125422 | Kindly Check . |
2001-05-01
(7 rows)
GROUPSMS=# select * from subscriber_mast ;
sub_id | sub_name | sub_username | sub_pwd |
sub_phone | sub_add | sub_type | plan
--------+-----------+--------------+-----------+-----------+----------------+----------+------
2 | veeren | veeren | veeren |
8976548 | kljhjkhdslkjhf | o | c
10 | textarea1 | textarea1 | textarea1 |
textarea1 | textarea1 | o | c
(2 rows)
GROUPSMS=# select * from sub_group_contact_lnk;
sub_id | group_id | contact_id
--------+----------+------------
2 | 18 |
2 | 18 | 27
2 | 21 |
2 | 21 | 33
2 | 21 | 37
(5 rows)
GROUPSMS=# select * from pre_sub_bal ;
sub_id | sub_total | sub_current | msg_rate
--------+-----------+-------------+----------
3 | 350 | 200 | 125
4 | 250 | 200 | 75
7 | 500 | 500 | 75
8 | 250 | 250 | 75
1 | 250 | 125 | 125
2 | 250 | 117.25 | 125
(6 rows)
create view mis_group_smu_trans
as
select sub_id,count(message) as
tot_message,count(con_phone) as con_phone from
group_smu_trans
group by sub_id;
GROUPSMS=# select * from mis_group_smu_trans ;
sub_id | tot_message | con_phone
--------+-------------+-----------
2 | 7 | 7
(1 row)
create view mis_sub_group_contact
as
select sub_id,count(distinct group_id) as
group_id,count(distinct contact_id) as contact_id from
sub_group_contact_lnk
group by sub_id;
GROUPSMS=# select * from mis_sub_group_contact;
sub_id | group_id | contact_id
--------+----------+------------
2 | 2 | 3
(1 row)
create view test1 as
select
a.sub_id,a.sub_name,c.tot_message,c.con_phone,d.sub_total,d.sub_current
from
subscriber_mast a,
mis_group_smu_trans c,
pre_sub_bal d
where
a.sub_id=c.sub_id and
a.sub_id=d.sub_id;
GROUPSMS=# select * from test1;
sub_id | sub_name | tot_message | con_phone |
sub_total | sub_current
--------+----------+-------------+-----------+-----------+-------------
2 | veeren | 7 | 7 |
250 | 117.25
(1 row)
create view test2 as
select
a.sub_id,b.group_id,b.contact_id
from
subscriber_mast a,
mis_sub_group_contact b
where
a.sub_id=b.sub_id;
GROUPSMS=# select * from test2;
sub_id | group_id | contact_id
--------+----------+------------
2 | 2 | 3
(1 row)
select a.*,b.* from test1 a ,test2 b
where b.sub_id=a.sub_id;
GROUPSMS-# where b.sub_id=a.sub_id;
sub_id | sub_name | tot_message | con_phone |
sub_total | sub_current | sub_id | group_id | contac
--------+----------+-------------+-----------+-----------+-------------+--------+----------+--------
2 | veeren | 35 | 35 |
250 | 117.25 | 2 | 2 | 3
(1 row)
Now wht happen is in my final query of test1
and test2, "total_message" and "con_phone" both
the columns output is changed by 5 times which is a
random value.
The same test case i have taken to the "oracle"
there i find the it is working absolutly fine.
Kindly handle this issue because one of major
application at my customor end is running.If find
problem in reading pls check the attachment file.
Regards
Shashi Bhushan Ahuja
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/i have four same tables in postgres and oracle.I'm using the postgres 7.1
version.
listed below
GROUPSMS=# select * from group_smu_trans ;
sub_id | con_phone | message |
--------+--------------+------------------------------------------------------------------+---------
2 | 919810058237 | hi checking | 2001-05-01
2 | 919810299102 | hi checking | 2001-05-01
2 | 919810058237 | hi checking | 2001-05-01
2 | 919810299102 | hi checking | 2001-05-01
2 | 919810058237 | hi checking | 2001-05-01
2 | 919810299102 | hi checking | 2001-05-01
2 | 9810125422 | Kindly Check . | 2001-05-01
(7 rows)
GROUPSMS=# select * from subscriber_mast ;
sub_id | sub_name | sub_username | sub_pwd | sub_phone | sub_add | sub_type | plan
--------+-----------+--------------+-----------+-----------+----------------+----------+------
2 | veeren | veeren | veeren | 8976548 | kljhjkhdslkjhf | o | c
10 | textarea1 | textarea1 | textarea1 | textarea1 | textarea1 | o | c
(2 rows)
GROUPSMS=# select * from sub_group_contact_lnk;
sub_id | group_id | contact_id
--------+----------+------------
2 | 18 |
2 | 18 | 27
2 | 21 |
2 | 21 | 33
2 | 21 | 37
(5 rows)
GROUPSMS=# select * from pre_sub_bal ;
sub_id | sub_total | sub_current | msg_rate
--------+-----------+-------------+----------
3 | 350 | 200 | 125
4 | 250 | 200 | 75
7 | 500 | 500 | 75
8 | 250 | 250 | 75
1 | 250 | 125 | 125
2 | 250 | 117.25 | 125
(6 rows)
create view mis_group_smu_trans
as
select sub_id,count(message) as tot_message,count(con_phone) as con_phone from
group_smu_trans
group by sub_id;
GROUPSMS=# select * from mis_group_smu_trans ;
sub_id | tot_message | con_phone
--------+-------------+-----------
2 | 7 | 7
(1 row)
create view mis_sub_group_contact
as
select sub_id,count(distinct group_id) as
group_id,count(distinct contact_id) as contact_id from
sub_group_contact_lnk
group by sub_id;
GROUPSMS=# select * from mis_sub_group_contact;
sub_id | group_id | contact_id
--------+----------+------------
2 | 2 | 3
(1 row)
create view test1 as
select
a.sub_id,a.sub_name,c.tot_message,c.con_phone,d.sub_total,d.sub_current
from
subscriber_mast a,
mis_group_smu_trans c,
pre_sub_bal d
where
a.sub_id=c.sub_id and a.sub_id=d.sub_id;
GROUPSMS=# select * from test1;
sub_id | sub_name | tot_message | con_phone | sub_total | sub_current
--------+----------+-------------+-----------+-----------+-------------
2 | veeren | 7 | 7 | 250 | 117.25
(1 row)
create view test2 as
select
a.sub_id,b.group_id,b.contact_id
from
subscriber_mast a,
mis_sub_group_contact b
where
a.sub_id=b.sub_id;
GROUPSMS=# select * from test2;
sub_id | group_id | contact_id
--------+----------+------------
2 | 2 | 3
(1 row)
select a.*,b.* from test1 a ,test2 b
where b.sub_id=a.sub_id;
GROUPSMS-# where b.sub_id=a.sub_id;
sub_id | sub_name | tot_message | con_phone | sub_total | sub_current | sub_id | group_id | contac
--------+----------+-------------+-----------+-----------+-------------+--------+----------+--------
2 | veeren | 35 | 35 | 250 | 117.25 | 2 | 2 | 3
(1 row)
Now wht happen is in my final query of test1 and test2, "total_message" and "con_phone" both
the columns output is changed by 5 times which is a random value.
The same test case i have taken to the "oracle" there i find the it is working absolutly fine.
Kindly handle this issue because one of major application at my customor end is running.
Regards
Shashi Bhushan Ahuja
shashi ahuja <shashiahuja@yahoo.com> writes:
> Now wht happen is in my final query of test1 and test2, "total_message" and "con_phone" both
> the columns output is changed by 5 times which is a random value.
Would you mind providing this example in the form of a psql script that
reproduces the problem starting from an empty database? (ie, create and
fill the tables and execute the query that gives a bogus result)
Guessing at your table declarations is not a productive use of our time.
regards, tom lane
I do not see this (using fairly random table creations since you didn't provide the schema) in 7.2devel. I get 7 and 7 at the end. Which 7.1 are you using? Have you tried upgrading to 7.1.2? In any case, a script to create the tables and views and populate them with the appropriate data would be helpful here since it could be dependant on the actual table structure. On Thu, 31 May 2001, shashi ahuja wrote: > i have four same tables in postgres and oracle.I'm > using the postgres 7.1 version. > listed below > > GROUPSMS=# select * from group_smu_trans ; > sub_id | con_phone | > message | > --------+--------------+------------------------------------------------------------------+--------- > 2 | 919810058237 | hi checking > | 2001-05-01 > 2 | 919810299102 | hi checking > | 2001-05-01 > 2 | 919810058237 | hi checking > | 2001-05-01 > 2 | 919810299102 | hi checking > | 2001-05-01 > 2 | 919810058237 | hi checking > | 2001-05-01 > 2 | 919810299102 | hi checking > | 2001-05-01 > 2 | 9810125422 | Kindly Check . | > 2001-05-01 > (7 rows) > > GROUPSMS=# select * from subscriber_mast ; > sub_id | sub_name | sub_username | sub_pwd | > sub_phone | sub_add | sub_type | plan > --------+-----------+--------------+-----------+-----------+----------------+----------+------ > 2 | veeren | veeren | veeren | > 8976548 | kljhjkhdslkjhf | o | c > 10 | textarea1 | textarea1 | textarea1 | > textarea1 | textarea1 | o | c > (2 rows) > > GROUPSMS=# select * from sub_group_contact_lnk; > sub_id | group_id | contact_id > --------+----------+------------ > 2 | 18 | > 2 | 18 | 27 > 2 | 21 | > 2 | 21 | 33 > 2 | 21 | 37 > (5 rows) > > GROUPSMS=# select * from pre_sub_bal ; > sub_id | sub_total | sub_current | msg_rate > --------+-----------+-------------+---------- > 3 | 350 | 200 | 125 > 4 | 250 | 200 | 75 > 7 | 500 | 500 | 75 > 8 | 250 | 250 | 75 > 1 | 250 | 125 | 125 > 2 | 250 | 117.25 | 125 > (6 rows) > > create view mis_group_smu_trans > as > select sub_id,count(message) as > tot_message,count(con_phone) as con_phone from > group_smu_trans > group by sub_id; > GROUPSMS=# select * from mis_group_smu_trans ; > sub_id | tot_message | con_phone > --------+-------------+----------- > 2 | 7 | 7 > (1 row) > > create view mis_sub_group_contact > as > select sub_id,count(distinct group_id) as > group_id,count(distinct contact_id) as contact_id from > sub_group_contact_lnk > group by sub_id; > GROUPSMS=# select * from mis_sub_group_contact; > sub_id | group_id | contact_id > --------+----------+------------ > 2 | 2 | 3 > (1 row) > > create view test1 as > select > a.sub_id,a.sub_name,c.tot_message,c.con_phone,d.sub_total,d.sub_current > from > subscriber_mast a, > mis_group_smu_trans c, > pre_sub_bal d > where > a.sub_id=c.sub_id and > a.sub_id=d.sub_id; > GROUPSMS=# select * from test1; > sub_id | sub_name | tot_message | con_phone | > sub_total | sub_current > --------+----------+-------------+-----------+-----------+------------- > 2 | veeren | 7 | 7 | > 250 | 117.25 > (1 row) > > create view test2 as > select > a.sub_id,b.group_id,b.contact_id > from > subscriber_mast a, > mis_sub_group_contact b > where > a.sub_id=b.sub_id; > > GROUPSMS=# select * from test2; > sub_id | group_id | contact_id > --------+----------+------------ > 2 | 2 | 3 > (1 row) > > > select a.*,b.* from test1 a ,test2 b > where b.sub_id=a.sub_id; > > GROUPSMS-# where b.sub_id=a.sub_id; > sub_id | sub_name | tot_message | con_phone | > sub_total | sub_current | sub_id | group_id | contac > --------+----------+-------------+-----------+-----------+-------------+--------+----------+-------- > 2 | veeren | 35 | 35 | > 250 | 117.25 | 2 | 2 | 3 > (1 row) > > > > > > > Now wht happen is in my final query of test1 > and test2, "total_message" and "con_phone" both > the columns output is changed by 5 times which is a > random value. > > > The same test case i have taken to the "oracle" > there i find the it is working absolutly fine. > Kindly handle this issue because one of major > application at my customor end is running.If find > problem in reading pls check the attachment file. > > > > > Regards > Shashi Bhushan Ahuja > > > > __________________________________________________ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail - only $35 > a year! http://personal.mail.yahoo.com/