Invalid Column Name on CONCAT_WS

Question

Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue.

I have two tables cases and legal_entity the data on the two tables looks like this

legal_entity

first_name	last_name	address_1_zip
Maria	Munoz	33122
John	Glenn	10003
Melissa	Johnson	90210

cases

style	case_number	create_date
Munoz, Maria	SL12345	2021-03-01 11:45:26.657
Glenn, John	SL45678	2021-03-02 12:10:14.552
Johnson, Melissa (DNY)	SG42561	2021-04-10 01:06:10.214

What I am trying to do is combine the last_name and first_name fields from the legal_entity table with a comma between them, and match (contains) it against the style field in the cases table. For everything in the last month basically. This is the query I'm using

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON LE.CombinedName=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date

The issue I run into is that it seems to not like the LE.CombinedName so I'm not sure how to reference the concatenated string against the style column in the cases table.

Any help would be greatly appreciated.

nbk · Accepted Answer · 2021-04-16T17:55:48.323

You can use only columns from the joined tables or in your case concatenated version of it

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date

CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;
INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;

CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;
INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
GO

first_name | last_name | address_1_zip | CombinedName
:--------- | :-------- | ------------: | :-----------
Maria      | Munoz     |         33122 | Munoz,Maria 
John       | Glenn     |         10003 | Glenn,John

db<>fiddle here

CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;
INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;
GO

CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;
INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;
GO

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON PATINDEX( CONCAT( LE.last_name,', ',LE.first_name,'%'),C.style) > 0
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
GO

first_name | last_name | address_1_zip | CombinedName   
:--------- | :-------- | ------------: | :--------------
Maria      | Munoz     |         33122 | Munoz,Maria    
John       | Glenn     |         10003 | Glenn,John     
Melissa    | Johnson   |         90210 | Johnson,Melissa

db<>fiddle here

Invalid Column Name on CONCAT_WS

1 Answers1