Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Y1CW8-0005VX-KD for pgsql-admin@arkaria.postgresql.org; Wed, 17 Dec 2014 11:11:44 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y1CW7-00066X-VX for pgsql-admin@arkaria.postgresql.org; Wed, 17 Dec 2014 11:11:44 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Y1CW6-00066Q-JF for pgsql-admin@postgresql.org; Wed, 17 Dec 2014 11:11:42 +0000 Received: from eu1sys200aog127.obsmtp.com ([207.126.144.176]) by makus.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y1CW2-0001Gf-1H for pgsql-admin@postgresql.org; Wed, 17 Dec 2014 11:11:40 +0000 Received: from mail-wi0-f181.google.com ([209.85.212.181]) (using TLSv1) by eu1sys200aob127.postini.com ([207.126.147.11]) with SMTP ID DSNKVJFk6ATOKXao5VHP1kKcJUzjuqbwfsjU@postini.com; Wed, 17 Dec 2014 11:11:37 UTC Received: by mail-wi0-f181.google.com with SMTP id r20so15618436wiv.14 for ; Wed, 17 Dec 2014 03:11:36 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:references:in-reply-to:mime-version :thread-index:date:message-id:subject:to:content-type; bh=HAhRNyj8CowoOJ90/pB1J34ErJFx40syqnkRh/YVbxg=; b=eOqEnwwOiRsU/WJrwGQFM7vfnth8A/sDmZ8Y6ZdChsl37qkFy0gmcpRjWa+1Hhe7l4 RQ/4wGfuif4+z5UyVpRwnlj7ylwvDPoPNk9E6vheJrEwfB2HiyRW3YFcZ4+T3kHd7w8f /G5oj1EWb5eBpJ8p64qGcodduennPm/l9bWNrHWIS7szswIvNMeVHd5S48yzLekZrcUf UjUidkEogFL5nQs0W4mqMJ7qzCdk/7DKgOpkqMwr+Ku9FLdVhqOZiIn9xnPwQ4S6S5qC m+574cLwct8nFyacoClU86moiTtqggF43Zz5TSyVEa+WWKkd3ahYs9yhMO+ZQ8Vz9lzu Q3VQ== X-Gm-Message-State: ALoCoQmcsttf2IZeio16EZKtvF28kqOZyfs3FiGRg2dXLw1zxiu2uiny4wQ5DtnGxZo/LaOrIq0/pCdfbn2LxdFqiM8ESTC3pRmSP4k8P2fxt3ekWB9XukjHamBpvAqW2nRqZwlhlqBT9LODt7kDeesvFCqXoLX7qw== X-Received: by 10.194.2.75 with SMTP id 11mr60140627wjs.78.1418814696819; Wed, 17 Dec 2014 03:11:36 -0800 (PST) X-Received: by 10.194.2.75 with SMTP id 11mr60140612wjs.78.1418814696714; Wed, 17 Dec 2014 03:11:36 -0800 (PST) From: Khangelani Gama References: 93a59f416c27e4a0f50e82f29968ab1e@mail.gmail.com In-Reply-To: 93a59f416c27e4a0f50e82f29968ab1e@mail.gmail.com MIME-Version: 1.0 X-Mailer: Microsoft Outlook 14.0 Thread-Index: AdAZ4R2Zj4ZHdgtKStW3ED7U2UdJ4gACNuYQ Date: Wed, 17 Dec 2014 13:11:35 +0200 Message-ID: Subject: Re: postgres 9.1 - querie problem using a dblink To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=047d7b3a834c002d1f050a678a7f X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --047d7b3a834c002d1f050a678a7f Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Sorry, I came with this =E2=80=A6the db cent_8008 doesn=E2=80=99t have two= columns cdbs_br_cde,cdbs_grp_cde *From:* Khangelani Gama [mailto:kgama@argility.com] *Sent:* 17 December 2014 12:08 PM *To:* pgsql-admin@postgresql.org *Subject:* postgres 9.1 - querie problem using a dblink Hi Please help, when I run this querie I get an error saying : column "cdbs_br_cde" specified in USING clause does not exist in left table The DATABASE LINK is called cen_8008. select a.cdbs_br_cde as br_cde, sum(case when b.value_type=3D107 then a.value else 0 end) as mtd_sog, sum(case when b.value_type=3D110 then a.value else 0 end) as mtd_cos from eis_mtd_sales@cen_8008 a left join eis_value_types@cen_8008 b using (cdbs_br_cde,cdbs_grp_cde,value_type) --left join eis_daily_sales@cen_8008 c on (b.cdbs_br_cde=3Dc.cdbs_br_cde a= nd b.cdbs_grp_cde=3Dc.cdbs_grp_cde and b.value_type=3Dc.value_type and c.value_date::date=3Dsysdate::date -interval '1 day') where 1=3D1 --and a.cdbs_br_cde=3D'8008' and a.cdbs_grp_cde=3D'1~7~1' and a.fpp_cde=3D'201412' and a.value_type in (107,110) group by a.cdbs_br_cde order by a.cdbs_br_cde; example on how I created the dblink for cent8008 CREATE public DATABASE LINK cen_8008 CONNECT TO user IDENTIFIED BY 'passwd' USING libpq 'host=3DIP port=3D3456 dbname=3Dc8008'; CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addre= ssee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. An= y review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intend= ed addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiari= es distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other tha= n strictly for business purposes. =0D --047d7b3a834c002d1f050a678a7f Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Sorry, I came with this =E2= =80=A6the db cent_8008 doesn=E2=80=99t =C2=A0have two columns cdbs_br_cde,c= dbs_grp_cde

