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  |
 |
bluetooth420
Messages: 146
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 #656095 is a reply to message #656093] |
Thu, 22 September 2016 08:13  |
 |
Michel Cadot
Messages: 68645
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');
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*
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 #656099 is a reply to message #656085] |
Thu, 22 September 2016 09:32 |
Solomon Yakobson
Messages: 3273
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 |
Solomon Yakobson
Messages: 3273
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 #656104 is a reply to message #656103] |
Thu, 22 September 2016 10:23 |
|
Michel Cadot
Messages: 68645
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 #656148 is a reply to message #656145] |
Sat, 24 September 2016 15:01 |
Solomon Yakobson
Messages: 3273
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 |
|
bluetooth420
Messages: 146
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 |
 |
BlackSwan
Messages: 26766
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
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 02:27:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
