3 Replies Latest reply on Jul 14, 2015 8:01 AM by Gregory Rodrigues

    Query for duplicate Tuner Id

    R B
      Share This:

      Hi All,

       

      Could you please suggest a query through which I can search for duplicate Tuner Id.



      @ : Please suggest.

       

       

      Regards,

      Rahul

        • 1. Re: Query for duplicate Tuner Id
          Young So

          Here is a query for duplicate tuner count:

           

          select name, count(machine_id) No_of_tuners from inv_tuner group by machine_id,name having count(*) >=2
          

           

          Here is the query for list of duplicate tuner list

           

          select a.tuner_id from tuner a,tunerchannel  b where a.tuner_id= b.tuner_id and a.tuner_id in
          (select tuner_id from inv_tunerchannel
          where seq_id in
          (select seq_id from inv_tuner,inv_tunerchannel where
          inv_tuner.tuner_id = inv_tunerchannel.tuner_id and inv_tuner.machine_id in
          (select inv_tuner.machine_id from inv_tuner group by machine_id,installdir
          having count(*) >=2 )))
          and a.tuner_id not in
          (select tuner_id from inv_tunerchannel
          where seq_id in
          (select max(seq_id) from inv_tuner,inv_tunerchannel where
          inv_tuner.tuner_id = inv_tunerchannel.tuner_id and inv_tuner.machine_id in
          (select inv_tuner.machine_id from inv_tuner group by machine_id,installdir
          having count(*) >=2 )
          group by machine_id))
          
          1 of 1 people found this helpful
          • 2. Re: Query for duplicate Tuner Id
            R B

            Hi @Young So

             

            1st query is executing successfully.

             

            But in 2nd query I am facing below error :

             

            Before proceeding, correct the following:

            • There may be a mistake in your syntax. The following SQL error was returned from the database: com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT permission was denied on the object 'tunerchannel', database 'invdb', schema 'dbo'.

             

             

            Regards,

            Rahul

            • 3. Re: Query for duplicate Tuner Id

              Sorry for the late reply been busy with a new addition to my family

               

              Are you looking for the machine names with duplicate tuners or do you want the duplicate tuner_id's

               

              The above error indicates that you are missing the SELECT permission on the tunerchannel table. Try running the query directly on the DB.

               

              Also try this query:

               

              select name, machine_id, count(*) from inv_tuner group by machine_id, lower(installdir) having count(*) >=2

               

              p.s the above query is not tested.