CODEWITHSUNDEEP
sqlamazon-redshift
raj247
raj247

Reputation: 401

[Amazon](500310) Invalid operation: invalid reference to FROM-clause entry for table "fact_spv_commissioned_lot";

I have looked at a few similar questions on stackoverflow but none of it helped me. I keep on getting this error no matter what I do.

Amazon Invalid operation: invalid reference to FROM-clause entry for table "fact_spv_commissioned_lot";

SELECT COUNT(*) FROM staging_serials s
  JOIN dim_md_company c ON (c.lsc_company_id = s.companyid)
  JOIN staging_product p ON (s.compositeproductcode = p.compositeproductcode)
  JOIN dim_packaging_level l ON (l.unit_of_measure = p.packaginguom)
WHERE c.sk_company_id = fact_spv_commissioned_lot.sk_company_id
  AND s.lotnumber = fact_spv_commissioned_lot.lot_number
  and p.sk_product_id = fact_spv_commissioned_lot.sk_product_id
  and l.sk_packaging_level_id = fact_spv_commissioned_lot.sk_packaging_level_id

Upvotes: 0

Views: 1095

Answers (1)

DBug
DBug

Reputation: 2566

Because fact_spv_commissioned_lot is not listed in the table clause of the query. You're joining staging_serials, dim_md_company, staging_product, and dim_packaging_level tables. You need to include fact_spv_commissioned_lot table.

Upvotes: 2

Related Questions