Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2013
    Posts
    5

    Red face Unanswered: Oracle SQL Query Help Please

    I need help with this oracle query:

    SELECT acp_ver_loan_rate_pct,
    case
    when acp_ver_loan_rate_pct is null then SUM(CASE WHEN acp_ver_loan_rate_pct IS NULL THEN 1 ELSE 0 END)
    else
    COUNT(acp_ver_loan_rate_pct) end AS vv_count
    FROM ALSCMGR.ALSC_C001_ORIG_D
    GROUP BY acp_ver_loan_rate_pct
    ORDER BY vv_count desc
    limit 100

    Error message: ORA-00933 Sql command not properly ended.
    I also tried SELECT top 100 acp_ver_loan_rate_pct,
    ORA-00923: FROM keyword not found where expected
    Also, tried
    where rownum<=100
    Error message: ORA-00933 Sql command not properly ended.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,088
    Provided Answers: 4
    Which database version do you use?

    WHERE clause goes between FROM and GROUP BY clauses, not somewhere you find it appropriate just because. Therefore,
    Code:
    FROM ALSCMGR.ALSC_C001_ORIG_D
    WHERE rownum <= 100
    GROUP BY acp_ver_loan_rate_pct
    would "work", but the question is whether it actually does what you expected it to. Most probably not.

    Here's an option (11g) which might be better. Your query is a source for another SELECT which calculates ROW_NUMBER. The final SELECT takes only values you're interested in.
    Code:
    WITH your_query
         AS (  SELECT acp_ver_loan_rate_pct,
                      CASE
                         WHEN acp_ver_loan_rate_pct IS NULL
                         THEN
                            SUM (
                               CASE
                                  WHEN acp_ver_loan_rate_pct IS NULL THEN 1
                                  ELSE 0
                               END)
                         ELSE
                            COUNT (acp_ver_loan_rate_pct)
                      END
                         AS vv_count
                 FROM ALSCMGR.ALSC_C001_ORIG_D
             GROUP BY acp_ver_loan_rate_pct),
         row_numbers
         AS (SELECT acp_ver_loan_rate_pct,
                    vv_count,
                    ROW_NUMBER () OVER (ORDER BY vv_count DESC) rn  --> consider RANK or DENSE_RANK
               FROM your_query)
      SELECT rn, acp_ver_loan_rate_pct, vv_count
        FROM row_numbers
       WHERE rn <= 100
    ORDER BY rn DESC;
    Consider using RANK and/or DENSE_RANK instead of ROW_NUMBER.

    Note that 12c offers row limiting clause so - if you use that version, have a look at the documentation.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,505
    The way you have the code, it will return random rows. Do the following. This query will work in any version since 8i

    Code:
    SELECT Acp_ver_loan_rate_pct,
    Vv_count
    FROM
    (
      SELECT Acp_ver_loan_rate_pct,
             CASE
                WHEN Acp_ver_loan_rate_pct IS NULL
                THEN
                   SUM (CASE WHEN Acp_ver_loan_rate_pct IS NULL THEN 1 ELSE 0 END)
                ELSE
                   COUNT (Acp_ver_loan_rate_pct)
             END
                AS Vv_count
        FROM Alscmgr.Alsc_c001_orig_d
    GROUP BY Acp_ver_loan_rate_pct
    ORDER BY Vv_count DESC)
    WHERE ROWNUM <= 100;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

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