Thread

1. invalid reference to FROM-clause entry for table

   Sam Stearns <sam.stearns@dat.com> — 2024-10-03T22:14:46Z

   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
   [image: DAT]
   <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>
   

2. Re: invalid reference to FROM-clause entry for table

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-10-03T22:21:32Z

   Sam Stearns <sam.stearns@dat.com> writes:
   > This one is really doing my head in:
   >   ...
   >   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.
   
   IIRC, the join alias "alias6" hides any table aliases inside it.
   Leave that off.  Or reference the column as "alias6.crmaccountid".
   (This way might require fooling around with column aliases so that
   crmaccountid is a unique column name within that scope.)
   
   			regards, tom lane
   
   
   
   

3. RE: invalid reference to FROM-clause entry for table

   Adw Spe <h.3stnc4kdbem6q_8p@outlook.com> — 2024-10-04T13:27:31Z

   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 <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
   
   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 removed by sender. DAT]<https://url.us.m.mimecastprotect.com/s/Imx-CNkG04T6AkjnH4hXFykruy?domain=dat.com>
   

4. Re: invalid reference to FROM-clause entry for table

   Sam Stearns <sam.stearns@dat.com> — 2024-10-04T15:12:49Z

   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 <h.3stnc4kdbem6q_8p@outlook.com>
   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 <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
   >
   >
   >
   > *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>
   

5. Re: invalid reference to FROM-clause entry for table

   David G. Johnston <david.g.johnston@gmail.com> — 2024-10-05T02:35:02Z

   On Friday, October 4, 2024, Adw Spe <h.3stnc4kdbem6q_8p@outlook.com> wrote:
   
   > Maybe I’m wrong, but I think every subquery must at least have a
   > SELECT-FROM pair.
   >
   
   You are indeed wrong - at least for PostgreSQL.  No query requires a from
   clause in PostgreSQL.
   
   David J.