public inbox for [email protected]
help / color / mirror / Atom feedinvalid reference to FROM-clause entry for table
5+ messages / 4 participants
[nested] [flat]
* invalid reference to FROM-clause entry for table
@ 2024-10-03 22:14 Sam Stearns <[email protected]>
2024-10-03 22:21 ` Re: invalid reference to FROM-clause entry for table Tom Lane <[email protected]>
2024-10-04 13:27 ` RE: invalid reference to FROM-clause entry for table Adw Spe <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Sam Stearns @ 2024-10-03 22:14 UTC (permalink / raw)
To: [email protected]; +Cc: Peter Garza <[email protected]>; Henry Ashu <[email protected]>
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: invalid reference to FROM-clause entry for table
2024-10-03 22:14 invalid reference to FROM-clause entry for table Sam Stearns <[email protected]>
@ 2024-10-03 22:21 ` Tom Lane <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2024-10-03 22:21 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; +Cc: [email protected]; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
Sam Stearns <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: invalid reference to FROM-clause entry for table
2024-10-03 22:14 invalid reference to FROM-clause entry for table Sam Stearns <[email protected]>
@ 2024-10-04 13:27 ` Adw Spe <[email protected]>
2024-10-04 15:12 ` Re: invalid reference to FROM-clause entry for table Sam Stearns <[email protected]>
2024-10-05 02:35 ` Re: invalid reference to FROM-clause entry for table David G. Johnston <[email protected]>
1 sibling, 2 replies; 5+ messages in thread
From: Adw Spe @ 2024-10-04 13:27 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; [email protected] <[email protected]>; +Cc: Peter Garza <[email protected]>; Henry Ashu <[email protected]>
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 removed by sender. DAT]<https://url.us.m.mimecastprotect.com/s/Imx-CNkG04T6AkjnH4hXFykruy?domain=dat.com;
Attachments:
[image/jpeg] ~WRD3354.jpg (823B, 3-~WRD3354.jpg)
download | view image
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: invalid reference to FROM-clause entry for table
2024-10-03 22:14 invalid reference to FROM-clause entry for table Sam Stearns <[email protected]>
2024-10-04 13:27 ` RE: invalid reference to FROM-clause entry for table Adw Spe <[email protected]>
@ 2024-10-04 15:12 ` Sam Stearns <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Sam Stearns @ 2024-10-04 15:12 UTC (permalink / raw)
To: Adw Spe <[email protected]>; +Cc: [email protected] <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: invalid reference to FROM-clause entry for table
2024-10-03 22:14 invalid reference to FROM-clause entry for table Sam Stearns <[email protected]>
2024-10-04 13:27 ` RE: invalid reference to FROM-clause entry for table Adw Spe <[email protected]>
@ 2024-10-05 02:35 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2024-10-05 02:35 UTC (permalink / raw)
To: Adw Spe <[email protected]>; +Cc: Sam Stearns <[email protected]>; [email protected] <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
On Friday, October 4, 2024, Adw Spe <[email protected]> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-10-05 02:35 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-03 22:14 invalid reference to FROM-clause entry for table Sam Stearns <[email protected]>
2024-10-03 22:21 ` Tom Lane <[email protected]>
2024-10-04 13:27 ` Adw Spe <[email protected]>
2024-10-04 15:12 ` Sam Stearns <[email protected]>
2024-10-05 02:35 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox