Cross database query not returning wanted result

95 Views Asked by At

I'm trying to get data from another database "Finantial" but the result its returning is not the same as the result I get in the other database with the same code minus the dblinks

Here is the code I'm having issues with:

select trans.accountid ,sum(trans.amount)as montantpaye,loan.amount-sum(trans.amount) as montantrestant,loan.duration-count(trans.transid) as moisrestant
from (SELECT * FROM dblink('user=postgres password=password dbname=finantial', 'select amount,accountid,duration from loan')
    AS loan(amount int,accountid int,duration int)) as loan,
    (SELECT * FROM dblink('user=postgres password=password dbname=finantial', 'select accountid from account')
    AS account(accountid int)) as account,
    (SELECT * FROM dblink('user=postgres password=password dbname=finantial', 'select amount,accountid,ksymbol,transid from trans')
    AS trans(accountid int,amount int,ksymbol varchar,transid int)) as trans
 where trans.ksymbol='UVER' and
        account.accountid=loan.accountid and
        trans.accountid=account.accountid
group by (trans.accountid,loan.amount,loan.duration)

and here's what i get

enter image description here

whereas when I query this data in the database "finantial" with this code :

select trans.accountid, sum(trans.amount)as montant_paye,loan.amount-sum(trans.amount) as montant_restant,loan.duration-count(trans.transid) as moisrestant
from loan, account,trans
 where account.accountid=loan.accountid and ksymbol='UVER' and trans.accountid=account.accountid
group by (trans.accountid,loan.amount,loan.duration)

I get this result :

enter image description here

I would appreciate it if you could tell me what I need to fix in my code.

0

There are 0 best solutions below