I am trying to make part of this query conditional, but I am really struggling to do so. Below I've typed out some pseudo-code that is representative of my query.
Main Query
SELECT A.T_NBR
,A.I_NBR
,A.DATE
,A.MX_CD
,A.MY_CD
,A.S_CD
,B.O_NBR
FROM A,B,C,D,E,F,H,I,TMP_J
WHERE A.MY_CD IN ('FOO','BAR')
AND A.T_NBR NOT IN (
SELECT A.T_NBR
FROM A,K
WHERE A.MY_CD = 'BAR'
AND P_DATE < ADD_MONTHS(SYSDATE, -36)
AND A.S_CD NOT IN ('AAA','BBB')
AND K.K_CD NOT IN ('DDD','EEE')
--JOIN
AND A.I_NBR = K.I_NBR(+)
)
AND U_CD = 'FFFF'
--DATE LESS THAN 3 YEARS
AND TO_DATE(H.DATE,'YYYY-MM-DD') < ADD_MONTHS(SYSDATE, -36)
--NUM GREATER THAN 23
AND TO_CHAR(SYSDATE,'SYYYY')-TO_CHAR(I.NUM,'SYYYY') > 23
AND A.S_CD NOT IN ('AAA','BBB')
AND B.DATE = (
SELECT MAX(DATE)
FROM B
)
AND B.O_NBR < 200
AND F.MG_CD <> '000'
AND A.T_NBR NOT IN (12345,54321,98765)
AND ((E.T IS NULL) OR (RTRIM(E.T) LIKE '%###%'))
AND TMP_J.I_NBR IS NULL
--JOINS
AND A.T_BR = B.T_NBR(+)
AND ((B.I_NBR = F.I_NBR) AND (B.DATE = F.DATE))
AND A.S_NBR = D.S_NBR(+)
AND A.D_NBR = C.D_NBR(+)
AND ((D.S_NBR = G.S_NBR(+)) AND (UPPER(G.A_CD(+)) = 'XXX'))
AND G.A_NBR = E.A_NBR(+)
AND H.I_NBR = A.I_NBR
AND A.S_NBR = I.S_NBR(+)
AND A.T_NBR = TMP_J.T_NBR(+);
Essentially, this is what I am trying to make conditional (written out in plain English with pseudo-code):
IF
A.MY_CD = 'BAR'
THEN
A.T_NBR NOT IN (
SELECT A.T_NBR
FROM A,K
WHERE A.MY_CD = 'BAR'
AND P_DATE < ADD_MONTHS(SYSDATE, -36)
AND A.S_CD NOT IN ('AAA','BBB')
AND K.K_CD NOT IN ('DDD','EEE')
--JOIN
AND A.I_NBR = K.I_NBR(+)
)
ELSE IF
A.MY_CD = 'FOO'
THEN
WHERE U_CD = 'FFFF'
--DATE LESS THAN 3 YEARS
AND TO_DATE(H.DATE,'YYYY-MM-DD') < ADD_MONTHS(SYSDATE, -36)
--NUM GREATER THAN 23
AND TO_CHAR(SYSDATE,'SYYYY')-TO_CHAR(I.NUM,'SYYYY') > 23
AND THEN I JUST WANT TO REST OF THE WHERE CLAUSES TO OPERATE OUTSIDE OF THE CONDITION.
Aside from not really knowing what method would work best (CASE/WHEN, DECODE, IF/THEN), the part where I'm really struggling is trying to implement a SELECT statement within these conditional clauses. I've tried implementing a CASE WHEN in the SELECT and WHERE parts of the query while attempting to put a SELECT statement in the WHEN portion of the CASE statement. I've also tried DECODE in the same fashion. Additionally, I have tried implementing an IF/THEN, but can't seem to tie it in with my main query.
I'm certainly not a pro when it comes to PL/SQL, so it may just be my lapse in knowledge. What would be the best approach to make a portion of this query conditional?
Thanks so much for your responses and assistance!
You seem to just want to combine the conditions with AND/OR logic; without making any other changes to your approach:
...
FROM A,B,C,D,E,F,H,I,TMP_J
WHERE
(
(
A.MY_CD = 'BAR'
AND A.T_NBR NOT IN (
SELECT A.T_NBR
FROM A,K
WHERE A.MY_CD = 'BAR'
AND P_DATE < ADD_MONTHS(SYSDATE, -36)
AND A.S_CD NOT IN ('AAA','BBB')
AND K.K_CD NOT IN ('DDD','EEE')
--JOIN
AND A.I_NBR = K.I_NBR(+)
)
)
OR
(
A.MY_CD = 'FOO'
AND U_CD = 'FFFF'
--DATE LESS THAN 3 YEARS
AND TO_DATE(H.DATE,'YYYY-MM-DD') < ADD_MONTHS(SYSDATE, -36)
--NUM GREATER THAN 23
AND TO_CHAR(SYSDATE,'SYYYY')-TO_CHAR(I.NUM,'SYYYY') > 23
)
)
AND A.S_CD NOT IN ('AAA','BBB')
AND B.DATE = (
SELECT MAX(DATE)
FROM B
)
AND B.O_NBR < 200
AND F.MG_CD <> '000'
AND A.T_NBR NOT IN (12345,54321,98765)
AND ((E.T IS NULL) OR (RTRIM(E.T) LIKE '%###%'))
AND TMP_J.I_NBR IS NULL
--JOINS
...
That seems to be logically what you are asking for. But you will need to look at the execution plan and performance, among other things. There are various other issues to think about, including switching to use explicit joins, not in vs not exists (as mentioned in comments), not storing dates as strings, maybe using (more, modern) joins instead of subqueries, maybe using extract() to get your year number instead of to_char() and implicit conversion to a number (if that clause even makes sense), etc.