sam2929
asked on
Join based upon multiple condition
How can i do this in sql
i have table Partner with columns(This table will be master table)
ActivityPartner ID,Sender Partner ID,Receiver Partner ID
then i have another table customer with columns
customer ID,ActivityCustomerKey
i want the logic like
"IF ActivityPartner ID = Customer ID
then ActivityCustomerKey
else
IF Sender Partner ID = Customer ID
then ActivityCustomerKey else
IF Receiver Partner ID= Customer ID
then ActivityCustomerKey
else
ActivityCustomerKey = 'Unknown'
i have table Partner with columns(This table will be master table)
ActivityPartner ID,Sender Partner ID,Receiver Partner ID
then i have another table customer with columns
customer ID,ActivityCustomerKey
i want the logic like
"IF ActivityPartner ID = Customer ID
then ActivityCustomerKey
else
IF Sender Partner ID = Customer ID
then ActivityCustomerKey else
IF Receiver Partner ID= Customer ID
then ActivityCustomerKey
else
ActivityCustomerKey = 'Unknown'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
getting error
ACT_CUST_ID is chracter 10
BIC_PARTNER_ID is chracter 16
ACT_CUST_KEY is integer 8
proc sql;
6 validate
7 select * from stgtcrm.STG_T_TKT_FCT,QSM_ DI.ACTIVE_ CUST_DIM
8 where CASE WHEN STG_T_TKT_FCT.BIC_PARTNER_ ID = ACTIVE_CUST_DIM.ACT_CUST_I D then ACTIVE_CUST_DIM.ACT_CUST_K EY
9 ELSE 0
10 END As ACTIVE_CUST_DIM.ACT_CUST_K EY;
__
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT,
NOTIN, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.
2 The SAS System
ACT_CUST_ID is chracter 10
BIC_PARTNER_ID is chracter 16
ACT_CUST_KEY is integer 8
proc sql;
6 validate
7 select * from stgtcrm.STG_T_TKT_FCT,QSM_
8 where CASE WHEN STG_T_TKT_FCT.BIC_PARTNER_
9 ELSE 0
10 END As ACTIVE_CUST_DIM.ACT_CUST_K
__
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT,
NOTIN, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.
2 The SAS System
Hi
Apologies, I'm not sure I understand the question any more.
It is probably easier to give an example of the two tables, and what you want the result to be.
Try this - if it doesn't work - post the examples so we can see what you need...
PROC SQL;
select * from stgtcrm.STG_T_TKT_FCT,QSM_ DI.ACTIVE_ CUST_DIM
where STG_T_TKT_FCT.BIC_PARTNER_ ID = ACTIVE_CUST_DIM.ACT_CUST_I D
OR
STG_T_TKT_FCT.BIC_SENDER_I D = ACTIVE_CUST_DIM.ACT_CUST_I D
OR
STG_T_TKT_FCT.BIC_RECIEVER _ID = ACTIVE_CUST_DIM.ACT_CUST_I D ;
Apologies, I'm not sure I understand the question any more.
It is probably easier to give an example of the two tables, and what you want the result to be.
Try this - if it doesn't work - post the examples so we can see what you need...
PROC SQL;
select * from stgtcrm.STG_T_TKT_FCT,QSM_
where STG_T_TKT_FCT.BIC_PARTNER_
OR
STG_T_TKT_FCT.BIC_SENDER_I
OR
STG_T_TKT_FCT.BIC_RECIEVER
ASKER
This will not work
reason is customer id can have one to many relation to partner . what we want is if customer id
is found in ActivityPartner ID then get ActivityCustomerKey if not then search in Sender Partner ID
if not there then search Receiver Partner ID
partner
ActivityPartner ID,Sender Partner ID,Receiver Partner ID
101 101 101
100 100 100
customer will be
customer ID,ActivityCustomerKey
101 1
100 2
103 3
i want the logic like
reason is customer id can have one to many relation to partner . what we want is if customer id
is found in ActivityPartner ID then get ActivityCustomerKey if not then search in Sender Partner ID
if not there then search Receiver Partner ID
partner
ActivityPartner ID,Sender Partner ID,Receiver Partner ID
101 101 101
100 100 100
customer will be
customer ID,ActivityCustomerKey
101 1
100 2
103 3
i want the logic like
Use a CASE statement:
PROC SQL;
Create table p1 as
select
CASE WHEN ActivityPartner ID = Customer ID then ActivityCustomerKey
WHEN Sender Partner ID = Customer ID then ActivityCustomerKey
Receiver Partner ID= Customer ID
then ActivityCustomerKey
else
ActivityCustomerKey = 'Unknown'