2 Replies Latest reply: Sep 30, 2013 6:37 PM by Tina RSS

    Need help in SQL query..

    Tina

      Hi Gurus,

      I am trying to write a query.

      If I activated for eg.

      1) a tag in a vehicle as below ,

      2)  and then inactivated the same tag from the vehicle ,

      3)  and activated another tag in the same vehicle.

       

      My Query should pull, that vehicle as tagless if there is no active tag associated with that vehicle.

      If there is both inactive and active tag, then it shouldnt pull it as tagless vehicle.

       

      How to write a query

       

      1)

      CREATE tags ( acct_id number

                            , veh_id NUMBER

                           , tag_id NUMBER

                            , tag_sts VARCHAR2(10) );

       

      -- Activated tag

      INSERT INTO tags VALUES (1001,111, 1, 'ACTIVE');

       

      2)-- Then I Inactivated tag

        UPDATE TAGS T

        SET     T.TAG_STS =  'INACTIVE'

         WHERE T.TAG_ID = 1;

       

      3)--activated tag to the same vehicle and added one more tag to new vehicle.

         We can have multiple vehicle in the same account

       

      INSERT INTO tags VALUES ( 1001 ,111 , 2, 'ACTIVE');

      INSERT INTO tags VALUES (1001,222, 2, 'ACTIVE');

        • 1. Re: Need help in SQL query..
          Frank Kulash

          hI,

           

           

           

          Tina wrote:

           

          Hi Gurus,

          I am trying to write a query.

          If I activated for eg.

          1) a tag in a vehicle as below ,

          2)  and then inactivated the same tag from the vehicle ,

          3)  and activated another tag in the same vehicle.

           

          My Query should pull, that vehicle as tagless if there is no active tag associated with that vehicle.

          If there is both inactive and active tag, then it shouldnt pull it as tagless vehicle.

           

          How to write a query

           

          1)

          CREATE tags ( acct_id number

                                , veh_id NUMBER

                               , tag_id NUMBER

                                , tag_sts VARCHAR2(10) );

           

          -- Activated tag

          INSERT INTO tags VALUES (1001,111, 1, 'ACTIVE');

           

          2)-- Then I Inactivated tag

            UPDATE TAGS T

            SET     T.TAG_STS =  'INACTIVE'

             WHERE T.TAG_ID = 1;

           

          3)--activated tag to the same vehicle and added one more tag to new vehicle.

             We can have multiple vehicle in the same account

           

          INSERT INTO tags VALUES ( 1001 ,111 , 2, 'ACTIVE');

          INSERT INTO tags VALUES (1001,222, 2, 'ACTIVE');

          Thanks for posting the CREATE TABLE and INSERT statements.  Please test and, if necessary, correct them before posting.  There's an error in the CREATE statement.

           

          Here's one way to do what you requested:

           

          SELECT    t.*

          ,         CASE

                        WHEN  0 = COUNT (CASE WHEN tag_sts =   'ACTIVE' THEN 1 END) OVER (PARTITION BY veh_id)

                        THEN  'Tagless'

                        ELSE  'Others'

                    END               AS label

          FROM      tags  t

          ORDER BY  veh_id

          ;

           

          I hope this answers your question.
          If not, point out where the query above is giving the wrong results, and explain, using specific examples, how you get the correct results from the given data in those places.  If you changed the query at all, post your code.

          • 2. Re: Need help in SQL query..
            Tina

            Thanks Frank..it solved my issue.