Oracle Query Syntax structure

Hello Experts,

I am trying to write a nested case statement but I am not sure what is the best way to structure it to make it more readable.  Meaning how you indent the case statements.

Can someone please help.  Wanting it to look professionally written.

            (CASE
                WHEN UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') < TO_DATE('08/12/2013','MM/DD/YYYY') THEN
               CASE WHEN PEV.HOURS_TO_RESOLVE = 0 OR PEV.HOURS_TO_RESOLVE IS NULL THEN 1 ELSE PEV.HOURS_TO_RESOLVE END
                WHEN UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') > TO_DATE('08/12/2013','MM/DD/YYYY') 
                AND UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') < TO_DATE('09/2/2013','MM/DD/YYYY') THEN
                 CASE WHEN PEV.HOURS_TO_RESOLVE = 0 OR PEV.HOURS_TO_RESOLVE IS NULL THEN 1 
                 WHEN PEV.HOURS_TO_RESOLVE < 1 THEN (1 + PEV.HOURS_TO_RESOLVE) ELSE PEV.HOURS_TO_RESOLVE END
                 ELSE
                CASE WHEN PEV.HOURS_TO_RESOLVE = 0 OR PEV.HOURS_TO_RESOLVE IS NULL THEN 1 ELSE(1 + PEV.HOURS_TO_RESOLVE) END
            END) AS HOURS_TO_RESOLVE,

Open in new window

Maliki HassaniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
1. Move the ELSE to separate lines (see line 7)
2. Indent the subordinate (dependent) clauses by some consistent value, perhaps 4 characters.

The common free tools, including SQL*Developer, or Textpad, or Notepad++, can all reformat a statement.  Just pick one to be consistent.

A quick example, but my indents didn't copy well.

 (CASE WHEN UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') <
         TO_DATE('08/12/2013','MM/DD/YYYY')
              THEN CASE WHEN PEV.HOURS_TO_RESOLVE = 0 OR PEV.HOURS_TO_RESOLVE IS NULL
                  THEN 1
                  ELSE PEV.HOURS_TO_RESOLVE
       END
       WHEN UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') >
         TO_DATE('08/12/2013','MM/DD/YYYY') AND
         UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') <
         TO_DATE('09/2/2013','MM/DD/YYYY')
         THEN CASE WHEN PEV.HOURS_TO_RESOLVE =
            0 OR PEV.HOURS_TO_RESOLVE IS NULL
            THEN 1
            WHEN PEV.HOURS_TO_RESOLVE < 1
            THEN (1 + PEV.HOURS_TO_RESOLVE)
                ELSE PEV.HOURS_TO_RESOLVE
       END
       ELSE CASE WHEN PEV.HOURS_TO_RESOLVE = 0 OR PEV.HOURS_TO_RESOLVE IS NULL
           THEN 1
         ELSE(1 + PEV.HOURS_TO_RESOLVE)
       END
  END) AS HOURS_TO_RESOLVE,
0
AielloJCommented:
I prefer a structure like block structured coding / programming.  The WHEN, THEN, ELSE, and END, statements should line up.

I also prefer 2 spaces to 4.  Make sure your editor is configured to not convert spaces to tabs.  See below

(
 CASE
   WHEN
     UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') < TO_DATE('08/12/2013','MM/DD/YYYY')
   THEN
     CASE
       WHEN
         PEV.HOURS_TO_RESOLVE = 0 OR PEV.HOURS_TO_RESOLVE IS NULL
         THEN
         1
       ELSE
         PEV.HOURS_TO_RESOLVE
       END
        
         WHEN
         UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') > TO_DATE('08/12/2013','MM/DD/YYYY')
        AND
         UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') < TO_DATE('09/2/2013','MM/DD/YYYY')
       THEN
         CASE
           WHEN
             PEV.HOURS_TO_RESOLVE = 0
                OR
             PEV.HOURS_TO_RESOLVE IS NULL
           THEN
             1
                  
           WHEN
             PEV.HOURS_TO_RESOLVE < 1
           THEN
             (1 + PEV.HOURS_TO_RESOLVE)
           ELSE
             PEV.HOURS_TO_RESOLVE
           END
       ELSE
         CASE
           WHEN
             PEV.HOURS_TO_RESOLVE = 0
            OR
             PEV.HOURS_TO_RESOLVE IS NULL
           THEN
             1
           ELSE
             (1 + PEV.HOURS_TO_RESOLVE)
           END
 END
) AS HOURS_TO_RESOLVE,

