Re: invalid reference to FROM-clause entry for table

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Sam Stearns <[email protected]>
To: Adw Spe <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Peter Garza <[email protected]>
Cc: Henry Ashu <[email protected]>
Subject: Re: invalid reference to FROM-clause entry for table
Date: Fri, 4 Oct 2024 08:12:49 -0700
Message-ID: <CAN6TVjn_Vg=sQKXJCrFyzRiEksiC0CAO3pzBk=HJ6gFJjW9cdA@mail.gmail.com> (raw)
In-Reply-To: <LV8P221MB1267DAD1CB3347A560989811A2722@LV8P221MB1267.NAMP221.PROD.OUTLOOK.COM>
References: <CAN6TVj=Kn0b1a5NDujb8rgWG8jDJhGLMWwCW-ZgQEuM=vND=dA@mail.gmail.com>
	<LV8P221MB1267DAD1CB3347A560989811A2722@LV8P221MB1267.NAMP221.PROD.OUTLOOK.COM>

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 AM Adw Spe <[email protected]>
wrote:

> Object “O” is only referenceable within the *scope* of “alias6”.  Outside
> of the open and closed parentheses that define the alias6 subquery, “O”
> doesn’t exist.  Also, subquery “O” doesn’t have a SELECT keyword.  Maybe
> I’m wrong, but I think every subquery must at least have a SELECT-FROM pair.
>
>
>
> *From:* Sam Stearns <[email protected]>
> *Sent:* Thursday, October 3, 2024 5:15 PM
> *To:* [email protected]
> *Cc:* Peter Garza <[email protected]>; Henry Ashu <[email protected]>
> *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_miles,
> 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_CITY,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.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_MILES,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.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.INCLUDE_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_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_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=d.user_id)
>         inner join office o on (u.officeid=o.officeid)
>         left outer join login l on (l.userid=u.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 from
> 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
> <https://url.us.m.mimecastprotect.com/s/RoVnCM8X14TJgBzoSwfwF89v5d?domain=dat.com;
>
> [image: Image removed by sender. DAT]
> <https://url.us.m.mimecastprotect.com/s/Imx-CNkG04T6AkjnH4hXFykruy?domain=dat.com;
>


-- 

*Samuel Stearns*
Lead Database Administrator
*c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com
[image: DAT]
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link;


Attachments:

  [image/jpeg] ~WRD3354.jpg (823B, 3-~WRD3354.jpg)
  download | view image

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: invalid reference to FROM-clause entry for table
  In-Reply-To: <CAN6TVjn_Vg=sQKXJCrFyzRiEksiC0CAO3pzBk=HJ6gFJjW9cdA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox