Luis Lara Luis Lara - 1 month ago 7
SQL Question

Using CASE and SELECT from another table using CONVERT

I'm working on SQL Server and I need help with a query.

This is the scenario:

I have 2 Tables

Roster
and
Customer
.

PK [ID]
exists in both Tables.

CREATE TABLE [dbo].[Tbl_Roster_Test](
[CONCATENATE_NUMBER] [varchar](100) NOT NULL,
[CONCATENATE_NAME] [varchar](100) NULL,
[CUST_ID] [varchar](100) NULL,
[CUST_NAME] [varchar](100) NULL,
[ID] [varchar](100) NOT NULL,
[ID_NAME] [varchar](100) NULL,
) ON [PRIMARY]

CREATE TABLE [dbo].[Tbl_Customer_Test](
[ID] [varchar](100) NOT NULL,
[CUST_ID_1] [varchar](100) NULL,
[CUST_ID_2] [varchar](100) NULL,
) ON [PRIMARY]

INSERT INTO [BOSSTest].[dbo].[Tbl_Roster_Test]
([CONCATENATE_NUMBER],[CONCATENATE_NAME],[CUST_ID],[CUST_NAME],[ID],[ID_NAME])
SELECT 'US193085','UNIVERSITY OF ARIZONA','','','US193085','UNIVERSITY OF ARIZONA'
UNION ALL
SELECT 'US193085117933','UNIVERSITY OF ARIZ','117933','UNIVERSITY OF ARIZ','US193085','UNIVERSITY OF ARIZONA'
UNION ALL
SELECT 'US193085T22346','UNIVERSITY OF AZ','T22346','UNIVERSITY OF AZ','US193085','UNIVERSITY OF ARIZONA'

INSERT into [BOSSTest].[dbo].[Tbl_Customer_Test]
([ID],[CUST_ID_1],[CUST_ID_2])
SELECT 'US193085','117933',''
UNION ALL
SELECT 'US193085','T22346',''
UNION ALL
SELECT 'US193085','T22346','117933'
UNION ALL
SELECT 'US193085','',''


The logic ...

In [Tbl_Roster_Test]
IF [CUST_ID] = '' THEN [CONCATENATE_NAME] = [ID_Name]
ELSE
IF [CUST_ID] <> '' THEN [CONCATENATE_NAME] = [Cust_ID_Name]


Knowing this, now I'm trying to link both tables using [ID] using the above logic.

If there is a [Cust_ID] then select [Cust_ID_Name], if not then select [ID_Name]

There are 3 cases:

1. There is no data in [Cust_ID]
2. There is data in [CUST_ID_1]
3. There is no data in [CUST_ID_1] and There is data in [CUST_ID_2]


When I execute this query...

SELECT
C.[ID]
,C.[CUST_ID_1]
,C.[CUST_ID_2]
,(SELECT TOP 1 ISNULL([CONCATENATE_NAME],'') FROM BOSSTest.dbo.Tbl_Roster_Test R WHERE C.[ID] = R.[ID]) AS [Customer_Name]

FROM [BOSSTest].[dbo].[Tbl_Customer_Test] C


This is what I get.

+--------+------ -+---------+---------------------+
| ID|CUST_ID_1|CUST_ID_2| Customer_Name|
+--------+------ -+---------+---------------------+
|US193085| 117933| |UNIVERSITY OF ARIZONA|
|US193085| T22346| |UNIVERSITY OF ARIZONA|
|US193085| T22346| 117933|UNIVERSITY OF ARIZONA|
|US193085| | |UNIVERSITY OF ARIZONA|
+--------+---------+---------+---------------------+


Then I tried to use a Subquery ...

SELECT
[ID]
,[CUST_ID_1]
,[CUST_ID_2]
,CASE WHEN [CUST_ID_1] <>
THEN (SELECT TOP 1 CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(100),[CONCATENATE_NAME])) = 1 then CONVERT(VARCHAR(100),[CONCATENATE_NAME]) else 0 End) FROM BOSSTest.dbo.Tbl_Roster_Test R WHERE [CUST_ID_1] = R.[CUST_ID])
ELSE 0 END AS [Customer_Name]

FROM (

SELECT
C.[ID]
,C.[CUST_ID_1]
,C.[CUST_ID_2]
,(SELECT TOP 1 ISNULL([CONCATENATE_NAME],'') FROM BOSSTest.dbo.Tbl_Roster_Test R WHERE C.[ID] = R.[ID]) AS [Customer_Name]

FROM [BOSSTest].[dbo].[Tbl_Customer_Test] C

) AS TEST


And this is what I get.

+--------+------ -+---------+-------------+
| ID|CUST_ID_1|CUST_ID_2|Customer_Name|
+--------+------ -+---------+-------------+
|US193085| 117933| | NULL|
|US193085| T22346| | 0|
|US193085| T22346| 117933| 0|
|US193085| | | 0|
+--------+---------+---------+-------------+


I used
CONVERT
to avoid the following error
Syntax error converting the varchar value 'UNIVERSITY OF AZ' to a column of data type int.


But at the end, this is what I really need.

+--------+------ -+---------+---------------------+
| ID|CUST_ID_1|CUST_ID_2| Customer_Name|
+--------+------ -+---------+---------------------+
|US193085| 117933| | UNIVERSITY OF ARIZ|
|US193085| T22346| | UNIVERSITY OF AZ|
|US193085| T22346| 117933| UNIVERSITY OF AZ|
|US193085| | |UNIVERSITY OF ARIZONA|
+--------+---------+---------+---------------------+


Any suggestions on how I can run this logic using the 3 cases and make good use of
CONVERT
.

Thanks in advance,
Luis

Answer

I think that's error in Tbl_Customer_Test insert clauses - did You mind H17933 instead of 117933 ?

If that's a case then You have to write smth like this

SELECT CT.*,RT.CONCATENATE_NAME
FROM [dbo].[Tbl_Customer_Test] CT
left  join  [dbo].[Tbl_Roster_Test] RT 
on RT.CONCATENATE_NUMBER = 
CT.[ID] + isnull(nullif(ct.[CUST_ID_1],''), ct.[CUST_ID_2])

answer is

 ID         CUST_ID_1   CUST_ID_2   CONCATENATE_NAME
 ---------- ----------- ----------- ---------------------
 US193085   H17933                  UNIVERSITY OF ARIZ
 US193085   T22346                  UNIVERSITY OF AZ
 US193085   T22346       H17933     UNIVERSITY OF AZ
 US193085                           UNIVERSITY OF ARIZONA

UPD

due to comment added ( we eaten first symbol :)

SELECT CT.*,RT.CONCATENATE_NAME
FROM [dbo].[Tbl_Customer_Test] CT
join  [dbo].[Tbl_Roster_Test] RT 
on RT.CONCATENATE_NUMBER like
CT.[ID] + isnull(stuff(isnull(nullif(ct.[CUST_ID_1],''),ct[CUST_ID_2]),1,1,'_'),'')