Sub Query

[Rick]

Ladies and Gents,

This is an query written for Oracle 7.3. What is the sub-query doing? My
take is that the sub query is returning the max shis_action_date PER ROW.

SELECT SERV_SERVICE_LOCATION,
SHIS_ACTION_DATE,
SHIS_READING,
SHIS_INVN_CODE,
PREM_STREET_NAME,
PREM_STREET_NUMBER,
CUST_LAST_NAME,
CUST_FIRST_NAME,
MAPS_COLUMN2,
FROM MGR.SERV,
MGR.SHIS,
MGR.PREM,
MGR.CUST,
MGR.MAPS
WHERE SERV_PREM_CODE = SHIS_PREM_CODE
AND SERV_NUM = SHIS_SERV_NUM
AND SERV_PREM_CODE = PREM_CODE
AND SERV_PREM_CODE = MAPS_CODE
AND SERV_CUST_CODE = CUST_CUST_CODE (+)
AND SHIS_ACTION_DATE =
(
SELECT MAX(SHIS_ACTION_DATE)
FROM SHIS
WHERE SHIS_PREM_CODE = SERV_PREM_CODE
AND SHIS_SERV_NUM = SERV_NUM
)
AND SERV_READING_SEQ > 0
AND SERV_ROUTE <= 9000
AND SERV_ROUTE >= 1000
ORDER BY SERV_ROUTE ASC,
SERV_READING_SEQ ASC;

[Naveen]

"Rick" <rlmx@email.msn.com> wrote:
>
>Ladies and Gents,
>
>This is an query written for Oracle 7.3. What is the sub-query doing?
My
>take is that the sub query is returning the max shis_action_date PER ROW.


Rick

Your query will return all rows which have the latest shis_action_date and
NOT the max shis_action_date per row.

By latest shis_action_date I mean all the records for which the value of
the field 'shis_action_date' is equal to the max value determined in the
sub query.

Hope this is clear
Naveen