CODEWITHSUNDEEP
sqloracle-databaseinlinecommon-table-expression
Rob Morris
Rob Morris

Reputation: 137

How to change CTE coding to INLINE view? Oracle SQL

My system doesn't allow CTE in coding and the first word has to always be SELECT.

Could someone explain (ideally in a Oracle SQL for dummies format) how I would go about changing code written with CTE into INLINE code?

As an example of CTE code:

   WITH table1
   AS (SELECT enquiry_status_log.enquiry_number,
            enquiry_status_log.enquiry_log_number,
            follow_up.follow_up_name,
            enquiry_status_log.follow_up_date,
            RANK ()
            OVER (PARTITION BY enquiry_status_log.enquiry_number
                  ORDER BY enquiry_status_log.enquiry_log_number DESC)
               rn
       FROM enquiry_status_log
            JOIN follow_up
               ON enquiry_status_log.follow_up_code =
                     follow_up.follow_up_code)
SELECT enquiry_number,
     enquiry_log_number,
     follow_up_name,
     follow_up_date
FROM table1
WHERE rn = 1
ORDER BY enquiry_number, enquiry_log_number

How would I write this (please, VERY easy to understand) so that SELECT would be the first word in the code and CTE wasn't used?

Upvotes: 0

Views: 92

Answers (1)

GMB
GMB

Reputation: 222622

Your CTE is used only once in your query, so you can simply turn it to a subquery:

SELECT enquiry_number,
     enquiry_log_number,
     follow_up_name,
     follow_up_date
FROM (
    SELECT 
        e.enquiry_number,
        e.enquiry_log_number,
        f.follow_up_name,
        e.follow_up_date,
        RANK () OVER (
            PARTITION BY e.enquiry_number
            ORDER BY e.enquiry_log_number DESC
        ) rn
       FROM enquiry_status_log e
        JOIN follow_up f ON e.follow_up_code = f.follow_up_code
    ) table1
WHERE rn = 1
ORDER BY enquiry_number, enquiry_log_number

Note: table aliases make the query shorter and easier to read. I modified you query to use them.

Also, it is possible that your query could be rewritten to use a correlated subquery for filtering instead of rank(). In Oracle, this could lead to better performance:

SELECT 
    e.enquiry_number,
    e.enquiry_log_number,
    f.follow_up_name,
    e.follow_up_date
FROM enquiry_status_log e
JOIN follow_up f ON e.follow_up_code = f.follow_up_code
WHERE e.enquiry_log_number = (
    SELECT MAX(enquiry_log_number)
    FROM enquiry_status_log e1
    WHERE e.enquiry_number = e1.enquiry_number
)
ORDER BY e.enquiry_number, e.enquiry_log_number

Upvotes: 3

Related Questions