11 Replies Latest reply: Jul 11, 2013 2:54 AM by Etbin RSS

    SQL Filter Help Needed

    jpm47

      Hello,

       

      I am in need of some SQL help as I am not sure how to do it.  I want to only return rows of data that meet the following criteria:

       

      1) in tableA columnA it could have a value shown as such:  A,B,1,2,3,5,8

      2) in tableA columnB it shows one of the following four variables (Test1, Test2, Test3, Test4)

      3) in tableA columnC it will show a date.

      4) in tableB there is a row for each day of the year. 

      5) in tableB there is a column that matches each of the four variables in #2 above. 

       

      First it should match the date in tableA ColumnC to the row in tableB that matches.  Then it should then find the column in tableB that matches to the value in tableA columnB.  in that cell it will have a single value from A-D or 1-9.  If it is an A for example, it will need to check against tableA columnA.  if it has an A in it, it will return it.  If it does not, it will not.  

       

      Does anyone have any idea how to write this?  Is this possible?

       

      Thank you,

      Jeff

        • 1. Re: SQL Filter Help Needed
          Frank Kulash

          Hi, Jeff,

           

          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.

          Explain, using specific examples, how you get those results from that data.

          Simplify the problem as much as possible.  For example, I know you really have 7 possible values in tablea.columna, but, just for now, could you pretend that you only have 2 or 3 different values.  Likewise, could you pretend that January 1, 2 and 3 are the only days in the year?  That way, you won't have to post so much sample data.  We'll find a solution that is easy to adapt to your real values.

          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

           

          I can't tell what you want.  I can thing of several things you might mean, and they are all possible in Oracle.  For example, when you say that tableb must have a row for every day of the year, are you talking about any particular year, or would that requirement be met if all 366 days are present, regardless of what year?    When counting days of the year, do you include rows that do not match tablea?  Pick sample data that makes this as clear as you can.

          • 2. Re: SQL Filter Help Needed
            jpm47

            Create table scripts:

             

            CREATE TABLE TABLEA

            (IDFIELD VARCHAR2(100 BYTE),

            DATEFIELD DATE,

            CODEFIELD VARCHAR2(100 BYTE),

            VARIABLEFIELD VARCHAR2(100 BYTE)

            );

             

             

            CREATE TABLE TABLEB

            (DATEDEFINEFIELD DATE,

            VARIABLE1 VARCHAR2(100 BYTE),

            VARIABLE2 VARCHAR2(100 BYTE),

            VARIABLE3 VARCHAR2(100 BYTE),

            VARIABLE4 VARCHAR2(100 BYTE)

            );

             

            Insert Script:

             

            INSERT ALL

            INTO TABLEA (IDFIELD, DATEFIELD, CODEFIELD, VARIABLEFIELD)

            VALUES

            (1, to_date('01/01/2013', 'MM/DD/YYYY'), '1,2,3', 'VARIABLE2')

            INTO TABLEA (IDFIELD, DATEFIELD, CODEFIELD, VARIABLEFIELD)

            VALUES

            (2, to_date('01/01/2013', 'MM/DD/YYYY'), '1', 'VARIABLE3')

            INTO TABLEA (IDFIELD, DATEFIELD, CODEFIELD, VARIABLEFIELD)

            VALUES

            (3, to_date('01/01/2013', 'MM/DD/YYYY'), '2,3', 'VARIABLE1')

            INTO TABLEA (IDFIELD, DATEFIELD, CODEFIELD, VARIABLEFIELD)

            VALUES

            (4, to_date('01/01/2013', 'MM/DD/YYYY'), '1,3', 'VARIABLE4')

            INTO TABLEA (IDFIELD, DATEFIELD, CODEFIELD, VARIABLEFIELD)

            VALUES

            (5, to_date('01/01/2013', 'MM/DD/YYYY'), '1,2', 'VARIABLE2')

            INTO TABLEA (IDFIELD, DATEFIELD, CODEFIELD, VARIABLEFIELD)

            VALUES

            (6, to_date('02/05/2013', 'MM/DD/YYYY'), '1,2,3', 'VARIABLE1')

            INTO TABLEA (IDFIELD, DATEFIELD, CODEFIELD, VARIABLEFIELD)

            VALUES

            (7, to_date('02/05/2013', 'MM/DD/YYYY'), '2,3', 'VARIABLE3')

            INTO TABLEA (IDFIELD, DATEFIELD, CODEFIELD, VARIABLEFIELD)

            VALUES

            (8, to_date('02/05/2013', 'MM/DD/YYYY'), '1,2,3', 'VARIABLE4')

            INTO TABLEA (IDFIELD, DATEFIELD, CODEFIELD, VARIABLEFIELD)

            VALUES

            (9, to_date('02/05/2013', 'MM/DD/YYYY'), '1,2', 'VARIABLE4')

            INTO TABLEA (IDFIELD, DATEFIELD, CODEFIELD, VARIABLEFIELD)

            VALUES

            (10, to_date('02/05/2013', 'MM/DD/YYYY'), '2,3', 'VARIABLE2')

            SELECT * FROM DUAL;

             

             

            INSERT ALL

            INTO TABLEB (datedefinefield, variable1, variable2, variable3, variable4)

            VALUES

            (to_date('01/01/2013', 'MM/DD/YYYY'), '3', '2', '3', '1')

            INTO TABLEB (datedefinefield, variable1, variable2, variable3, variable4)

            VALUES

            (to_date('02/05/2013', 'MM/DD/YYYY'), '1', '3', '1', '2')

            SELECT * FROM DUAL;

             

             

            This gives you the gist of what I want to accomplish.  Basically I want to say for example on date 1/1/13 for Variable 4 what rows in tableA will be returned.  So the filter would check tableB for the date then the column for variable4.  it sees a 1.  it then would check tableA and check the date field and variable field and for any row that meets the same date/variablefield as well as containing a 1 in the codefield, that row should be returned.  In this example IDFIELD 4 would be returned. 

            • 3. Re: SQL Filter Help Needed
              Frank Kulash

              Hi,

               

              Thanks for posting the samp,e data.  Don't forget to post the exact results you want from that data.  If the results depend on run-time user inputs, post a couple of sets of inputs, and the results you want from the same sample data for each set.

              You want a solution that works in your version of Oracle, don't you?  Say what version that is.

              • 4. Re: SQL Filter Help Needed
                jpm47

                Oracle version does not matter.  This is for a SQL query.  If you need a version lets say 11g newest.  You could be doing this in TOAD, SQL Developer, SQL Plus, doesn't matter. 

                 

                What the query will look like will be something like:

                 

                SELECT *

                FROM TABLEA

                WHERE DATEFIELD='01/01/2013' AND VARIABLEFIELD='VARIABLE4' AND

                (<enter filter criteria created from above>)

                • 5. Re: SQL Filter Help Needed
                  Etbin

                  Maybe NOT TESTED! No Database at hand

                   

                  select x.idfield

                    from table_a x,

                         (select datedefinefield,

                                 :the_variable the_variable,

                                 case :the_variable when 'VARIABLE1'

                                                    then variable1

                                                    when 'VARIABLE2'

                                                    then variable2

                                                    when 'VARIABLE3'

                                                    then variable3

                                                    when 'VARIABLE4'

                                                    then variable4

                                 end the_value

                            from table_b

                           where datedefinefield = to_date(:the_date,'yyyymmdd')

                         ) y

                  where x.datefield = y.datedefinefield

                     and x.variablefield = y.the_variable

                     and instr(','||x.codefield||',',','||y.the_value||',') > 0

                   

                  Regards

                   

                  Etbin

                  • 6. Re: SQL Filter Help Needed
                    jpm47

                    I honestly don't even know how to test that query that you wrote Etbin.  I copied it into SQL Developer and received a popup for "Enter Bind Values" for the_variable and the_date.  Not sure what to do with that. 

                    • 7. Re: SQL Filter Help Needed
                      Etbin

                      enter:

                      • 20130101 for the_date
                      • VARIABLE4 for the_variable

                      and modify table names to match your tables too (tablea , tableb instead of table_a , table_b)

                       

                      Regards

                       

                      Etbin

                      • 8. Re: SQL Filter Help Needed
                        jpm47

                        Do I leave the :?  Do I need single quotes around the values entered when I leave the :?  I am confused.

                         

                        select x.idfield

                          from tablea x,

                               (select datedefinefield,

                                       :VARIABLE4 the_variable

                                       case :VARIABLE4 when 'VARIABLE1'

                                                          then variable1

                                                          when 'VARIABLE2'

                                                          then variable2

                                                          when 'VARIABLE3'

                                                          then variable3

                                                          when 'VARIABLE4'

                                                          then variable4

                                       end the_value

                                  from tableb

                                 where datedefinefield = to_date(:20130101,'yyyymmdd')

                               ) y

                        where x.datefield = y.datedefinefield

                           and x.variablefield = y.the_variable

                           and instr(','||x.codefield||',',','||y.the_value||',') > 0

                         

                        BTW, if i take the colons out and have or don't have single quotes, i get a "ORA-00923: FROM keyword not found where expected error.  Thank you for your help. 

                        • 9. Re: SQL Filter Help Needed
                          Etbin

                          Check the query using hardcoded values first - values taken from your explication

                           

                          select x.idfield

                            from tablea x,

                                 (select datedefinefield,

                                         'VARIABLE4' the_variable,

                                         case 'VARIABLE4' when 'VARIABLE1'

                                                            then variable1

                                                            when 'VARIABLE2'

                                                            then variable2

                                                            when 'VARIABLE3'

                                                            then variable3

                                                            when 'VARIABLE4'

                                                            then variable4

                                         end the_value

                                    from tableb

                                   where datedefinefield = date '2013-01-01'

                                 ) y

                          where x.datefield = y.datedefinefield

                             and x.variablefield = y.the_variable

                             and instr(','||x.codefield||',',','||y.the_value||',') > 0


                          then replace the hardcoded values with bind variables


                          select x.idfield

                            from tablea x,

                                 (select datedefinefield,

                                         :the_variable the_variable,

                                         case :the_variable when 'VARIABLE1'

                                                            then variable1

                                                            when 'VARIABLE2'

                                                            then variable2

                                                            when 'VARIABLE3'

                                                            then variable3

                                                            when 'VARIABLE4'

                                                            then variable4

                                         end the_value

                                    from tableb

                                   where datedefinefield = to_date(:the_date,'yyyymmdd')

                                 ) y

                          where x.datefield = y.datedefinefield

                             and x.variablefield = y.the_variable

                             and instr(','||x.codefield||',',','||y.the_value||',') > 0

                           

                          type the values in the window SQL Developer displays before execution to prompt you for bind variable values

                          VARIABLE4 for the_variable

                          20130101 for the_date

                          no quotes this time then hit the Apply ( not very sure that's the caption) button to start execution and you should get the same result as with hardcoded values.

                          If that works, you can start playing with (submitting values of your choice)


                          Regards


                          Etbin

                          • 10. Re: SQL Filter Help Needed
                            jpm47

                            I got it to work with yours.  Thank you for that Etbin.  One small issue though, I was hoping to do this entirely in the WHERE clause.  Is it possible?  What you wrote above effects the FROM clause and creates a JOIN as well. 

                            • 11. Re: SQL Filter Help Needed
                              Etbin

                               

                               

                              select *

                                from tablea

                              where idfield in (select x.idfield

                                                   from table_a x,

                                                        (select datedefinefield,

                                                                :the_variable the_variable

                                                                case :the_variable when 'VARIABLE1'

                                                                                   then variable1

                                                                                   when 'VARIABLE2'

                                                                                   then variable2

                                                                                   when 'VARIABLE3'

                                                                                   then variable3

                                                                                   when 'VARIABLE4'

                                                                                   then variable4

                                                                end the_value

                                                           from table_b

                                                          where datedefinefield = to_date(:the_date,'yyyymmdd')

                                                        ) y

                                                  where x.datefield = y.datedefinefield

                                                    and x.variablefield = y.the_variable

                                                    and instr(','||x.codefield||',',','||y.the_value||',') > 0

                                                )


                              Maybe (if the select within instr would always return a single row)


                              select *

                                from tablea

                              where datefield = to_date(:the_date,'yyyymmdd')

                                 and variablefield = :the_variable

                                 and instr(','||codefield||',',

                                           (select ','||case :the_variable when 'VARIABLE1'

                                                                           then variable1

                                                                           when 'VARIABLE2'

                                                                           then variable2

                                                                           when 'VARIABLE3'

                                                                           then variable3

                                                                           when 'VARIABLE4'

                                                                           then variable4

                                                        end||','

                                              from tableb

                                             where datedefinefield = to_date(:the_date,'yyyymmdd')

                                           )

                                          ) > 0

                               

                              Regards

                               

                              Etbin

                               

                              Message was edited by: Etbin replaced the second select with a working one