OCCU
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))-C ONVERT(cha r(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_L OAN_ACCOUN T_ID
FROM DIM_MEMBER INNER JOIN
DIM_LOAN_ACCOUNT ON DIM_MEMBER.MEMBER_ID = LEFT(DIM_LOAN_ACCOUNT.CURR ENT_LOAN_A CCOUNT_ID, 5)
WHERE (DIM_LOAN_ACCOUNT.CURRENT_ LOAN_ACCOU NT_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_L OAN_ACCOUN T_ID
FROM DIM_MEMBER INNER JOIN
DIM_LOAN_ACCOUNT ON DIM_MEMBER.MEMBER_ID =
LEFT(DIM_LOAN_ACCOUNT.CURR ENT_LOAN_A CCOUNT_ID, 5)
WHERE (DIM_LOAN_ACCOUNT.CURRENT_ LOAN_ACCOU NT_ID LIKE '%32%'))
THEN 'Y'
ELSE 'N'
END,
)
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(
'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_
(
SELECT DIM_MEMBER.MEMBER_ID, DIM_LOAN_ACCOUNT.CURRENT_L
FROM DIM_MEMBER INNER JOIN
DIM_LOAN_ACCOUNT ON DIM_MEMBER.MEMBER_ID = LEFT(DIM_LOAN_ACCOUNT.CURR
WHERE (DIM_LOAN_ACCOUNT.CURRENT_
)
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_L
FROM DIM_MEMBER INNER JOIN
DIM_LOAN_ACCOUNT ON DIM_MEMBER.MEMBER_ID =
LEFT(DIM_LOAN_ACCOUNT.CURR
WHERE (DIM_LOAN_ACCOUNT.CURRENT_
THEN 'Y'
ELSE 'N'
END,
)
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.CURR ENT_LOAN_A CCOUNT_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))-C ONVERT(cha r(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...
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(
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome! That did the trick thank you so much! I was closer than I thought I was...
ASKER
Thanks so much!!!
Open in new window
(i.e. get rid of , DIM_LOAN_ACCOUNT.CURRENT_L