Sub Query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Sub Query

  1. #1
    Rick Guest

    Sub Query


    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;

  2. #2
    Naveen Guest

    Re: Sub Query


    "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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center