Question

  Asked: Mar 13 2008   11:08 AM GMT
  Asked by: Bmdayal


Using Case Statement Inside SQL Join


SQL, T-SQL, JOIN, CASE statement

Hi,
I am trying to convert dynamic sql into static sql due to permission issue, I faced a challange when i am trying to convert this part of code given below


-- join to ADDR_DETAIL
SET @join_sql = 'inner join ADDR_DETAIL on ADDR_DETAIL.GRP_CONTACT_ID = pm.GRP_CONTACT_ID AND '

IF (@ChannelEntity = '') OR (@ChannelEntity IS NULL)
BEGIN
-- Preferred Address
SET @join_sql = @join_sql + 'ADDR_DETAIL.PREFERRED_ADDR_FL=''Y'' '
END
ELSE
BEGIN
-- Use specified Channel Enitity
SET @join_sql = @join_sql + @ActChannelEntity + ' = ''' + @ChannelEntity + ''' '
END

-- Email
IF (@Channel = 'E')
SET @filter_sql = @filter_sql + ' AND RTRIM(ADDR_DETAIL.email_addr) <> '''' '


-- Address Cleansing
IF (@ACleanse = 'CL')
BEGIN
-- Only cleansed addresses
SET @join_sql = @join_sql + ' AND ADDR_DETAIL.CLNS_STATUS LIKE ''C%'' '
END
ELSE IF (@ACleanse = 'AG')
BEGIN
-- All addresses except 'Gone Aways'
SET @join_sql = @join_sql + ' AND ADDR_DETAIL.CLNS_STATUS != ''GA'' '
END


I tried to use the case statement something like this given below inside Join but it has not worked, I know this is very far away from my solution, but please help me to get the exact solution.

INNER JOIN ADDR_DETAIL (NOLOCK) ON
ADDR_DETAIL.GRP_CONTACT_ID = part_master.GRP_CONTACT_ID AND
CASE
WHEN @ChannelEntity = '' OR @ChannelEntity IS NULL THEN
ADDR_DETAIL.PREFERRED_ADDR_FL END ='Y' AND
CASE
WHEN @ChannelEntity <> '' OR @ChannelEntity IS NOT NULL THEN
ADDR_DETAIL.ADDR_TYP_CD END = @ChannelEntity AND
CASE WHEN @ACleanse = 'CL' THEN
ADDR_DETAIL.CLNS_STATUS END like 'C%' AND
CASE WHEN @ACleanse = 'AG' THEN
ADDR_DETAIL.CLNS_STATUS END != 'GA' AND
RTRIM(ADDR_DETAIL.email_addr) <> ''

I am not very good in SQL please help me to solve this problem

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window

Answer Wiki (Improve, edit or add to this answer)


 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0



You will have to build the whole SQL statement into a variable in one go then execute it.

The INNER JOIN is implied

e.g. select x.a from x, y where x.rowid=y.rowid

This is an inner join and will only return rows where there is a reference in both tables
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Development, SQL Server and Database.

Looking for relevant Development Whitepapers? Visit the SearchSQLServer.com Research Library.


Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register