Re: Problem with sql

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/