1 Reply Latest reply on Jun 3, 2016 3:56 PM by BScarbrough

    Decoding Bit Values for Specific Associated Values

    BScarbrough

      I am attempting to pull a value from a specific record from TABLE_1, which references a bit value from a record in TABLE_2.

       

      For example, the following is a simple example of TABLE_1:

       

      ITEM_TYPE      ITEM_BIT

      Gloves              1

      Shoes               2

      Shoe Laces      4

       

      Let's say that an Item is a shoe, but includes the Shoe Laces along with it (total bit value = 6). Yes, I understand that technically this would be read as '0,1,1' - but for the sake of simplicity, I am using the summed bit value as this is how it is presented in the database and makes it much easier for me to comprehend on replies.

       

      The following is the Item's record, which includes the bit value.

       

      ITEM_NAME     BIT_VALUE       QUANTITY

      Item A               6                         10

      Item B               1                         15

      Item C               4                         20

       

      Now, I am want to pull the ITEM_TYPE from TABLE_A, without multiple records. I prefer the query to report this record as 'Shoes'.

       

      Currently,  I have written the script as follows:

       

      SELECT

      ITEM_NAME,

      DECODE(BITAND(ITEM_TYPE, 2), 2, 'Shoes', '0') AS ITEM_TYPE,

      QUANTITY

      FROM

      TABLE_1 T1, TABLE_2 T2

      WHERE

      ITEM_NAME IN ('Item A', 'Item B')

      AND ITEM_TYPE IN ('Gloves','Shoes','Shoe Laces');

       

      The sample results report as:

       

      ITEM_NAME     ITEM_TYPE     QUANTITY

      Item A               Shoes               10

      Item A               Shoes               10

      Item B               Shoes               15

      Item B               Shoes               15

      Item C               Shoes               20

      Item C               Shoes               20

       

       

      The following table are the desired results that I am looking for, but only have a slight idea of how to get there (I have attempted multiple different methods of writing the script).

      Note: The actual report would have an additional join, so I am not exactly sure how to join a table that does not have any correlating values to avoid the duplicate records.

       

      ITEM_NAME     ITEM_TYPE     QUANTITY

      Item A               Shoes               10

      Item B               Gloves              15

      Item C               Shoe Laces      20

       

       

      I realize some of the examples/samples above are flawed and may not be perfected (I modified the actual report for this post), but hopefully this provides enough information for what I am getting across.

      Just the correct DECODE statement desired would be enough to get me started.

       

       

      Thanks for your help!

        • 1. Re: Decoding Bit Values for Specific Associated Values
          BScarbrough

          FYI, for those interested, I believe that I was able to find a solution. Below is the script that would provide the desired results.

          Note: The actual script I was working with would not want to include 'Shoe Laces', for example.

           

          SELECT

           

          ITEM_NAME,

          ITEM_TYPE,

          SUM(QUANTITY),

          BITAND(ITEM_BIT,BIT_VALUE) AS ITEM_BIT_VALUE

           

          FROM

           

          TABLE_1, TABLE_2

           

          WHERE

           

          ITEM_TYPE IN ('Gloves','Shoes')

          AND QUANTITY > 0

           

          GROUP BY

          ITEM_NAME, ITEM_TYPE, BITAND(ITEM_BIT,BIT_VALUE)


          HAVING

          BITAND(ITEM_BIT,BIT_VALUE) > 0


          The BITAND statement is used to decode which ITEM_BIT values are associated to the BIT_VALUE in TABLE_1.

          I was also required to use a 'SUM' statement in order to utilize the 'HAVING' clause, which reduced the unneeded records (with ITEM_BIT_BALUE = 0).

          However, I am unable to compose the script to suppress the BIT_VALUE field, but I can make do with having it in there.

           

          Hope this helps!