MIME-Version: 1.0
From: Sam Stearns <sam.stearns@dat.com>
Date: Thu, 3 Oct 2024 15:14:46 -0700
Message-ID: 
 <CAN6TVj=Kn0b1a5NDujb8rgWG8jDJhGLMWwCW-ZgQEuM=vND=dA@mail.gmail.com>
Subject: invalid reference to FROM-clause entry for table
To: pgsql-sql@lists.postgresql.org
Cc: Peter Garza <peter.garza@dat.com>, Henry Ashu <henry.ashu@dat.com>
Content-Type: multipart/alternative; boundary="000000000000780e94062399e267"
Archived-At: 
 <https://www.postgresql.org/message-id/CAN6TVj%3DKn0b1a5NDujb8rgWG8jDJhGLMWwCW-ZgQEuM%3DvND%3DdA%40mail.gmail.com>
Precedence: bulk

--000000000000780e94062399e267
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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.CO=
MMENT2,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_AP=
PLICATION,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_CAL=
LBACK_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_M=
ILES,d.WHO_CREATED,d.UPDATE_COUNT,d.ALARM_MATCH_COUNT,d.BASIS_SEARCH_ID,d.B=
ASIS_ASSET_ID,d.BUSINESS_DAYS,d.ROW_LAST_UPDATED,d.EQUIPMENT_CLASSES,d.INCL=
UDE_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_AVAILABILIT=
Y,d.LATEST_AVAILABILITY,d.ASSET_TYPE,d.CUSTOMER_DIRECTORY_ID,d.WEIGHT_POUND=
S,d.EXTENDED_NETWORK,d.BASIS_ASSET_POSTERS_REF_ID,d.ORIGIN_POSTAL_CODE,d.DE=
STINATION_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 from
this part of the query.


Any help would be greatly appreciated.

Thanks,

Sam

--=20

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

--000000000000780e94062399e267
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Howdy,<div><br></div><div>This one is really doing my head=
 in:</div><div><br></div><div>CREATE OR REPLACE VIEW dash_detail_fme (crm_a=
ccount_id, crm_company_id, login_id, fme_id, fme_type, user_id, start_date,=
 end_date, ltl, comment1, comment2, display_enhancements, look_count, origi=
n_city, destination_city, destination_states, origin_states, equipment_type=
, active, source_application, volume_amount, volume_unit, alarm_id, when_cr=
eated, length_feet, refresh_count, base_rate, rate_based_on, group_id, pref=
erred_callback_method, aggregate_business_days, take_count, trip_miles, pos=
ters_reference_id, alarm_exact_match_count, alarm_similar_match_count, alar=
m_take_count, destination_radius_miles, age_limit_minutes, origin_radius_mi=
les, who_created, update_count, alarm_match_count, basis_search_id, basis_a=
sset_id, business_days, row_last_updated, equipment_classes, include_ltls, =
include_fulls, asset_count, commodity, credit_score, days_to_pay, tia_membe=
r, p3_membership_level, edit_count, earliest_availability, latest_availabil=
ity, asset_type, customer_directory_id, weight_pounds, extended_network, ba=
sis_asset_posters_ref_id, origin_postal_code, destination_postal_code, exac=
t_match_count, similar_match_count) AS SELECT<br>=C2=A0 =C2=A0 o.crmaccount=
id AS crm_account_id,<br>=C2=A0 =C2=A0 o.crmcompanyid AS crm_company_id,<br=
>=C2=A0 =C2=A0 CASE when l.loginid is not null<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0then l.loginid<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0when u=
.crmSubaccountId is not null<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0th=
en concat(&#39;LEGACY-SUB-&#39;, COALESCE(trim(both u.crmSubaccountId), =
9;--&#39;))<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0else &#39;UNKNOWN&#=
39;<br>=C2=A0 =C2=A0 END AS login_id,<br>=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_ENH=
ANCEMENTS,d.LOOK_COUNT,d.ORIGIN_CITY,d.DESTINATION_CITY,d.DESTINATION_STATE=
S,d.ORIGIN_STATES,d.EQUIPMENT_TYPE,d.ACTIVE,d.SOURCE_APPLICATION,d.VOLUME_A=
MOUNT,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.AGGRE=
GATE_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.DESTINA=
TION_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.BUSI=
NESS_DAYS,d.ROW_LAST_UPDATED,d.EQUIPMENT_CLASSES,d.INCLUDE_LTLS,d.INCLUDE_F=
ULLS,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_AVAILABIL=
ITY,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_COD=
E,d.EXACT_MATCH_COUNT,d.SIMILAR_MATCH_COUNT<br>=C2=A0 FROM (pud_fme_data d<=
br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join csbuser u on (u.userid=3Dd.user_i=
d)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join office o on (u.officeid=3Do.of=
ficeid)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 left outer join login l on (l.userid=
=3Du.userid) ) alias6;</div><div><br></div><div><br></div><div>ERROR: =C2=
=A0invalid reference to FROM-clause entry for table &quot;o&quot;<br>LINE 2=
: =C2=A0 =C2=A0 o.crmaccountid AS crm_account_id,<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 ^<br>DETAIL: =C2=A0There is an entry for table &quot;o=
&quot;, but it cannot be referenced from this part of the query.</div><div>=
<br></div><div><br></div><div>Any help would be greatly appreciated.</div><=
div><br></div><div>Thanks,</div><div><br></div><div>Sam<br clear=3D"all"><d=
iv><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=
=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_signature"><div =
dir=3D"ltr"><p style=3D"color:rgb(0,0,0);font-family:Gotham,&quot;Helvetica=
 Neue&quot;,Helvetica,Arial,sans-serif;font-size:0.8rem;line-height:1.2rem"=
><strong style=3D"font-size:0.9rem;color:rgb(51,51,51)">Samuel Stearns</str=
ong><br><span style=3D"color:rgb(102,102,102)">Lead Database Administrator<=
br><strong>c:</strong>=C2=A0971 762 6879=C2=A0<span style=3D"color:rgb(147,=
149,152)">|</span>=C2=A0<strong>o:</strong>=C2=A0503 672 5115=C2=A0<span st=
yle=3D"color:rgb(147,149,152)">|</span>=C2=A0DAT.com</span></p><div style=
=3D"color:rgb(0,0,0);font-family:&quot;Times New Roman&quot;;font-size:medi=
um"><a href=3D"https://www.dat.com/?utm_medium=3Demail&amp;utm_source=3DDAT=
_email_signature_link" target=3D"_blank"><img src=3D"https://www.dat.com/wp=
-content/uploads/media/images/global/emails/signature2/dat_126.png" alt=3D"=
DAT" width=3D"126" height=3D"54"></a></div></div></div></div></div>

--000000000000780e94062399e267--