Can anyone see where my subquery is wrong?
I am trying to find the latest date for the REVIEW_DATE column, but my data is still showing all
dates. Can anyone see what I am doing wrong?
Thank you
Don't see anything off-hand, but would be good to see the entire query.
That said, the idea should work.
However, see links like the following for other options, like using CTEs, joining on the derived table, etc. (there are many more such links, of course...)
Hi Gary,
Please see the query below:
SELECT InOp.IOR_NO AS "IOR #",
InOp.OP_COMP AS "OpCo",
--Operating Region
InOp.CREW_AREA AS "District",
--Work Group
--Work Group Adjusted
InOp.STATION AS "Sub",
InOp.DEVICE AS "Equipment/Line Name",
InOp.VOLTAGE_LEVEL || 'kV' AS "Voltage",
InOp.POLE_PAD AS "Pole/Pad #",
InOp.PROBLEM_DETAIL AS "Problem Description",
InOp.ACTION_COMMENT AS "Repair Status",
InOp.OUTAGE_TYPE AS "Outage Type",
InOp.OUTAGE_STATUS AS "Outage Status",
InOp.APP_NUM AS "Switching Order #",
InOp.WORK_GROUP_AREA AS "Work Group Area",
R.REVIEWED_BY AS "Person Responsible",
to_char(R.REVIEW_DATE, 'mm/dd/yyyy') AS "Last Reviewed Date",
InOp.LOCATION_DETAIL AS "Location",
Trim(InOp.POWERON) AS "Event #",
Trim(InOp.SAP) AS "SAP Order #",
Trim(InOp.WR_NUM) AS "WR #",
InOp.CREATED_BY AS "Created By",
InOp.CREATE_DATE AS "Created Date"
FROM TOA_APP.TOA_INOP InOp
LEFT JOIN TOA_APP.TOA_INOP_REVIEW R ON R.INOP_ID = InOp.INOP_ID
WHERE REVIEW_DATE =
(SELECT max(R.REVIEW_DATE)
FROM TOA_APP.TOA_INOP_REVIEW
WHERE R.REVIEW_DATE IS NOT NULL )
AND InOp.IOR_NO = 'IOR-19-01466-PN'
ORDER BY InOp.IOR_NO DESC
I've been doing some quick testing on a condensed version of your query, including its structural components (embedded SELECT in WHERE, ORDER BY, etc.) and I get proper results, so cant' reproduce the non-max dates you're seeing.
Only thing I see at this point is that you're not qualifying your REVIEW_DATE within the WHERE clause... e.g.
...
...
Any SQL experts out there? Again, there are other ways of accomplishing this, but Seth's method to grab the max date should work.
Gary, can you think of another way to do this? I looked at those URLs you sent, and I don't really understand them to be honest. I am a novice when it comes to SQL
