We help IT Professionals succeed at work.

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

OCCU
OCCU asked
on
81 Views
Last Modified: 2017-03-16
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,
)
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2013

Commented:
It will work if you use a single column in your select:

WHEN MEMBER_ID IN(SELECT        DIM_MEMBER.MEMBER_ID
                                                                                FROM            DIM_MEMBER 

Open in new window

(i.e. get rid of , DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID)

Author

Commented:
So perhaps I'm not fully understanding, but I'm not sure how adding the underlined code in the below query will return a Y for the 'Auto Loan' column I want to add if the member has an CURRENT_LOAN_ACCOUNT_ID LIKE '%L32%' when DIM_MEMBER_MEMBER_ID = LEFT(DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID) and a N if not;

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',
                    CASE
                    WHEN MEMBER_TYPE_DESC = 'Business' THEN 'Y'
                    ELSE 'N'
                    END AS 'Business Account',

                   (WHEN MEMBER_ID IN(SELECT        DIM_MEMBER.MEMBER_ID
                    FROM DIM_MEMBER) AS 'Auto Loan')


FROM            DIM_MEMBER

In order to know if a member has an auto loan corresponding to their member # the DIM_LOAN_ACCOUNT has to be queried to see if there is a CURRENT_LOAN_ACCOUNT_ID LIKE '%L32%'.  Perhaps, as I feared, I'm not explaining clearly enough what I'm trying to accomplish...
CERTIFIED EXPERT
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Awesome! That did the trick thank you so much! I was closer than I thought I was...

Author

Commented:
Thanks so much!!!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.