Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Y1BX1-0003MX-CS for pgsql-admin@arkaria.postgresql.org; Wed, 17 Dec 2014 10:08:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y1BX0-0006H9-Sj for pgsql-admin@arkaria.postgresql.org; Wed, 17 Dec 2014 10:08:34 +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 1Y1BWz-0006ED-8b for pgsql-admin@postgresql.org; Wed, 17 Dec 2014 10:08:33 +0000 Received: from eu1sys200aog129.obsmtp.com ([207.126.144.200]) by makus.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y1BWv-0008Tb-PL for pgsql-admin@postgresql.org; Wed, 17 Dec 2014 10:08:31 +0000 Received: from mail-wg0-f52.google.com ([74.125.82.52]) (using TLSv1) by eu1sys200aob129.postini.com ([207.126.147.11]) with SMTP ID DSNKVJFWHLP0mMed808NeYegKAtb2MbOc3gs@postini.com; Wed, 17 Dec 2014 10:08:29 UTC Received: by mail-wg0-f52.google.com with SMTP id x12so19642087wgg.11 for ; Wed, 17 Dec 2014 02:08:28 -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:mime-version:thread-index:date:message-id :subject:to:content-type; bh=IcI+bW8F1sWMHosiZjzm7GA1aA7rSZSWCIh1PMj98EM=; b=JpYnzr5WVBonBdTu64uNEIFxcgG0YVoN8IgTgjN4qttGdcNxJOZu1XehIzr0NuUtIi n/3ZRGkgAB2oF87VpMegNY9mF+lvauwYjr2kghrbN1wkawM9UaThtN8B/IyGKWqDxmyE mzBuR1L9SOLJVw6c8GHlfUlUpYXb5rAon5wQhMmBTHrs+5yIrsF3Wc9bKmS2OzEZzuxU hjXgWum2LVmtYkD9Pa87z+SjjHrFwJIPCPaPtWnkDNA/fTly8Kn3YafmnoP9b3R6kR85 p9cUKPPON3lKO4w6A5ObzsutMS2QYL9r76FjiGP5vPXoFNI6Iw0wZbSBudmpfh9SIihX U7Vw== X-Gm-Message-State: ALoCoQlxsqy7fk2ftKmbhH2LZ2udEeasJHnHj+3SEf095gRXHPW5ESYec/jxA4K3Ikkf0IfDs4ztsD3/2+3kUmEvY6Coc/YWb0SJnHp8lKdQ+7Xdyu91488xVscJtl+UBjDXl90OiQjK/MBPMUj5AAL0BQFta246dw== X-Received: by 10.194.92.116 with SMTP id cl20mr71718809wjb.71.1418810908195; Wed, 17 Dec 2014 02:08:28 -0800 (PST) X-Received: by 10.194.92.116 with SMTP id cl20mr71718763wjb.71.1418810907935; Wed, 17 Dec 2014 02:08:27 -0800 (PST) From: Khangelani Gama MIME-Version: 1.0 X-Mailer: Microsoft Outlook 14.0 Thread-Index: AdAZ4R2Zj4ZHdgtKStW3ED7U2UdJ4g== Date: Wed, 17 Dec 2014 12:08:26 +0200 Message-ID: <93a59f416c27e4a0f50e82f29968ab1e@mail.gmail.com> Subject: postgres 9.1 - querie problem using a dblink To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=047d7bd910c22bdbfc050a66a874 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 --047d7bd910c22bdbfc050a66a874 Content-Type: text/plain; charset=UTF-8 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=107 then a.value else 0 end) as mtd_sog, sum(case when b.value_type=110 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=c.cdbs_br_cde and b.cdbs_grp_cde=c.cdbs_grp_cde and b.value_type=c.value_type and c.value_date::date=sysdate::date -interval '1 day') where 1=1 --and a.cdbs_br_cde='8008' and a.cdbs_grp_cde='1~7~1' and a.fpp_cde='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=IP port=3456 dbname=c8008'; CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. --047d7bd910c22bdbfc050a66a874 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hi

=C2=A0

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

=C2=A0

The DATABASE LINK is called cen_8008.

=C2=A0

=C2=A0

sele= ct 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,valu= e_type)

--left join eis_daily_sales@cen_8008 c o= n=C2=A0 (b.cdbs_br_cde=3Dc.cdbs_br_cde and b.cdbs_grp_cde=3Dc.cdbs_grp_cde = and b.value_type=3Dc.value_type and c.value_date::date=3Dsysdate::date -int= erval '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;

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=C2=A0

exa= mple on how I created the dblink for cent8008

=C2= =A0

CREATE public DATABASE LINK cen_8008 CONNECT = TO user IDENTIFIED BY 'passwd' USING libpq 'host=3DIP port=3D34= 56 dbname=3Dc8008';

=C2=A0

=C2=A0

=C2=A0=

=C2=A0

=C2=A0

<= /p>

=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
--047d7bd910c22bdbfc050a66a874--