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
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 5 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 5 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004