Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2012
    Posts
    36

    Unanswered: Removing duplicates in from the main query

    Dear experts,

    My Query is as below,

    SELECT sl.ENTITY_ID,
    sl.OPPORTUNITY_ID,
    sl.LICENSE_EXPIRY_DATE,
    sl.CREATE_DATE
    FROM SAP_LICENSES sl
    WHERE UPPER (sl.CREATE_DATE) IS NOT NULL
    AND (TO_DATE (sl.LICENSE_EXPIRY_DATE, 'dd-mm-yy') IN
    ( SELECT MAX (
    TO_DATE (sap_licenses.LICENSE_EXPIRY_DATE,
    'dd-mm-yy'))
    FROM SAP_LICENSES
    WHERE TRIM (sap_licenses.entity_id) = TRIM (sl.entity_id)
    GROUP BY sap_licenses.entity_id))
    AND sl.entity_id = '0000200478'


    Here im selecting one record ,sl.entity_id='0000200478'

    My data is having the same maximum value in the output, (Please refer the output data),

    My requirement is i should have only one record grouped by entity_id,

    Please advise.
    Attached Thumbnails Attached Thumbnails License.jpg  

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Note that you have 2 opportunity_id values. Which one do you want?

  3. #3
    Join Date
    Feb 2012
    Posts
    36
    I have two scenarios,

    1.In the above example i have to keep opportunity_id = '31568'
    it is the scenario where opportunity_id is different.

    2,in the other scenario,
    i have same opportunity_id,

    Please advise on both the scenarios.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    1.In the above example i have to keep opportunity_id = '31568'
    Why? Remember, I don't know your data, I don't know the requirements around your query.

  5. #5
    Join Date
    Feb 2012
    Posts
    36
    Here in my requirement,


    After grouping by entity_id in the inner query and joing outer table and inner table in the sub query, we get two maximum 'license_expiry_date' records,
    Out of which

    i have two scenarions

    1.Different opportunity id, here in this case i have to choose one opportunity id which business asks.

    2.We will have same opportunity id (Could be duplicated), in this case we have to have only one record.

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    1. Unless you can specify the logic here, I cannot help you.
    2. I would expect just one row to be returned from this if you specify DISTINCT in your query.

  7. #7
    Join Date
    Feb 2012
    Posts
    36
    If i have different opportunity id they i have to select the Maximum of opportunity id.

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    then put a max around that column in the select clause and add the appropriate group by clause. Job done.

  9. #9
    Join Date
    Feb 2012
    Posts
    36
    SELECT sl.ENTITY_ID,
    sl.OPPORTUNITY_ID,
    sl.LICENSE_EXPIRY_DATE,
    sl.CREATE_DATE
    FROM SAP_LICENSES sl
    WHERE UPPER (sl.CREATE_DATE) IS NOT NULL
    AND (TO_DATE (sl.LICENSE_EXPIRY_DATE, 'dd-mm-yy') IN
    ( SELECT MAX (
    TO_DATE (sap_licenses.LICENSE_EXPIRY_DATE,
    'dd-mm-yy')),
    MAX (sap_licenses.opportunity_id)
    FROM SAP_LICENSES
    WHERE TRIM (sap_licenses.entity_id) = TRIM (sl.entity_id)
    GROUP BY sap_licenses.entity_id))
    AND sl.entity_id = '0000200478'



    Tried to put max for opportunity id, doesnt work. please find the query above for your reference

  10. #10
    Join Date
    Feb 2012
    Posts
    36
    Error says, 'Too many Values'

  11. #11
    Join Date
    Dec 2007
    Posts
    253
    Put it in the OUTER select

  12. #12
    Join Date
    Dec 2007
    Posts
    253
    It also looks like you can simply put a max around the license expiry date in the outer query and get rid of the correllated sub query altogether.

  13. #13
    Join Date
    Feb 2012
    Posts
    36
    SELECT sl.ENTITY_ID,
    max( sl.OPPORTUNITY_ID) as opp_id,
    sl.LICENSE_EXPIRY_DATE,
    sl.CREATE_DATE
    FROM SAP_LICENSES sl
    WHERE UPPER (sl.CREATE_DATE) IS NOT NULL
    AND (TO_DATE (sl.LICENSE_EXPIRY_DATE, 'dd-mm-yy') IN
    ( SELECT MAX (
    TO_DATE (sap_licenses.LICENSE_EXPIRY_DATE,
    'dd-mm-yy'))
    FROM SAP_LICENSES
    WHERE TRIM (sap_licenses.entity_id) = TRIM (sl.entity_id)
    GROUP BY sap_licenses.entity_id))
    AND sl.entity_id = '0000200478'
    group by sl.ENTITY_ID,sl.OPPORTUNITY_ID,sl.LICENSE_EXPIRY_D ATE,sl.CREATE_DATE

    Tried this buddy, doesnt help, same case

  14. #14
    Join Date
    Dec 2007
    Posts
    253
    Take another look at your group by. You have too many columns in there. Think about how you want to group your data. Failing that, take a look at the documentation on aggregate functions.

  15. #15
    Join Date
    Feb 2012
    Posts
    36
    Thanks bro, i got it :-)

    Thank you much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •