Forum Discussion
Two missing record from this query
I have the following query:
SELECT '20'||SUBSTR(CAST(T1.LMDTM1 as VarCHar(15)), 2, 6) as Date, T1.LMFSTM as Status,
T1.LMREF1 as CustPO, TRIM(T1.LMREF1) ||'-'||TRIM(T1.LMREF2) as Key,
T1.LMREF2 as Order, T1.LMREF3 as GUID
FROM EXTSYSFCC.EXLLMQLD T1
WHERE t1.LMDTM1 = (
SELECT MAX(t2.LMDTM1)
FROM EXTSYSFCC.EXLLMQLD t2
WHERE t2.LMREF2 = t1.LMREF2
) AND LMTPC1 = 'ABC' AND LMMSID = '123'
For some unknown reason to me I detected at least two records that are not included, the date field is 20250217 and the part where it skips those two records, there is at least one record with the same date, is this:
t1.LMDTM1 = (
SELECT MAX(t2.LMDTM1)
FROM EXTSYSFCC.EXLLMQLD t2
WHERE t2.LMREF2 = t1.LMREF2
)
If I delete that part I can see those two records.
Any help will be deeply appreciated.
Thanks!
Despite the fact that you're technically in the wrong forum, this is a logic error.
Because of the "MAX" statement, you'll only get one match with that t1.LMDTM1 = (...) comparison.Of course, returning more than one row on that subquery would lead to other errors.
I would attempt to replace your equality and subquery with a join, see if that helps.
SELECT '20'||SUBSTR(CAST(T1.LMDTM1 as VarCHar(15)), 2, 6) as Date, T1.LMFSTM as Status, T1.LMREF1 as CustPO, TRIM(T1.LMREF1) ||'-'||TRIM(T1.LMREF2) as Key, T1.LMREF2 as Order, T1.LMREF3 as GUID FROM EXTSYSFCC.EXLLMQLD T1 JOIN EXTSYSFCC.EXLLMQLD T2 ON t2.LMREF2 = t1.LMREF2 WHERE t1.LMTPC1 = 'ABC' AND t1.LMMSID = '123' AND t1.LMDTM1 = t2.LMDTM1;
In the future, Oracle PL SQL related questions should be asked at their community forum, in order to improve your chances for relevant replies.
SQL & PL/SQL - Oracle ForumsThat is Oracle PL/SQL, right?
Then this is the wrong forum, it's for Microsoft SQL Server.
