Link to home
Start Free TrialLog in
Avatar of OCCU
OCCUFlag for United States of America

asked on

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

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,
)
Avatar of chaau
chaau
Flag of Australia image

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)
Avatar of OCCU

ASKER

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...
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of OCCU

ASKER

Awesome! That did the trick thank you so much! I was closer than I thought I was...
Avatar of OCCU

ASKER

Thanks so much!!!