RE: invalid reference to FROM-clause entry for table

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(dot)stearns(at)dat(dot)com>
Sent: Thursday, October 3, 2024 5:15 PM
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Cc: Peter Garza <peter(dot)garza(at)dat(dot)com>; Henry Ashu <henry(dot)ashu(at)dat(dot)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>