-
A Query Doubt
How to write a query to display records, same plotnumber having more than one gangcode (with different Gangcodes) and commonpool combination . Here I am giving sample data.
PLOTNUMBER LABOURCHARGES GANGCODE COMMONPOOL
P010140121 157.00 T0075 10.00
P010230103 162.00 T0004 10.00
P010330568 157.00 T0115 0.00
P010330568 162.00 T0132 10.00
P010330568 162.00 T0132 0.00
P010330568 162.00 T0132 0.00
P010350114 157.00 T0103 10.00
P010350114 157.00 T0103 10.00
Output should be
P010330568 157.00 T0115 0.00
P010330568 162.00 T0132 0.00
P010330568 162.00 T0132 10.00
My Table structure is
Plotnumber varchar2(12)
LABOURCHARGES number(10,2)
GANGCODE varchar2(6)
COMMONPOOL number(10,2)
-
1 Attachment(s)
Re: A Query Doubt
Dear Krishna,
Kindly try this query and reply. I have tuned it at my end.
I hope it will not take much execution time.
Have a pleasant time
-
Re: A Query Doubt
Hello,
The desired output can be achieved by this query
select * from (Table_name) where
( nvl(plotnumber,'NA') ,nvl(gangcode,'NA'), nvl(commonpool,0) )
in
(select nvl(plotnumber,'NA') ,nvl(gangcode,'NA'), nvl(commonpool,0)
from (Table_name)
where plotnumber = '&plotnumber'
group by nvl(plotnumber,'NA') ,nvl(gangcode,'NA'), nvl(commonpool,0)
)
/
If you want to tune it then a composite index on three of this columns will do.
-
Re: A Query Doubt
Dear avtar259
Thank you for your response. Your query will display all the records from the table. Because your inner query will eliminate duplicate records. But you are selecting all the records once again in outer query based on the result returned by inner query. It will display all the records.My requirement is same plotnumber having more than one gangcode. First query given by srikumar_nair_it will surve that purpose.
-
Simple Select
Hi Krishna,
This query fulfills the requirement. Any performance difference from the ones given by others???
Query:
------
SELECT *
FROM TableName
WHERE plotnumber=&plot
* and & are best practices
Output:
-------
PLOTNUMBER LABOURCHARGES GANGCODE COMMONPOOL
P010330568 157.00 T0115 0.00
P010330568 162.00 T0132 0.00
P010330568 162.00 T0132 10.00
Ranganayaki.
-
Re: A Query Doubt
Please test the below query for your result
select * from out_tab where
plot in (
select t.plot from
(
select min(rowid) rn, plot, gangcode
from out_tab
group by plot, gangcode) t
group by t.plot
having count(t.plot) > 1)
group by plot, labour, gangcode, common_pool;
See the result
PLOT LABOUR GANGCO COMMON_POOL
------------ ---------- ------ -----------
P010330568 157 T0115 0
P010330568 162 T0132 0
P010330568 162 T0132 10
Thanks