CODEWITHSUNDEEP
sqlsql-serverdatabaseinner-join
Tom
Tom

Reputation: 67

Invalid column name using row_number() OVER(PARTITION BY )

I have two tables TBL_CONTACT and TBL_PHONE that I am trying to join.

The TBL_PHONE table contains duplicate rows for contacts with multiple phone numbers, so I am partitioning these and trying to select the first instance only.

Here is the code I have:

SELECT
    (CONTACT.CONTACTID),
    (CONTACT.FULLNAME),
    (PHONE.contactid),
    (PHONE.numberdisplay),
    (row_number() OVER(PARTITION BY PHONE.Contactid ORDER BY PHONE.Contactid)) prn
FROM
    "ACT2015Demo"."dbo"."TBL_CONTACT" AS CONTACT
INNER JOIN
    TBL_PHONE AS PHONE
    ON CONTACT.Contactid = PHONE.Contactid
WHERE
    CAST(Editdate AS DATE) = CAST(GETDATE() AS DATE)

This gives the following:

CONTACTID   FULLNAME   CONTACTID    NUMBERDISPLAY   PRN
1001        Name1      1001         Tel1001 1       1
1001        Name1      1001         Tel1001 2       2
1002        Name2      1002         Tel1002 1       1
1003        Name3      1003         Tel1003 1       1
1003        Name3      1003         Tel1003 2       2
1003        Name3      1003         Tel1003 3       3

I then want to use the PRN column to limit the output to only rows with PRN = 1. I have tried the following, as this has worked for me in the past on less complex joins:

SELECT
    (CONTACT.CONTACTID),
    (CONTACT.FULLNAME),
    (PHONE.contactid),
    (PHONE.numberdisplay),
    (row_number() OVER(PARTITION BY PHONE.Contactid ORDER BY PHONE.Contactid)) prn
FROM
    "ACT2015Demo"."dbo"."TBL_CONTACT" AS CONTACT
INNER JOIN
    TBL_PHONE AS PHONE
    ON CONTACT.Contactid = PHONE.Contactid AND PRN = 1
WHERE
    CAST(Editdate AS DATE) = CAST(GETDATE() AS DATE)

However, this gives me the invalid column name error for PRN? I have also tried using PRN = 1 as part of the WHERE with the same error.

How can I get PRN to work as a column name and limit the output?

Upvotes: 1

Views: 5261

Answers (3)

wwmbes
wwmbes

Reputation: 304

This error also happens when the row_number() OVER(PARTITION BY ) clause has a composite set of key fields which appear in both tables in the join. The error will not go away no-matter how the aliases are arranged or rearranged. To solve this, the identically named key columns from the joining table must be renamed in an in-line temporary table as follows. Change this:

    INNER JOIN
    TBL_PHONE AS PHONE
    ON CONTACT.Contactid = PHONE.Contactid 
    ... some other col_names 
    etc. ...

to this:

    INNER JOIN (
        SELECT
            Contactid as PK_Contactid,
            ... some other col_names as PK_col_names,
            etc. ...
        FROM TBL_PHONE
        ) AS PHONE
    ON CONTACT.Contactid = PHONE.PK_Contactid
    ... some other col_names 
    etc. ...

NB. and don't prefix the any col_names with table aliases in the row_number() OVER(PARTITION BY ) clause at all. Worked for me.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562388

Something a bit weird and confusing for some people about SQL is that column aliases you define in the select-list cannot be referenced in the FROM clause or WHERE clause.

This is confusing because of the fact that column aliases appear to be defined early in the query (as the select-list is before the FROM clause). But the order of the syntax is not the order of execution.

You can get around this by running your query as a derived table subquery:

SELECT * 
FROM (
    SELECT
        CONTACT.CONTACTID,
        CONTACT.FULLNAME,
        PHONE.contactid,
        PHONE.numberdisplay,
        ROW_NUMBER() OVER(PARTITION BY PHONE.Contactid ORDER BY PHONE.Contactid) AS PRN
    FROM
        "ACT2015Demo"."dbo"."TBL_CONTACT" AS CONTACT
    INNER JOIN
        TBL_PHONE AS PHONE
        ON CONTACT.Contactid = PHONE.Contactid
    WHERE
        CAST(Editdate AS DATE) = CAST(GETDATE() AS DATE)
    ) AS DerivedTable
WHERE PRN = 1

PS: MySQL does not support windowing functions in versions up to 5.7. It has been announced that this feature is being worked on, hopefully it will be ready in MySQL 8. I think you may have tagged your question incorrectly.

You use the schema qualifier dbo which makes me think you are using Microsoft SQL Server or Sybase, is this correct?

Upvotes: 3

TheGameiswar
TheGameiswar

Reputation: 28900

I have tried the following, as this has worked for me in the past on less complex joins...The way you tried will never work for simple joins as well.You have to use Derived table or CTE

Select 
*
from
(
SELECT
    (CONTACT.CONTACTID) as concontactid,
    (CONTACT.FULLNAME),
    (PHONE.contactid),
    (PHONE.numberdisplay),
    (row_number() OVER(PARTITION BY PHONE.Contactid ORDER BY PHONE.Contactid)) prn
FROM
    "ACT2015Demo"."dbo"."TBL_CONTACT" AS CONTACT
INNER JOIN
    TBL_PHONE AS PHONE
    ON CONTACT.Contactid = PHONE.Contactid 
WHERE
    CAST(Editdate AS DATE) = CAST(GETDATE() AS DATE)
) as b
where prn=1

Upvotes: 1

Related Questions