Home » SQL & PL/SQL » SQL & PL/SQL » minimize the SELECT statements (oracle)
| minimize the SELECT statements [message #689934] |
Sun, 04 August 2024 12:43  |
 |
suji6281
Messages: 151
Registered: September 2014
|
Senior Member |
|
|
Hi All,
Please help me to simplify the below SQL. Given below SQL has two SELECT statements. can you please help me to simplify below SQL with only one SELECT statement.
SELECT DECODE(RIBS,'1',DECODE(STAR,'1','DP','IB'),DECODE(STAR,'1','CB','GL')) FROM (SELECT
MAX(DECODE(L.TREE_NAME,'STAT_PRODUCTS','1','0')) AS AMERICAS,
MAX(DECODE(L.TREE_NAME,'RIBS_PRODUCTS','1','0')) AS RIBS,
MAX(DECODE(L.TREE_NAME,'STAR_PRODUCTS','1','0')) AS STAR
FROM PSTREENODE N JOIN PSTREELEAF L ON (N.SETID=L.SETID AND N.SETCNTRLVALUE=L.SETCNTRLVALUE AND N.TREE_NAME=L.TREE_NAME AND N.EFFDT=L.EFFDT
AND N.TREE_NODE_NUM=L.TREE_NODE_NUM AND N.TREE_NAME IN ('STAT_PRODUCTS','STAR_PRODUCTS','RIBS_PRODUCTS'))
AND N.EFFDT = (SELECT MAX(ED.EFFDT) FROM PSTREENODE ED WHERE ED.SETID=N.SETID AND ED.SETCNTRLVALUE=N.SETCNTRLVALUE AND ED.TREE_NAME=N.TREE_NAME)
AND L.RANGE_FROM = <input value>
GROUP BY L.RANGE_FROM
)
Regards
Suji
--moderator update: corrected the use of [code] tags
[Updated on: Mon, 05 August 2024 02:55] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: minimize the SELECT statements [message #689939 is a reply to message #689934] |
Mon, 05 August 2024 03:00  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
I see three SELECT statements. Apart from that, what are you trying to achieve? For example, do you think there is a problem with the execution plan? Or is it that you don't understand the query?
|
|
|
|
| Re: minimize the SELECT statements [message #689940 is a reply to message #689939] |
Mon, 05 August 2024 04:48 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Let's format that properly shall we:
SELECT DECODE(RIBS,'1',DECODE(STAR,'1','DP','IB'),DECODE(STAR,'1','CB','GL'))
FROM (SELECT MAX(DECODE(L.TREE_NAME,'STAT_PRODUCTS','1','0')) AS AMERICAS,
MAX(DECODE(L.TREE_NAME,'RIBS_PRODUCTS','1','0')) AS RIBS,
MAX(DECODE(L.TREE_NAME,'STAR_PRODUCTS','1','0')) AS STAR
FROM PSTREENODE N
JOIN PSTREELEAF L ON (N.SETID = L.SETID
AND N.SETCNTRLVALUE = L.SETCNTRLVALUE
AND N.TREE_NAME = L.TREE_NAME
AND N.EFFDT = L.EFFDT
AND N.TREE_NODE_NUM = L.TREE_NODE_NUM
AND N.TREE_NAME IN ('STAT_PRODUCTS','STAR_PRODUCTS','RIBS_PRODUCTS')
)
AND N.EFFDT = (SELECT MAX(ED.EFFDT)
FROM PSTREENODE ED
WHERE ED.SETID = N.SETID
AND ED.SETCNTRLVALUE = N.SETCNTRLVALUE
AND ED.TREE_NAME = N.TREE_NAME)
AND L.RANGE_FROM = <input value>
GROUP BY L.RANGE_FROM
)
So should that outer decode be referencing americas as well as ribs and star, or should the inner select not have americas?
[Updated on: Mon, 05 August 2024 04:49] Report message to a moderator |
|
|
|
| Re: minimize the SELECT statements [message #689941 is a reply to message #689940] |
Mon, 05 August 2024 08:25 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
After that you really need to answer John's question - why do you want to reduce the number of selects?
Nested selects aren't inherently bad, sometimes they're the only way to do something, sometimes they make the query more performant, often they make it more readable.
If readability is a concern then I'd try replacing the outer nested decode with a CASE statement.
|
|
|
|
| Re: minimize the SELECT statements [message #689942 is a reply to message #689941] |
Mon, 05 August 2024 09:53 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Looking at query more closely the query returns 'GL' when there are no entries for RIBS_PRODUCTS or STAR_PRODUCTS, but to get GL rather than no rows there would need to be any entry for STAT_PRODCUTS (what the americas column checks), but you can get the same result without americas in the select so long as STAT_PRODUCTS is in the IN clause.
Here's a version using case with comments that makes what's going on clearer:
SELECT CASE WHEN has_ribs = 'Y' and has_star = 'Y' then 'DP' -- has ribs and star
when has_ribs = 'Y' and has_star = 'N' then 'IB' -- has ribs but not star
when has_ribs = 'N' and has_star = 'Y' then 'CB' -- has star but not ribs
else 'GL' -- has neither star nor ribs, so must have stat
end
FROM (SELECT MAX(DECODE(L.TREE_NAME,'RIBS_PRODUCTS','Y','N')) AS has_ribs,
MAX(DECODE(L.TREE_NAME,'STAR_PRODUCTS','Y','N')) AS has_star
FROM PSTREENODE N
JOIN PSTREELEAF L ON N.SETID = L.SETID
AND N.SETCNTRLVALUE = L.SETCNTRLVALUE
AND N.TREE_NAME = L.TREE_NAME
AND N.EFFDT = L.EFFDT
AND N.TREE_NODE_NUM = L.TREE_NODE_NUM
AND N.TREE_NAME IN ('STAR_PRODUCTS','RIBS_PRODUCTS', 'STAT_PRODUCTS')
AND N.EFFDT = (SELECT MAX(ED.EFFDT)
FROM PSTREENODE ED
WHERE ED.SETID = N.SETID
AND ED.SETCNTRLVALUE = N.SETCNTRLVALUE
AND ED.TREE_NAME = N.TREE_NAME
)
WHERE L.RANGE_FROM = <input value>
GROUP BY L.RANGE_FROM
)
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 08:10:55 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
