Home » SQL & PL/SQL » SQL & PL/SQL » minimize the SELECT statements (oracle)
minimize the SELECT statements [message #689934] Sun, 04 August 2024 12:43 Go to next message
suji6281
Messages: 145
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
)
Thankyou.

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 #689935 is a reply to message #689934] Sun, 04 August 2024 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68701
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Waiting for you feedback in your previous topic...

Re: minimize the SELECT statements [message #689938 is a reply to message #689935] Sun, 04 August 2024 12:53 Go to previous messageGo to next message
suji6281
Messages: 145
Registered: September 2014
Senior Member
Appreciate your help on the previous query.
Re: minimize the SELECT statements [message #689939 is a reply to message #689934] Mon, 05 August 2024 03:00 Go to previous messageGo to next message
John Watson
Messages: 8954
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 Go to previous messageGo to next message
cookiemonster
Messages: 13946
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
     )
The first thing that you need to do is work out what is going on with AMERICAS - it's not referenced in the outer select, which suggests a bug in the query.
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 Go to previous messageGo to next message
cookiemonster
Messages: 13946
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 Go to previous messageGo to next message
cookiemonster
Messages: 13946
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
     )
Re: minimize the SELECT statements [message #689952 is a reply to message #689942] Fri, 16 August 2024 03:36 Go to previous messageGo to next message
suji6281
Messages: 145
Registered: September 2014
Senior Member
Yes, It should be GL when nothing was matched. Thank you so much for your SQL Query on my request.
Re: minimize the SELECT statements [message #689953 is a reply to message #689952] Fri, 16 August 2024 04:54 Go to previous message
cookiemonster
Messages: 13946
Registered: September 2008
Location: Rainy Manchester
Senior Member
So are you happy with that version? It still has 3 select keywords.
That said I would leave it as is unless you're having performance issues with it.
Previous Topic: diffrence between two chacter fields.
Next Topic: Query CLOB column with values in a Set List
Goto Forum:
  


Current Time: Sat Oct 12 14:10:39 CDT 2024