Home » SQL & PL/SQL » SQL & PL/SQL » Nested Query- Result Achieved. Performance improvement ? (10.2.0.1.0)
Nested Query- Result Achieved. Performance improvement ? [message #656085] Thu, 22 September 2016 04:04 Go to next message
bluetooth420
Messages: 136
Registered: November 2011
Senior Member
Hi

I need

1) Minimum Salary of each job
2) Mark "*" if that job+salary has commission
3) concatenate the result in one row and ignore job titles.


select wm_concat(req1) 
from 
   (
Select job, sal, min(comm), sal|| decode(sign(nvl(comm,0)), 1, '*') as req1
from emp
where (job,sal) in (
                   select job, min(sal) as salm 
                   from emp 
                   group by job)
group by job, sal, decode(sign(nvl(comm,0)), 1, '*')
order by job
   )



Can this query be further improved by hitting EMP table only once?




Re: Nested Query- Result Achieved. Performance improvement ? [message #656088 is a reply to message #656085] Thu, 22 September 2016 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I give you the first step:
SQL> with
  2    data as (
  3      select sal, comm, job,
  4             rank() over (partition by job order by sal) rk
  5      from emp
  6    )
  7  select sal, comm, job
  8  from data
  9  where rk = 1
 10  /
       SAL       COMM JOB
---------- ---------- ---------
      3000            ANALYST
      3000            ANALYST
       800            CLERK
      2450            MANAGER
      5000            PRESIDENT
      1250        500 SALESMAN
      1250       1400 SALESMAN
Now you have to decide what you want if the minimum sal macthes with both a row without comm and one with comm: '*' or not '*'?

Re: Nested Query- Result Achieved. Performance improvement ? [message #656093 is a reply to message #656088] Thu, 22 September 2016 07:46 Go to previous messageGo to next message
bluetooth420
Messages: 136
Registered: November 2011
Senior Member
Null comm is to be replace by 0
and i think there is an asuumption that there will be no two same records having same sal and comm.

Even if it is there than minimum comm is to be picked.
hence the code
decode(sign(nvl(comm,0)), 1, '*')
Re: Nested Query- Result Achieved. Performance improvement ? [message #656095 is a reply to message #656093] Thu, 22 September 2016 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Even if it is there than minimum comm is to be picked.
hence the code
This is not what your query does, you have been lucky that rows in a job have either all a comm or none a comm.
Your query currently gives (the inner part):
SQL> Select job, sal, min(comm), sal|| decode(sign(nvl(comm,0)), 1, '*') as req1
  2  from emp
  3  where (job,sal) in (
  4                     select job, min(sal) as salm
  5                     from emp
  6                     group by job)
  7  group by job, sal, decode(sign(nvl(comm,0)), 1, '*')
  8  order by job
  9  /
JOB              SAL  MIN(COMM) REQ1
--------- ---------- ---------- -----------------------------------------
ANALYST         3000            3000
CLERK            800            800
MANAGER         2450            2450
PRESIDENT       5000            5000
SALESMAN        1250        500 1250*

Just add this row:
insert into emp (empno, ename, sal, comm, job) values (0, 'bluetooth', 3000, 100, 'ANALYST');
Then your query gives:
SQL> Select job, sal, min(comm), sal|| decode(sign(nvl(comm,0)), 1, '*') as req1
  2  from emp
  3  where (job,sal) in (
  4                     select job, min(sal) as salm
  5                     from emp
  6                     group by job)
  7  group by job, sal, decode(sign(nvl(comm,0)), 1, '*')
  8  order by job
  9  /
JOB              SAL  MIN(COMM) REQ1
--------- ---------- ---------- -----------------------------------------
ANALYST         3000        100 3000*
ANALYST         3000            3000
CLERK            800            800
MANAGER         2450            2450
PRESIDENT       5000            5000
SALESMAN        1250        500 1250*
You have 2 lines for ANALYST.
So is this the first or the second one you want?

[Updated on: Thu, 22 September 2016 08:14]

Report message to a moderator

Re: Nested Query- Result Achieved. Performance improvement ? [message #656096 is a reply to message #656095] Thu, 22 September 2016 08:36 Go to previous messageGo to next message
bluetooth420
Messages: 136
Registered: November 2011
Senior Member
Yes, i want the first one.

In fact there will no NULL value in the actual data.

There will be either 0 or 1.

If 0 then ignore it else mark *.
Re: Nested Query- Result Achieved. Performance improvement ? [message #656098 is a reply to message #656096] Thu, 22 September 2016 09:31 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you don't want the minimum comm but the maximum one, if the maximum is greater than 0 then '*'.

Re: Nested Query- Result Achieved. Performance improvement ? [message #656099 is a reply to message #656085] Thu, 22 September 2016 09:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
select  listagg(
                job || ' ' || min(sal) ||
                nvl2(
                     max(comm) keep(dense_rank first order by sal),
                     '*',
                     null
                    ),
                 ', '
                ) within group(order by job) jon_list
  from  emp
  group by job
/

JON_LIST
---------------------------------------------------------------------
ANALYST 3000, CLERK 800, MANAGER 2450, PRESIDENT 5000, SALESMAN 1250*

SQL> 

SY.
Re: Nested Query- Result Achieved. Performance improvement ? [message #656100 is a reply to message #656099] Thu, 22 September 2016 09:34 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oh, and:

SQL> insert into emp (empno, ename, sal, comm, job) values (0, 'bluetooth', 3000, 100, 'ANALYST');

1 row created.

SQL> select  listagg(
  2                  job || ' ' || min(sal) ||
  3                  nvl2(
  4                       max(comm) keep(dense_rank first order by sal),
  5                       '*',
  6                       null
  7                      ),
  8                   ', '
  9                  ) within group(order by job) jon_list
 10    from  emp
 11    group by job
 12  /

JON_LIST
--------------------------------------------------------------------------------
ANALYST 3000*, CLERK 800, MANAGER 2450, PRESIDENT 5000, SALESMAN 1250*

SQL> rollbac;
Rollback complete.
SQL> 

SY.
Re: Nested Query- Result Achieved. Performance improvement ? [message #656101 is a reply to message #656100] Thu, 22 September 2016 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

LISTAGG does not exist in 10g.
And it is very nice to you to directly provide a solution when I try to lead OP to the solution by himself, really very nice.
Do you think I couldn't provide a solution myself, even a correct one for 10g?

[Updated on: Thu, 22 September 2016 09:39]

Report message to a moderator

Re: Nested Query- Result Achieved. Performance improvement ? [message #656103 is a reply to message #656101] Thu, 22 September 2016 10:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
Ah, I missed 10G. In regards to providing solution - your very first post does it (except concatenating rows together). I just showed it can be (and IMHO should be) done using aggregation (functions FIRST/LAST do exist in 10G).

SY.
Re: Nested Query- Result Achieved. Performance improvement ? [message #656104 is a reply to message #656103] Thu, 22 September 2016 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
- your very first post does it
No, it does not, maybe it does for you as SQL expert but it does not for others, it is just the core (well, I could just give the "data" part), it is also there to show there can be a problem with the specification (and there is).
Then you come, pick the work I did with OP and provide your solution.
Maybe you could wait I provide a (real) solution before posting a better one or maybe you could explain it.

[Updated on: Thu, 22 September 2016 10:24]

Report message to a moderator

Re: Nested Query- Result Achieved. Performance improvement ? [message #656105 is a reply to message #656085] Thu, 22 September 2016 10:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
bluetooth420 wrote on Thu, 22 September 2016 05:04
Hi
select wm_concat(req1)
Never use undocumented functions. WM_CONCAT is undocumented and can change at any time. In fact WM_CONCAT is gone in 12C and I already saw many posts where people are hit with it (ticking bombs go off sooner or later).

SY.

[Updated on: Thu, 22 September 2016 10:29]

Report message to a moderator

Re: Nested Query- Result Achieved. Performance improvement ? [message #656106 is a reply to message #656105] Thu, 22 September 2016 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, and this would be my next remark when the core of the problem would be solved.
Of course, this does not matter.

Re: Nested Query- Result Achieved. Performance improvement ? [message #656140 is a reply to message #656098] Sat, 24 September 2016 06:43 Go to previous messageGo to next message
bluetooth420
Messages: 136
Registered: November 2011
Senior Member
Michel Cadot wrote on Thu, 22 September 2016 19:31

So you don't want the minimum comm but the maximum one, if the maximum is greater than 0 then '*'.

No i want minimum to be checked. Can not ignore minimum. There will be no NULL values. Negative, zero or positive.
So
if Sign(comm)=1
then '*'
else
 then ' '
end if;
Quote:

JOB SAL MIN(COMM) REQ1
ANALYST 3000 100 3000*
ANALYST 3000 3000
In above case after adding bluetooth in emp table, 3000 without * is needed.
Re: Nested Query- Result Achieved. Performance improvement ? [message #656141 is a reply to message #656140] Sat, 24 September 2016 08:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
So change NVL2 in my suggestion to CASE, MAX to MIN and COMM to NVL(COMM,-1).

SY.
Re: Nested Query- Result Achieved. Performance improvement ? [message #656145 is a reply to message #656141] Sat, 24 September 2016 10:55 Go to previous messageGo to next message
bluetooth420
Messages: 136
Registered: November 2011
Senior Member
Respect SY

 1  select  listagg(
 2                   job || ' ' || min(sal) ||
 3                   nvl2(
 4                        max(comm) keep(dense_rank first order by sal),
 5                        '*',
 6                        null
 7                       ),
 8                    ', '
 9                   ) within group(order by job) jon_list
10     from  emp
11*    group by job


is giving me error

ERROR at line 9:
ORA-00923: FROM keyword not found where expected

Kindly guide
Re: Nested Query- Result Achieved. Performance improvement ? [message #656146 is a reply to message #656145] Sat, 24 September 2016 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I already answered to this, read my posts.

Quote:
No i want minimum to be checked. Can not ignore minimum.
So why your result is with * as the minimum is 0 (just convert NULL to 0 in the example).

Quote:
So
Code: [Select all] [Show/ hide]

if Sign(comm)=1
then '*'
else
then ' '
end if;
This is completely meaningless. You don't test a minimum or a maximum but EVERY value with this test.

Re: Nested Query- Result Achieved. Performance improvement ? [message #656148 is a reply to message #656145] Sat, 24 September 2016 15:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
bluetooth420 wrote on Sat, 24 September 2016 11:55

is giving me error
As Michel already noted I missed you are on 10G where LISTAGG doesn't exists. You can use XMLAGG:

with t as (
           select  job,
                   job || ' ' || min(sal) ||
                   nvl2(
                        max(comm) keep(dense_rank first order by sal),
                        '*',
                        null
                       ) element
             from  emp
             group by job
          )
select  rtrim(
              xmlagg(
                     xmlelement(
                                e,
                                element,
                                ', '
                               ).extract('//text()')
                     order by job
                    ).GetClobVal(),
              ', '
             ) job_list
  from  t
/

JOB_LIST
---------------------------------------------------------------------
ANALYST 3000, CLERK 800, MANAGER 2450, PRESIDENT 5000, SALESMAN 1250*

SQL> 

SY.
Re: Nested Query- Result Achieved. Performance improvement ? [message #656154 is a reply to message #656148] Sun, 25 September 2016 12:19 Go to previous messageGo to next message
bluetooth420
Messages: 136
Registered: November 2011
Senior Member
Select cname, invno, invdate, dues,  EOD_DUE,    max(Days) , decode(rk, 1, '*', null) as FALG   
from (
Select cname, invno, invdate, dues,  EOD_DUE,    Days, rtot, 
            min(fate) KEEP (DENSE_rank  FIRST order by fate) over (partition by   invno) rk
from (
Select cname, invno, invdate, dues, EOD_DUE,  amount, dated,  rtot, (dated-invdate) Days, decode(    sign(        ( (eod_due*((:pcent)/100))   -rtot)                   ), 1, 1, 0) as fate
from (
select a.ccode, d.cname, b.invno, invdate, dated, eod_due, sum(qty*uprice) dues,  amount ,
 sum(amount) over (partition by a.ccode, b.invno order by a.ccode,  dated, ref) rtot
from vcusthist1 a, inv0s b, inv1s c, cust d
where a.ccode=b.ccode and d.ccode=a.ccode
and b.invno=c.invno
and a.ccode=nvl(:mccode, a.ccode)
and aors='S'
and b.invdate<a.dated
and invdate between :mbdate  and :medate 
group by a.ccode, d.cname, b.invno, invdate, dated, amount , eod_due, ref
order by cname, invno, invdate, dated, ref))
where rtot - amount <= (eod_due*(:pcent/100))
)
group by cname, invno, invdate, dues,  EOD_DUE,    rk
order by invno, invdate

I made the basic DATA report.

I will concatenate it in one row soon.
Re: Nested Query- Result Achieved. Performance improvement ? [message #656155 is a reply to message #656154] Sun, 25 September 2016 12:22 Go to previous message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
SELECT cname, 
       invno, 
       invdate, 
       dues, 
       eod_due, 
       Max(days), 
       Decode(rk, 1, '*', 
                  NULL) AS FALG 
FROM   (SELECT cname, 
               invno, 
               invdate, 
               dues, 
               eod_due, 
               days, 
               rtot, 
               Min(fate) 
                 keep (dense_rank first ORDER BY fate) over ( 
                   PARTITION BY invno) rk 
        FROM   (SELECT cname, 
                       invno, 
                       invdate, 
                       dues, 
                       eod_due, 
                       amount, 
                       dated, 
                       rtot, 
                       ( dated - invdate ) 
                       Days, 
                       Decode(Sign(( ( eod_due * ( ( :pcent ) / 100 ) ) - rtot ) 
                              ), 1, 1 
                       , 
                       0) 
                       AS fate 
                FROM   (SELECT a.ccode, 
                               d.cname, 
                               b.invno, 
                               invdate, 
                               dated, 
                               eod_due, 
                               SUM(qty * uprice)                 dues, 
                               amount, 
                               SUM(amount) 
                                 over ( 
                                   PARTITION BY a.ccode, b.invno 
                                   ORDER BY a.ccode, dated, ref) rtot 
                        FROM   vcusthist1 a, 
                               inv0s b, 
                               inv1s c, 
                               cust d 
                        WHERE  a.ccode = b.ccode 
                               AND d.ccode = a.ccode 
                               AND b.invno = c.invno 
                               AND a.ccode = Nvl(:mccode, a.ccode) 
                               AND aors = 'S' 
                               AND b.invdate < a.dated 
                               AND invdate BETWEEN :mbdate AND :medate 
                        GROUP  BY a.ccode, 
                                  d.cname, 
                                  b.invno, 
                                  invdate, 
                                  dated, 
                                  amount, 
                                  eod_due, 
                                  ref 
                        ORDER  BY cname, 
                                  invno, 
                                  invdate, 
                                  dated, 
                                  ref)) 
        WHERE  rtot - amount <= ( eod_due * ( :pcent / 100 ) )) 
GROUP  BY cname, 
          invno, 
          invdate, 
          dues, 
          eod_due, 
          rk 
ORDER  BY invno, 
          invdate 

http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz
Previous Topic: Count Adult Age in Range Time in 2016
Next Topic: Date Time problem in sql server
Goto Forum:
  


Current Time: Sat Jan 20 20:01:43 CST 2018

Total time taken to generate the page: 0.29948 seconds