Skip site navigation (1) Skip section navigation (2)

Problem with sql

From: shashi ahuja <shashiahuja(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problem with sql
Date: 2001-05-31 12:44:45
Message-ID: 20010531124445.8872.qmail@web4903.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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/

Attachment: probpostgres.txt
Description: text/plain (4.4 KB)

Responses

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2001-05-31 13:48:50
Subject: REVOKE ... FROM username equals REVOKE ... FROM PUBLIC
Previous:From: PaulDate: 2001-05-31 04:50:35
Subject: Bug?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group