Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Error while compiling statement: FAILED: SemanticException [Error 10002]: Line 1:54 Invalid column reference 'TotalRecords'

avatar
Expert Contributor

Why can I not use TotalRecords field from sub query in the outer query?

It throws: Error while compiling statement:

FAILED: SemanticException [Error 10002]: Line 1:54 Invalid column reference 'TotalRecords' 

This is what my query looks like:

    SELECT temp.delay, count(*) as filteredRecords , temp.TotalRecords 
    FROM
    (
    select sales_flat_order.created_at as delay, count(*) over() as TotalRecords
    FROM 
    magentodb.sales_flat_order 
    LEFT OUTER JOIN erpdb.dispatch 
    ON 
    sales_flat_order.increment_id == dispatch.order_num 
    where
    TO_DATE(created_at) >= DATE_SUB(current_date(),10)
      )
    As temp
    WHERE
     temp.delay > 1 
    GROUP BY temp.delay
    Order BY temp.delay
3 REPLIES 3

avatar
@Simran Kaur

Seems like TotalRecords is a keyword. Try using TotalRecords_1 and see if it helps.

Thanks and Regards,

Sindhu

avatar
Expert Contributor

I did. That wasn't the issue. I needed to take Total column in the group by clause of outer query. Thanks

avatar

Could you please share the steps that resolved the issue and mark as best answer?

Thanks,

Sindhu