=C2=A0<= /span>

From: Khangelani Gama [mailto:kgama@argility.com]
Sent: 1= 7 December 2014 12:08 PM
To: pgsql-admin@postgresql.org
Subject: postgres 9.1 - = querie problem using a dblink

= =C2=A0

Hi

=C2=A0

Please help, when I run this querie I get an error say= ing : column "cdbs_br_cde" specified in USING clause does not exi= st in left table

=C2=A0

The DATABASE LINK is called cen_8008.

=C2=A0

=C2=A0

select a.cdbs_br_c= de as br_cde,

sum(case when b.value_type=3D107 th= en a.value else 0 end) as mtd_sog,

sum(case when = b.value_type=3D110 then a.value else 0 end) as mtd_cos

from eis_mtd_sales@cen_8008 a

left join ei= s_value_types@cen_8008 b using (cdbs_br_cde,cdbs_grp_cde,value_type)

--left join eis_daily_sales@cen_8008 c on=C2=A0 (b.cdb= s_br_cde=3Dc.cdbs_br_cde and b.cdbs_grp_cde=3Dc.cdbs_grp_cde and b.value_ty= pe=3Dc.value_type and c.value_date::date=3Dsysdate::date -interval '1 d= ay')

where 1=3D1

--= and a.cdbs_br_cde=3D'8008'

and a.cdbs_gr= p_cde=3D'1~7~1'

and a.fpp_cde=3D'201= 412'

and a.value_type in (107,110)

group by a.cdbs_br_cde

order by = a.cdbs_br_cde;

=C2=A0

= =C2=A0

=C2=A0

=C2=A0

=C2=A0

example on how I c= reated the dblink for cent8008

=C2=A0

CREATE public DATABASE LINK cen_8008 CONNECT TO user IDENTIF= IED BY 'passwd' USING libpq 'host=3DIP port=3D3456 dbname=3Dc80= 08';

=C2=A0

=C2=A0<= /p>

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=C2=A0


CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addre=
ssee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. An=
y review, use or dissemination thereof by anyone
other than the intended addressee is prohibited.If you are not the intend=
ed addressee please notify the writer immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiari=
es distance themselves from and accept no liability
for unauthorised use of their e-mail facilities or e-mails sent other tha=
n strictly for business purposes.
=0D
--047d7b3a834c002d1f050a678a7f--