Cfrgtkky

I have a problem with my select query

select distinct l.*, tmp_addr.address from l.lic as l 

left join l.hou as h on l.lic_id=h.lic_guid 

left join l.org as o on o.org_id=l.org_guid

LEFT JOIN (

SELECT address

    FROM dblink('sm_d','SELECT * FROM 

sm.func_get(' || COALESCE(QUOTE_LITERAL(h.hguid), 'NULL') || ',  TRUE, ''sm'')')

                AS addr(full_address TEXT, address_guid TEXT)

) AS tmp_addr ON h.house_address_guid=tmp_addr.address_guid

where o.root_organization_ref_guid='b26961ae-f015' and l.status <> 'INACTIVE' and l.version='true' and h.registry_status_type='INCLUDED';

SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table "h"
There is an entry for table "h", but it cannot be referenced from this part of the query.
Position: 348

This seems really weird:

select distinct l.*, tmp_addr.address 

FROM l.lic as l 

left join l.hou as h on l.lic_id=h.lic_guid 

left join l.org as o on o.org_id=l.org_guid

l.lic as l -- You are setting an alias for LIC table as l

left join l.hou as h -- Now, when you say l.hou, it means hou is a column from table l. But you are using it as a table.

left join l.org as o -- Same problem as above.

So, if l is a schema in your database where these tables are stored, I suggest you to use another alias name than l.

If l is not a schema, just remove l from l.hou and l.org. Instead say, hou and org.

This should fix the error. Let me know if this helps.