Hello; I am working with an Account Table and a Transactional Table. The Account table unique ID is [ACCT].[CASE_ID]; there is only one record for each [CASE_ID]. I am connecting to the transactional table [TRANS] using [ACCT].[CASE_ID] = [TRANS].[CASE_ID] in a LEFT JOIN. Within the transactional table, one unique case can have multiple transactions; so it is your typical waterfall type relationship.
There is a field within the transactional table called [TYPE]. Every single transaction within the transaction table either has a value in the [TYPE] field or a NULL.
Now I am only pulling information from the ACCT table; so for each unique CASE_ID there is only one record. What I am looking to do is to create a column in my main query to say...Look at each CASE_ID within the transaction table; if any of the transactions for said CASE_ID have a value in the [TYPE] column (or is not null); set the value to "Y".
I think your basic requirement can be met using the following:
SELECT A.Case_ID ,(CASE WHEN T.Type IS NOT NULL THEN 'Y' END)
,... other columns as required... FROM Account AS A LEFT OUTER JOIN Trans AS T ON A.Case_ID = T.Case_ID
Does that help?
Just to be clear, lets say a CASE_ID has 5 transactions.
- If all 5 transactions are NULL; this will show as "N",
- If all 5 transactions are NOT NULL; this will show as "Y",
- If 3 of 5 transactions are NOT NULL; this will show as "Y"
Are all three scenerios correct? Basically what I am asking is; if even one transactions is not null; I want my new column to give me a "Y". If I use your logic and scenerio 3 that I listed; my main query will give me two records for that single CASE_ID; one with a Y and one with a N in my results... I only want one record per CASE_ID.
Yes your understanding is correct.
One minor change to the SQL (to generate a 'N' value):
ELECT A.Case_ID ,(CASE WHEN T.Type IS NOT NULL THEN 'Y' ELSE 'N' END) ,... other columns as required... FROM Account AS A LEFT OUTER JOIN Trans AS T ON A.Case_ID = T.Case_ID
Remember that (using your example where the CASE_ID has 5 transactions), this CASE_ID will be in your answer set 5 times - because the row from the Account table joins to 5 rows from the Trans table.
If you only want the Account row to appear once in the ouput then you need to do some aggregation, something like:
ELECT A.Case_ID ,MAX(CASE WHEN T.Type IS NOT NULL THEN 'Y' ELSE 'N' END) ,... other columns as required... FROM Account AS A LEFT OUTER JOIN Trans AS T ON A.Case_ID = T.Case_ID GROUP BY...
Right now, before I tried to implement this new column, I am only getting one record per CASE_ID, becuase even though I am connected to the TRANS table, I do not have any output rows from the TRANS table. So my SELECT DISTINCT is allowing for one record per CASE_ID. But when I implement the new column as you state above; it is not adding a up to two records per CASE_ID, because each CASE_ID can have a "Y" only, a "N" only, or one of each.
What I want my output to show is... if the CASE_ID has ALL NULLS, give me "N", if there is even one transactions with a NOT NULL in the [TYPE] field, give me one record with a "Y", not one with a "Y" and one with a "N".
Am I explaining this ok?
How about showing some sample data and what you want the output to be?
I don't thik using DISTINCT is going to give you the correct answer.
I think you can do this with MAX and a CASE expression. as per my previous suggestion:
SELECT A.Case_ID ,MAX(CASE WHEN T.Type IS NOT NULL THEN 'Y' ELSE 'N' END) AS IND_COL FROM Account AS A LEFT OUTER JOIN Trans AS T ON A.Case_ID = T.Case_ID GROUP BY 1;
This will give you one row per Case_ID value, regardless of the number of transaction rows.
This one row will contain a value of 'N' for IND_COL if all Trans rows (for the Case_ID) contain NULL in the 'Type' column, otherwise it will contain 'Y'.
If that isn't what you want then I think some sample data and required output will help.
Perfect; adding the MAX will do the trick, becuase if one CASE_ID has a Y and a N; the MAX will equal the Y only!