Best regards,

AielloJ
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
PL/SQL Developer (Allround Automations) "beautifier" re-formats this way:

select (case
         when UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') <
              TO_DATE('08/12/2013', 'MM/DD/YYYY') then
          case
            when PEV.HOURS_TO_RESOLVE = 0 or PEV.HOURS_TO_RESOLVE is null then
             1
            else
             PEV.HOURS_TO_RESOLVE
          end
         when UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') >
              TO_DATE('08/12/2013', 'MM/DD/YYYY') and
              UDF_CONVERT_UNIX_DATETIME(PEV.DATE_CREATED, 'US/Eastern') <
              TO_DATE('09/2/2013', 'MM/DD/YYYY') then
          case
            when PEV.HOURS_TO_RESOLVE = 0 or PEV.HOURS_TO_RESOLVE is null then
             1
            when PEV.HOURS_TO_RESOLVE < 1 then
             (1 + PEV.HOURS_TO_RESOLVE)
            else
             PEV.HOURS_TO_RESOLVE
          end
         else
          case
            when PEV.HOURS_TO_RESOLVE = 0 or PEV.HOURS_TO_RESOLVE is null then
             1
            else
             (1 + PEV.HOURS_TO_RESOLVE)
          end
       end) as HOURS_TO_RESOLVE
  from dual;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Maliki HassaniAuthor Commented:
Thank you, I really like the beautifier.. Will give that a try.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Most of these tools, like Toad, PL/SQL Dev... can be customized to (auto)-format your statements and program codes (procs, funcs, packages, triggers...). virtually all of them can be tested via trial downloads, so just give it a try and find the one that fits your style and needs ;-)
0
DavidSenior Oracle Database AdministratorCommented:
Lance, as you can see there are acceptable varieties and no one best practice.  Something we haven't mentioned, though, that improves the "professional" qualify:  comment your code.  A less-familiar person should be able to walk through such a procedure, and readily describe the logic flow.

Off topic, but there are Oracle functions that would improve your performance, if you wish.  What catches my eye includes:
a.  Pass the date strings into the statement as pre-defined variables, e.g., &&BEGIN_DATE and &&END_DATE.
b.  Use BETWEEN for date range boundaries.
c.  Use NVL to replace nulls with a string, such as zero.  Your last bit of logic could be reduced to 1 + NVL(pev.hours_to_resolve,0)  because zero plus one equals one, of course.
d.  The actual SQL syntax, at least to Oracle, requires the column name on the equation's left hand:  pev.hours_to_resolve = 1 + NVL(pev.hours_to_resolve,0).
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
b.  Use BETWEEN for date range boundaries.

sorry dvz, but DON'T use between for that! Why: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_11210-Beware-of-Between.html
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
d.  The actual SQL syntax, at least to Oracle, requires the column name on the equation's left hand:  pev.hours_to_resolve = 1 + NVL(pev.hours_to_resolve,0).

what do you mean?!

In case of an IF-THEN condition the sides doesn't matter as long as you keep in mind, that Oracle solves this from left to right in general...
0
Maliki HassaniAuthor Commented:
Hmm...  I see what you are saying.  Very interesting about using the between.
0
DavidSenior Oracle Database AdministratorCommented:
Alex, we may have to take this one off-line :) BETWEEN, as your article confirms, is inclusive.  A search from letters BETWEEN A and C will return the set A|B|C, not B alone.  If the condition is to exclude the boundaries, then yes, the style used by the author will be required.

Another source, from http://www.techonthenet.com/sql/between.php:

SQL BETWEEN Condition - Date example

You can also use the SQL BETWEEN condition to retrieve values within a date range.

For example:

SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

This SQL BETWEEN condition example would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive). It would be equivalent to the following SQL SELECT statement:

SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Actually it's not my article, but you may "talk" to PortletPaul as he stated this "beware of between" and I totally agree with him. Apart from that, in the end it's up to you / LANCE_S_P to choose ;-)
But yes, this discussion should not take place here...
0
Maliki HassaniAuthor Commented:
Thanks for the insight..  I see where there are positives and negatives with the between.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.