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.
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,SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ;-)
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
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
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
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
b. Use BETWEEN for date range boundaries.
sorry dvz, but DON'T use between for that! Why: https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_11210-Beware-of-Between.html
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...
ASKER
Hmm... I see what you are saying. Very interesting about using the between.
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
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
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...
But yes, this discussion should not take place here...
ASKER
Thanks for the insight.. I see where there are positives and negatives with the between.
ASKER