Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    36

    Unanswered: Counting with Conditions.

    Dears,

    I have situation where,


    My Table SAP_LICENSE has a Column called LICENSE_NUM,

    Now my requirement is,


    If There exist more than one LICENSE_NUM, then Select the LICENSE_NUM, where TRANSACTION_TYPE ='ZRSL'
    Else
    If multiple LICENSE_NUM have the same TRANSACTION_TYPE ='ZRSL', Then Select the LIcense_NUM with maximum Creation date.

    My incomplete query is:

    SELECT sl.ENTITY_ID ENTITY_ID,
    sl.OPPORTUNITY_ID OPPORTUNITY_ID,
    sl.CAPITAL_AMOUNT CAPITAL_AMOUNT,
    sl.ECONOMIC_SECTOR_DESC ECONOMIC_SECTOR_DESC,
    sl.ECONOMIC_SUB_SECTOR ECONOMIC_SUB_SECTOR,
    sl.LICENSE_EXPIRY_DATE LICENSE_EXPIRY_DATE,
    sl.LICENSE_FIELD_DESC LICENSE_FIELD_DESC,
    sl.LICENSE_ISSUE_DATE LICENSE_ISSUE_DATE,
    sl.LIC_TYPE_CODE LIC_TYPE_CODE,
    sl.LIC_TYPE_DESC LIC_TYPE_DESC,
    sl.LICENSE_STATUS LICENSE_STATUS,
    sl.LIC_STATUS_TEXT LIC_STATUS_TEXT,
    sl.ECONOMIC_SECTOR_CODE ECONOMIC_SECTOR_CODE,
    sl.LIC_NUMBER LIC_NUMBER,
    sl.LIC_PRINT_STATUS LIC_PRINT_STATUS,
    sl.TRANSACTION_TYPE TRANSACTION_TYPE,
    sl.NO_OF_LINES NO_OF_LINES,
    sl.LIC_ACTIVITY_1 LIC_ACTIVITY_1,
    sl.LIC_ACTIVITY_2 LIC_ACTIVITY_2,
    sl.LIC_ACTIVITY_3 LIC_ACTIVITY_3,
    sl.LIC_ACTIVITY_4 LIC_ACTIVITY_4,
    sl.LIC_ACTIVITY_5 LIC_ACTIVITY_5,
    sl.LIC_ACTIVITY_6 LIC_ACTIVITY_6,
    sl.LIC_ACTIVITY_7 LIC_ACTIVITY_7,
    sl.PRIO PRIO,
    sl.SHORT_DESC SHORT_DESC,
    sl.CREATE_DATE CREATE_DATE,
    sl.STAT_SINCE STAT_SINCE
    FROM SAP_LICENSES sl
    WHERE UPPER (sl.CREATE_DATE) IS NOT NULL
    and LIC_NUMBER IN ( SELECT LIC_NUMBER FROM SAP_LICENSES where GROUP BY LIC_NUMBER having COUNT(LIC_NUMBER) >1 and having TRANSACTION_TYPE ='ZRSL'

    ???????????)

    Can some one pls advise ???
    Last edited by ijunaid; 10-27-13 at 10:18. Reason: Elaboration

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    SELECT *
     FROM  (/* Put your query here. */
            SELECT ...
                   ...
             FROM  SAP_LICENSES
             WHERE CREATE_DATE IS NOT NULL
            /* End of your query */
             ORDER BY
                   NULLIF(TRANSACTION_TYPE , 'ZRSL') NULLS FIRST
                 , CREATE_DATE                       DESC
           )
     WHERE ROWNUM = 1
    ;

    By the way,
    I couldn't see LICENSE_NUM(in the description of your requirement) in your query.
    Did you think LIC_NUMBER was same as LICENSE_NUM?
    Or, they were different things?

  3. #3
    Join Date
    Feb 2012
    Posts
    36
    LIC_NUMBER is same as LICENSE_NUMBER buddy,

    Thank you

Posting Permissions

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