troubleshooting Question

SQL Server sunbquery nested in a CASE statement in a SELECT statement

Avatar of OCCU
OCCUFlag for United States of America asked on
Microsoft SQL Server
5 Comments1 Solution90 ViewsLast Modified:
Let me see if I can explain this in such a way that it makes sense to anyone other than myself...
I have written a simple query which pulls some member information from our members table (Member #, Full Name, First Name, Last Name, Join Date, Age, Active (whether or not their account is active Y/N) and Business Account (whether or not they have a business account Y/N depending on if MEMBER_TYPE_DESC contains the string 'Business') The code for that query is as follows;
(
SELECT        MEMBER_ID AS 'Member #', MEMBER_FIRST_NAME + ' ' + MEMBER_LAST_NAME AS 'Full Name', MEMBER_FIRST_NAME AS 'First Name', MEMBER_LAST_NAME AS 'Last Name', JOIN_DATE AS 'Join Date',
                   (CONVERT(int,CONVERT(char(8),GetDate(),112))-CONVERT(char(8),BIRTH_DATE,112))/10000 AS 'Age', ACTIVE_FLAG AS 'Active',
                   'Business Account' =
                        CASE
                              WHEN MEMBER_TYPE_DESC = 'Business' THEN 'Y'
                              ELSE 'N'
                              END
FROM            DIM_MEMBER
)

Super simple so far, right? But now I want to add an Auto Loan column which returns a Y/N response if the member has an Auto Loan with us. Unfortunately, to determine whether or not they have an Auto Loan I have to reference CURRENT_LOAN_ACCOUNT_ID column in the DIM_LOAN_ACCOUNT table.

The CURRENT_LOAN_ACCOUNT_ID is the Member # (eg. 12345), L (for loan) and the Loan Type (32 for auto loan), and if a member has more than one auto loan a .1, .2, .3 ect. will be added to the end of the CURRENT_LOAN_ACCOUNT_ID so that the loans can always be differentiated. So, for example, if member # 12345 has 2 auto loans there would be CURRENT_LOAN_ACCOUNT_ID's of 12345L32 and 12345L32.1 in the DIM_LOAN_ACCOUNT.

There is no MEMBER_ID (Member #) column in the DIM_LOAN_ACCOUNT table, so I have written another simple query that pulls the Member # from the DIM_MAMBER table and the CURRENT_LOAN_ACCOUNT_ID LIKE '%32%' from the DIM_LOAN_ACCOUNT table and joined them on the MEMBER_ID and LEFT(CURRENT_LOAN_ACCOUNT_ID, 5) as follows;

(
SELECT        DIM_MEMBER.MEMBER_ID, DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID
FROM            DIM_MEMBER INNER JOIN
                         DIM_LOAN_ACCOUNT ON DIM_MEMBER.MEMBER_ID = LEFT(DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID, 5)
WHERE        (DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID LIKE '%32%')
)

I would like to know how I can add the Auto Loan column to my original query that possibly uses the above query as a subquery in a CASE statement, such that if the Member # from the original query is listed in the subquery it returns a Y and if it is not then it returns a N.

I was thinking I could use the following code in the original SELECT statement, but it returned an error;

('Auto Loan' =
                        CASE
                              WHEN MEMBER_ID IN(SELECT        DIM_MEMBER.MEMBER_ID, DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID
                                                                                FROM            DIM_MEMBER INNER JOIN
                                                                                DIM_LOAN_ACCOUNT ON DIM_MEMBER.MEMBER_ID =
                                                                                LEFT(DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID, 5)
                                                                                WHERE   (DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID LIKE '%32%'))
                         THEN 'Y'
                   ELSE 'N'
                   END,
)
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros