Search code examples
sqlwindow-functionssybase-asa

SQL Correlation error using OVER & PARTITION in SELECT statement

I am getting the following error when I am trying to execute my SQL SELECT statement

Could not execute statement.
Correllation name 'contact' not found
SQLCODE=-142, ODBC 3 State"42S02"
Line 1, Column 1

My code is as follows

Select forename, surname, email, quotedate
From ( SELECT  *, ROW_NUMBER() OVER (PARTITION BY tblQuote.contno ORDER BY quoteno DESC) AS rn
FROM dba.quotehdr as tblQuote left join dba.contact as tblContact on tblQuote.contno = tblContact.contno)q
where rn = 1 and quotedate <=today()-720 and emailbounced = 0 and email is not null and dba.contact.statusflag = 'A'
order by quotedate desc

This error only happended when I added in

dba.contact.statusflag = 'A'

I have tried this as 

tblContact.statusflag = 'A'

and I get the same error!

Any suggestions?

Solution

  • (What about q.statusflag = 'A' , as it seems you are using q as an Alias.) This original answer is not correct, amended to:

    @Shannon Severance is correct in his comment. You are trying to use the Where clause on the outer query - which does not contain any fields from the contact table. Let me tidy your query to help you see your subquery (q) - as:

    Select 
  forename
  ,surname
  ,email
  , quotedate
From 
  (
    SELECT 
      *
      , ROW_NUMBER() OVER (PARTITION BY tblQuote.contno ORDER BY  quoteno DESC) AS rn
    FROM dba.quotehdr as tblQuote 
    left join dba.contact as tblContact on tblQuote.contno = tblContact.contno
  ) q
left join dba.contact as tblContact on q.contno = tblContact.contno
where rn = 1 
  and quotedate <=today()-720 
  and emailbounced = 0 
  and email is not null 
  and tblContact.statusflag = 'A' -- Now sourced from last left join

order by quotedate desc

    You will need another LEFT JOIN on the dba.contact table to be able to access this field (ADDED NOW as an example).

    Also, depending on your database engine - if your field is duplicated in both tables, the SELECT * in a subquery may eject those fields, or rename them, or throw an error. Run your inner subquery by itself and see what it produces, or use explicit field name instead of *

    (I still really think your * in the subquery is causing the error and also the confusion. Remove it and replace with table.field names - this will help you understand what is going wrong ...Otherwise your query logic is pretty fine, and adding the extra left join that I suggest is overkill)