Case Statement within a Join?

Database
N/A

Case Statement within a Join?

Hi.  I am wondering what capabilities TD offers in the way of case statements in joins.

Here is the problem:

I have a table that is comprised of a common format set of data from several source systems.  The table contains information regarding descriptions of code values found in each of the source systems, and the code set name, blah blah blah.

The problem:  In very specific instances, I care about the Upper/Lower Case values.  To isolate this, I have added a flag for records that require a (CaseSpecific) clause at the join level.  I want to do this with a single SELECT FROM statement and use a CASE statement in the join to force a (CaseSpecific) requirement.  Something like this:

SELECT A.Sys_ID, A.Sys_Name, A.Code_Set_Name, A.Value_Id,  B.Code_Set_Desc
FROM
TD_Dev_Srvr1.MyTableA A
LEFT OUTER JOIN TD_Dev_Srvr1.MyTableB B ON B.Sys_ID = A.Sys_ID
     AND B.Code_Set_Name = A.Code_Set_Name
     AND CASE WHEN B.CASE_SENSITIVE = 'X' THEN B.Value_Id = A.Value_Id(CaseSpecific)
     ELSE  B.Value_Id = A.Value_Id END
WHERE A.Sys_Id = '2002'
;

The error I get is: [3707] Syntax Error, expected something like an 'END' keyword between the word 'Value_Id' and '='.

I would prefer to not have to union two different Queries and add in a where clause to filter them down to the 2 types of joins needed.

thoughts??

Thanks in advance.

Tags (1)
1 REPLY

Re: Case Statement within a Join?

you can use CASE in select statement itself instead of join.

Something like -

EXPLAIN SELECT A.Sys_ID, 
   A.Sys_Name,
   A.Code_Set_Name,
   CASE WHEN B.CASE_SENSITIVE = 'X' THEN A.Value_Id(CaseSpecific) ELSEA.Value_Id END AS Value_Id, 
   B.Code_Set_Desc
FROM TD_Dev_Srvr1.MyTableA A
LEFT OUTER JOIN TD_Dev_Srvr1.MyTableB B
ON B.Sys_ID = A.Sys_ID     
 AND B.Code_Set_Name = A.Code_Set_Name    
 B.Value_Id = Value_Id
WHERE A.Sys_Id = '2002';

Nishhcint