Using Case Statement Inside SQL Join

5 pts.
Tags:
CASE statement
JOIN statement
SQL
T-SQL
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
ASKED: March 13, 2008  11:08 AM
UPDATED: March 17, 2008  4:54 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following