Results 1 to 6 of 6

Thread: A Query Doubt

  1. #1
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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)

    Last edited by krishnaindia2007; 12-20-2007 at 12:14 AM.

  2. #2
    Contributing Member
    Join Date
    Nov 2007
    Answers
    53

    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

    Attached Files Attached Files

  3. #3
    Junior Member
    Join Date
    Dec 2007
    Answers
    1

    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.


  4. #4
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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.


  5. #5
    Junior Member
    Join Date
    Dec 2007
    Answers
    4

    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.


  6. #6
    Junior Member
    Join Date
    Oct 2007
    Answers
    1

    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact