This discussion is archived
11 Replies Latest reply: Jul 11, 2013 12:54 AM by Etbin RSS

SQL Filter Help Needed

jpm47 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

     

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points