Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iAIuK-0003FX-4O for pgsql-admin@arkaria.postgresql.org; Tue, 17 Sep 2019 19:13:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iAIuH-0004O5-Tg for pgsql-admin@arkaria.postgresql.org; Tue, 17 Sep 2019 19:12:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iAIuH-0004Nm-HO for pgsql-admin@lists.postgresql.org; Tue, 17 Sep 2019 19:12:57 +0000 Received: from mail-oi1-x22d.google.com ([2607:f8b0:4864:20::22d]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iAIuF-0007uo-1t for pgsql-admin@lists.postgresql.org; Tue, 17 Sep 2019 19:12:57 +0000 Received: by mail-oi1-x22d.google.com with SMTP id i16so3883533oie.4 for ; Tue, 17 Sep 2019 12:12:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=p0wytbqmti0zVKCQbWb29VAEc39JhzdFwv7k1+7zlNM=; b=JuGegjEBvcdLORhAuCWv3HknYVaVP4Ix+B7Y9ixARIpLo5lIAcgdEbE1m7ya5MX2o+ DDLHkTom3TgzbzFUSQ9d9LF8SzSCdgKzC37OfZZ2wTLNJjLgYHISx7SNErHbZ24tBFpf wR587nk4rwluky38/527mBRSvLPhmCr/oIDe7EAonnKgStW+0x7DBHr1myMUbmW4ce4x shccnO8+Ec32c0+gyoxuK1LAikePt7dfCuqRdUDEUrffcADoKy7GS2GIa/zomCULgwuL WnZ4r2ZP7Ii9k/5vcGBKfV2hUqEtUZlNnbRBRAjrJcg22FckAf3Z6n9X/ApAi7XYGe3w Sp0A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=p0wytbqmti0zVKCQbWb29VAEc39JhzdFwv7k1+7zlNM=; b=ePc85KR/q2NZI7P57/msByjl44Ut47OsmvlgEAsfODqIqGgze0xcL73nengLeWQR1z ecsgJWy+ipvQMKnQiqv6UZSfH4oC8ayO1UgPixMqbLFMHa7sv9A8nUPVeVHCDS5g0/E7 GSXZ6RJ0RWt+SWzW7f3nK+2LYUnmzFEzKKMIPZt+b0G/j+gDA5fug+Kjhv5jKeT8iE3j Lrcnb8WBFk2IZSBxoNkRoJc0oieUoVwoqle93aUtPpeK49m93WE36Q+KvEas3HRzJr81 DDgGcmJs0DmpSoiII9vJ9mng2fw4C8MeY7sUXvrT4v69Mh/X812/DltVqcgGFYe4qRwR X3SQ== X-Gm-Message-State: APjAAAV3gLJPd6lecUn3CQ/Q3x24u9eaS1s5iN6wHQQ7woXEIDuMwgz6 BQiO217Ithm+dc8sqGZLRk2DQqN21C6rDw== X-Google-Smtp-Source: APXvYqwgmbgpeEaE4CdyeFiWbZLngjOIF7Op9WdBhYXjzNRNcnF/kb7CybDkQkCnc55SkGp/CXkODg== X-Received: by 2002:aca:59c6:: with SMTP id n189mr5169706oib.127.1568747572547; Tue, 17 Sep 2019 12:12:52 -0700 (PDT) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id t6sm981458oij.39.2019.09.17.12.12.51 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 17 Sep 2019 12:12:51 -0700 (PDT) Subject: Re: create view To: pgsql-admin@lists.postgresql.org References: <1896838771.5876030.1568744001412.ref@mail.yahoo.com> <1896838771.5876030.1568744001412@mail.yahoo.com> From: Ron Message-ID: Date: Tue, 17 Sep 2019 14:12:50 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.8.0 MIME-Version: 1.0 In-Reply-To: <1896838771.5876030.1568744001412@mail.yahoo.com> Content-Type: multipart/alternative; boundary="------------EE719BC403DB80AD7DE87A9B" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------EE719BC403DB80AD7DE87A9B Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 9/17/19 1:13 PM, Pepe TD Vo wrote: > Hello experts, > > I need to create view from schema's tables and when I run I get an error: > > CREATE or REPLACE view "ECISDRDM"."BENE_VW" ( > receipt_number, > service_center, [snip] > ERROR:  column ap.application_id does not exist > LINE 50: INNER JOIN "ECISDRDM"."BNFT_CURR_FACT"bcf ON (ap.applicatio... >                         ^ > SQL state: 42703 > Character: 864 [snip] > I do a simple select query of application_cdim table, the result is fine: > > Select * > FROM "ECISDRDM"."APPLICATION_CDIM" ap When you have a weird error on a long or statement, simplify, simplify, simplify. Make a simple test view that's just on "ECISDRDM"."APPLICATION_CDIM".  Then make another test view that's just a join between those two tables.  Simplify everything as much as possible be only selecting a few columns, etc. That might show where your error is. -- Angular momentum makes the world go 'round. --------------EE719BC403DB80AD7DE87A9B Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 9/17/19 1:13 PM, Pepe TD Vo wrote:
Hello experts,

I need to create view from schema's tables and when I run I get an error:

CREATE or REPLACE view "ECISDRDM"."BENE_VW" (
receipt_number,
service_center,
[snip]
ERROR:  column ap.application_id does not exist
LINE 50: INNER JOIN "ECISDRDM"."BNFT_CURR_FACT" bcf ON (ap.applicatio...
                                                        ^
SQL state: 42703
Character: 864

[snip]
I do a simple select query of application_cdim table, the result is fine:

Select *
FROM  "ECISDRDM"."APPLICATION_CDIM" ap

When you have a weird error on a long or statement, simplify, simplify, simplify.

Make a simple test view that's just on "ECISDRDM"."APPLICATION_CDIM".  Then make another test view that's just a join between those two tables.  Simplify everything as much as possible be only selecting a few columns, etc.

That might show where your error is.

--
Angular momentum makes the world go 'round.
--------------EE719BC403DB80AD7DE87A9B--