SQL Correlation error using OVER & PARTITION in SELECT statement

Question

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?

The separate tables that make up the sub query `q` are only visible within that subquery. Outside of the subquery, all the columns belong only to `q`. — Shannon Severance, Jan 18 '15 at 17:07
Are you _sure_ you're using Oracle? There's a fair bit wrong with this... you can't use `AS` to alias tables; `TODAY()` is not a valid function (it's `SYSDATE`) and there's no need for the parenthesis; if you're selecting `*` and additional columns you need to alias that, i.e. `tablequote.*` (please don't do this only select the required columns) and you're referencing `statusflag` out of scope, why are you specifying the table when you aren't selecting from it? If you've got multiple columns with that name in your SELECT that'll cause more errors. — Ben, Jan 18 '15 at 18:11
`-00142` does not appear to be a valid Oracle error message, are you sure that's the right error number? — Jon Heller, Jan 18 '15 at 18:13
As far as I am aware it's an Oracle system. Adaptive Server Anywhere 9. I also write the SQL scripts in nteractive SQL/Infomaker. — Jonathan Griffin, Jan 18 '15 at 18:22
My understanding of SQL is basic, so apologies for any confusion. I did use the following as a base [link](http://stackoverflow.com/questions/1299556/sql-group-by-max) — Jonathan Griffin, Jan 18 '15 at 18:23
I did use the following link to help form the basis of my statement link [link](http://stackoverflow.com/questions/1299556/sql-group-by-max) — Jonathan Griffin, Jan 18 '15 at 18:50

Grantly · Accepted Answer · 2015-01-18T20:39:03.230

(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)

Thanks, I tried that but it didn't work. Statusflag appears in both tables too, so I tried q.contact.statusflag, no joy. — Jonathan Griffin, Jan 18 '15 at 18:31
Thanks Grantly where should I be putting my second LEFT JOIN — Jonathan Griffin, Jan 18 '15 at 20:17
@JonathanGriffin I would add it after your subquery q and before your WHERE clause. (However, I still think you can play with your subquery to eliminate the * and that would also solve the problem) I will adjust my answer with a left join - syntax may be off as I am not testing it. — Grantly, Jan 18 '15 at 20:38

SQL Correlation error using OVER & PARTITION in SELECT statement

1 Answers1