Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1swjzT-005noE-LZ for pgsql-sql@arkaria.postgresql.org; Fri, 04 Oct 2024 15:13:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1swjzS-001GOJ-Si for pgsql-sql@arkaria.postgresql.org; Fri, 04 Oct 2024 15:13:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1swjzS-001GOA-Ge for pgsql-sql@lists.postgresql.org; Fri, 04 Oct 2024 15:13:10 +0000 Received: from mx0a-0039f802.pphosted.com ([205.220.164.45]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1swjzM-002Wdf-UL for pgsql-sql@lists.postgresql.org; Fri, 04 Oct 2024 15:13:09 +0000 Received: from pps.filterd (m0209981.ppops.net [127.0.0.1]) by mx0b-0039f802.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 494603Z1018058 for ; Fri, 4 Oct 2024 08:13:03 -0700 Received: from mail-yw1-f197.google.com (mail-yw1-f197.google.com [209.85.128.197]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 42204e92cp-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Fri, 04 Oct 2024 08:13:03 -0700 (PDT) Received: by mail-yw1-f197.google.com with SMTP id 00721157ae682-6e25a6fddb0so42602567b3.0 for ; Fri, 04 Oct 2024 08:13:02 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1728054782; x=1728659582; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=sYqLZfZEOYIIbUvE+5II/KVc7i2c42yVotfqbgShExY=; b=ClzvEE83gI0QnOnlIgQ7DIcAxxX5WGWvaWFyrQ+OiTFbORYJpK0SbnitouVdRlOp+i XAcVTsvObOeBUg93/Xel1hQp0s1n0NhyB7RSTeo3GK3v4aklTrX/hTkX1C/TOrNWIpp1 IpcPAJONMbe5TV7dFFVF/YJxixGekA5MBPNUWXCFJKsEgPPim0rbZIPwePIebW1Ynego zwEp+ZJhLKeqQN7vqr3pyrixDAp3wM5rzU9EqIYuNasZHPfOuPJ9LMk/WZFFtJpDHa94 hfxPB8sFwRzSIhWkUC7b3yn6FOe2VAWeY8r8UkcqrNltFDhX5XsuqDv/fXCizywC2NhZ NuZw== X-Gm-Message-State: AOJu0YweNx6Fa4cZrNDCfFK1H4aTeepbEkwSbS28zFj2QjkAtvKuC6Nw Py7gtrTQZsSODp/0Ldb/hhT4moIPMfufp5WuorFD07q17XD540TgdYxX2Iyk+KGy3vuZNZhRrKO XzlHIhJHpAbT7wtWEbWi5t0p4OnLKx6fwlPVwqjbUAFjEyxlRSOPc3+RDaTEbSPb/i88JsGj15D W0NROFvvHaAhIsfQSSeG8RY8mIcCdyhM1tyF1ltjMn/HukMUHfkmSkHJgh1Bmf3TxqHyYR6NKQ X-Received: by 2002:a05:690c:3149:b0:615:1a0:78ea with SMTP id 00721157ae682-6e2c7293a4emr23212857b3.34.1728054781635; Fri, 04 Oct 2024 08:13:01 -0700 (PDT) X-Google-Smtp-Source: AGHT+IHgjfBxy5ey1r9v5QLm7QwDinW77cfqfBkobEKNW3XWcMtR6uqPbco3kFlgJfehHT2l1i/l/h1Ft9q2ATbxxT4= X-Received: by 2002:a05:690c:3149:b0:615:1a0:78ea with SMTP id 00721157ae682-6e2c7293a4emr23212267b3.34.1728054780814; Fri, 04 Oct 2024 08:13:00 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Sam Stearns Date: Fri, 4 Oct 2024 08:12:49 -0700 Message-ID: Subject: Re: invalid reference to FROM-clause entry for table To: Adw Spe Cc: "pgsql-sql@lists.postgresql.org" , Peter Garza , Henry Ashu Content-Type: multipart/related; boundary="0000000000005d3cc70623a81b24" X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1051,Hydra:6.0.680,FMLib:17.12.62.30 definitions=2024-10-04_12,2024-10-04_01,2024-09-30_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005d3cc70623a81b24 Content-Type: multipart/alternative; boundary="0000000000005d3cc60623a81b23" --0000000000005d3cc60623a81b23 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you all for the help! I removed alias alias6 which appears to have done the trick. Thanks, Sam On Fri, Oct 4, 2024 at 6:27=E2=80=AFAM Adw Spe wrote: > Object =E2=80=9CO=E2=80=9D is only referenceable within the *scope* of = =E2=80=9Calias6=E2=80=9D. Outside > of the open and closed parentheses that define the alias6 subquery, =E2= =80=9CO=E2=80=9D > doesn=E2=80=99t exist. Also, subquery =E2=80=9CO=E2=80=9D doesn=E2=80=99= t have a SELECT keyword. Maybe > I=E2=80=99m wrong, but I think every subquery must at least have a SELECT= -FROM pair. > > > > *From:* Sam Stearns > *Sent:* Thursday, October 3, 2024 5:15 PM > *To:* pgsql-sql@lists.postgresql.org > *Cc:* Peter Garza ; Henry Ashu > *Subject:* invalid reference to FROM-clause entry for table > > > > *EXTERNAL EMAIL* > > > > Howdy, > > > > This one is really doing my head in: > > > > CREATE OR REPLACE VIEW dash_detail_fme (crm_account_id, crm_company_id, > login_id, fme_id, fme_type, user_id, start_date, end_date, ltl, comment1, > comment2, display_enhancements, look_count, origin_city, destination_city= , > destination_states, origin_states, equipment_type, active, > source_application, volume_amount, volume_unit, alarm_id, when_created, > length_feet, refresh_count, base_rate, rate_based_on, group_id, > preferred_callback_method, aggregate_business_days, take_count, trip_mile= s, > posters_reference_id, alarm_exact_match_count, alarm_similar_match_count, > alarm_take_count, destination_radius_miles, age_limit_minutes, > origin_radius_miles, who_created, update_count, alarm_match_count, > basis_search_id, basis_asset_id, business_days, row_last_updated, > equipment_classes, include_ltls, include_fulls, asset_count, commodity, > credit_score, days_to_pay, tia_member, p3_membership_level, edit_count, > earliest_availability, latest_availability, asset_type, > customer_directory_id, weight_pounds, extended_network, > basis_asset_posters_ref_id, origin_postal_code, destination_postal_code, > exact_match_count, similar_match_count) AS SELECT > o.crmaccountid AS crm_account_id, > o.crmcompanyid AS crm_company_id, > CASE when l.loginid is not null > then l.loginid > when u.crmSubaccountId is not null > then concat('LEGACY-SUB-', COALESCE(trim(both > u.crmSubaccountId), '--')) > else 'UNKNOWN' > END AS login_id, > > d.FME_ID,d.FME_TYPE,d.USER_ID,d.START_DATE,d.END_DATE,d.LTL,d.COMMENT1,d.= COMMENT2,d.DISPLAY_ENHANCEMENTS,d.LOOK_COUNT,d.ORIGIN_CITY,d.DESTINATION_CI= TY,d.DESTINATION_STATES,d.ORIGIN_STATES,d.EQUIPMENT_TYPE,d.ACTIVE,d.SOURCE_= APPLICATION,d.VOLUME_AMOUNT,d.VOLUME_UNIT,d.ALARM_ID,d.WHEN_CREATED,d.LENGT= H_FEET,d.REFRESH_COUNT,d.BASE_RATE,d.RATE_BASED_ON,d.GROUP_ID,d.PREFERRED_C= ALLBACK_METHOD,d.AGGREGATE_BUSINESS_DAYS,d.TAKE_COUNT,d.TRIP_MILES,d.POSTER= S_REFERENCE_ID,d.ALARM_EXACT_MATCH_COUNT,d.ALARM_SIMILAR_MATCH_COUNT,d.ALAR= M_TAKE_COUNT,d.DESTINATION_RADIUS_MILES,d.AGE_LIMIT_MINUTES,d.ORIGIN_RADIUS= _MILES,d.WHO_CREATED,d.UPDATE_COUNT,d.ALARM_MATCH_COUNT,d.BASIS_SEARCH_ID,d= .BASIS_ASSET_ID,d.BUSINESS_DAYS,d.ROW_LAST_UPDATED,d.EQUIPMENT_CLASSES,d.IN= CLUDE_LTLS,d.INCLUDE_FULLS,d.ASSET_COUNT,d.COMMODITY,d.CREDIT_SCORE,d.DAYS_= TO_PAY,d.TIA_MEMBER,d.P3_MEMBERSHIP_LEVEL,d.EDIT_COUNT,d.EARLIEST_AVAILABIL= ITY,d.LATEST_AVAILABILITY,d.ASSET_TYPE,d.CUSTOMER_DIRECTORY_ID,d.WEIGHT_POU= NDS,d.EXTENDED_NETWORK,d.BASIS_ASSET_POSTERS_REF_ID,d.ORIGIN_POSTAL_CODE,d.= DESTINATION_POSTAL_CODE,d.EXACT_MATCH_COUNT,d.SIMILAR_MATCH_COUNT > FROM (pud_fme_data d > inner join csbuser u on (u.userid=3Dd.user_id) > inner join office o on (u.officeid=3Do.officeid) > left outer join login l on (l.userid=3Du.userid) ) alias6; > > > > > > ERROR: invalid reference to FROM-clause entry for table "o" > LINE 2: o.crmaccountid AS crm_account_id, > ^ > DETAIL: There is an entry for table "o", but it cannot be referenced fro= m > this part of the query. > > > > > > Any help would be greatly appreciated. > > > > Thanks, > > > > Sam > > > > -- > > *Samuel Stearns* > Lead Database Administrator > *c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com > > > [image: Image removed by sender. DAT] > > --=20 *Samuel Stearns* Lead Database Administrator *c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com [image: DAT] --0000000000005d3cc60623a81b23 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you all for the help!=C2=A0 I removed alias alias6 w= hich appears to have done the trick.

Thanks,
<= br>
Sam


=
On Fri, Oct 4, 2024 at 6:27=E2=80=AFA= M Adw Spe <h.3stnc4kdb= em6q_8p@outlook.com> wrote:

Object =E2=80=9CO=E2=80=9D is only referenceable wit= hin the scope of =E2=80=9Calias6=E2=80=9D.=C2=A0 Outside of the open= and closed parentheses that define the alias6 subquery, =E2=80=9CO=E2=80= =9D doesn=E2=80=99t exist.=C2=A0 Also, subquery =E2=80=9CO=E2=80=9D doesn= =E2=80=99t have a SELECT keyword.=C2=A0 Maybe I=E2=80=99m wrong, but I think every subquery must at least have a SELECT-FROM pair.

=C2=A0

From: Sam Stearns <sam.stearns@dat.com>
Sent: Thursday, October 3, 2024 5:15 PM
To: pgsql-sql@lists.postgresql.org
Cc: Peter Garza <peter.garza@dat.com>; Henry Ashu <henry.ashu@dat.com>
Subject: invalid reference to FROM-clause entry for table<= /u>

=C2=A0

EXTERNAL EMAIL

=C2=A0

Howdy,

=C2=A0

This one is really doing my head in:

=C2=A0

CREATE OR REPLACE VIEW dash_detail_fme (crm_account_= id, crm_company_id, login_id, fme_id, fme_type, user_id, start_date, end_da= te, ltl, comment1, comment2, display_enhancements, look_count, origin_city,= destination_city, destination_states, origin_states, equipment_type, active, source_application, volume_amount, = volume_unit, alarm_id, when_created, length_feet, refresh_count, base_rate,= rate_based_on, group_id, preferred_callback_method, aggregate_business_day= s, take_count, trip_miles, posters_reference_id, alarm_exact_match_count, alarm_similar_match_count, alarm_take_count, dest= ination_radius_miles, age_limit_minutes, origin_radius_miles, who_created, = update_count, alarm_match_count, basis_search_id, basis_asset_id, business_= days, row_last_updated, equipment_classes, include_ltls, include_fulls, asset_count, commodity, credit_score, days_to= _pay, tia_member, p3_membership_level, edit_count, earliest_availability, l= atest_availability, asset_type, customer_directory_id, weight_pounds, exten= ded_network, basis_asset_posters_ref_id, origin_postal_code, destination_postal_code, exact_match_count, similar_ma= tch_count) AS SELECT
=C2=A0 =C2=A0 o.crmaccountid AS crm_account_id,
=C2=A0 =C2=A0 o.crmcompanyid AS crm_company_id,
=C2=A0 =C2=A0 CASE when l.loginid is not null
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0then l.loginid
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0when u.crmSubaccountId is not null
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0then concat('LEGACY-SUB-',= COALESCE(trim(both u.crmSubaccountId), '--'))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0else 'UNKNOWN'
=C2=A0 =C2=A0 END AS login_id,
=C2=A0 =C2=A0 d.FME_ID,d.FME_TYPE,d.USER_ID,d.START_DATE,d.END_DATE,d.LTL,d= .COMMENT1,d.COMMENT2,d.DISPLAY_ENHANCEMENTS,d.LOOK_COUNT,d.ORIGIN_CITY,d.DE= STINATION_CITY,d.DESTINATION_STATES,d.ORIGIN_STATES,d.EQUIPMENT_TYPE,d.ACTI= VE,d.SOURCE_APPLICATION,d.VOLUME_AMOUNT,d.VOLUME_UNIT,d.ALARM_ID,d.WHEN_CRE= ATED,d.LENGTH_FEET,d.REFRESH_COUNT,d.BASE_RATE,d.RATE_BASED_ON,d.GROUP_ID,d= .PREFERRED_CALLBACK_METHOD,d.AGGREGATE_BUSINESS_DAYS,d.TAKE_COUNT,d.TRIP_MI= LES,d.POSTERS_REFERENCE_ID,d.ALARM_EXACT_MATCH_COUNT,d.ALARM_SIMILAR_MATCH_= COUNT,d.ALARM_TAKE_COUNT,d.DESTINATION_RADIUS_MILES,d.AGE_LIMIT_MINUTES,d.O= RIGIN_RADIUS_MILES,d.WHO_CREATED,d.UPDATE_COUNT,d.ALARM_MATCH_COUNT,d.BASIS= _SEARCH_ID,d.BASIS_ASSET_ID,d.BUSINESS_DAYS,d.ROW_LAST_UPDATED,d.EQUIPMENT_= CLASSES,d.INCLUDE_LTLS,d.INCLUDE_FULLS,d.ASSET_COUNT,d.COMMODITY,d.CREDIT_S= CORE,d.DAYS_TO_PAY,d.TIA_MEMBER,d.P3_MEMBERSHIP_LEVEL,d.EDIT_COUNT,d.EARLIE= ST_AVAILABILITY,d.LATEST_AVAILABILITY,d.ASSET_TYPE,d.CUSTOMER_DIRECTORY_ID,= d.WEIGHT_POUNDS,d.EXTENDED_NETWORK,d.BASIS_ASSET_POSTERS_REF_ID,d.ORIGIN_PO= STAL_CODE,d.DESTINATION_POSTAL_CODE,d.EXACT_MATCH_COUNT,d.SIMILAR_MATCH_COU= NT
=C2=A0 FROM (pud_fme_data d
=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join csbuser u on (u.userid=3Dd.user_id)<= br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join office o on (u.officeid=3Do.officeid= )
=C2=A0 =C2=A0 =C2=A0 =C2=A0 left outer join login l on (l.userid=3Du.userid= ) ) alias6;

=C2=A0

=C2=A0

ERROR: =C2=A0invalid reference to FROM-clause entry = for table "o"
LINE 2: =C2=A0 =C2=A0 o.crmaccountid AS crm_account_id,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^
DETAIL: =C2=A0There is an entry for table "o", but it cannot be r= eferenced from this part of the query.

=C2=A0

=C2=A0

Any help would be greatly appreciated.=

=C2=A0

Thanks,

=C2=A0

Sam

=C2=A0

--

Samuel Stearns
Lead Database Administrator
c:= =C2=A0971 762 6879=C2=A0|=C2=A0o:=C2=A0503 672 5115=C2=A0| DAT.com



--

Samuel Ste= arns
Lead Database Admin= istrator
c:=C2=A0971 762 6879=C2=A0|=C2=A0o:=C2=A0503 672 5115=C2= =A0|=C2=A0DAT.com

<= div style=3D"color:rgb(0,0,0);font-family:"Times New Roman";font-= size:medium">
--0000000000005d3cc60623a81b23-- --0000000000005d3cc70623a81b24 Content-Type: image/jpeg; name="~WRD3354.jpg" Content-Disposition: inline; filename="~WRD3354.jpg" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: ii_192581036739df9befd1 /9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAsLDBkSEw8UHRofHh0a HBwgJC4nICIsIxwcKDcpLDAxNDQ0Hyc5PTgyPC4zNDL/2wBDAQkJCQwLDBgNDRgyIRwhMjIyMjIy MjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjL/wAARCABkAGQDASIA AhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQA AAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3 ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWm p6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEA AwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSEx BhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElK U1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3 uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD3+iii gAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKA CiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAK KKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAoo ooAKKKKACiiigAooooAKKKKACiiigD//2Q== --0000000000005d3cc70623a81b24--