9 Replies Latest reply: May 2, 2014 4:06 PM by Etbin RSS

    sql query

    a_cute_person

      Hello experts,

       

      i need your help for a problem i am facing now.

       

      I have a query which i can't paste here due to NDA, but synopsis of the query is something like this

       

      select * from table where column_value in (parameter) . so the "parameter can be either null or a concatenated values, if user doesn't pass any value to parameter all records must returned from the "table" or select records for which are being passed as string must be used to get a result set.

       

      anybody got into something like this before ??

        • 1. Re: sql query
          Solomon Yakobson

          so the "parameter can be either null or a concatenated values

           

          Do you mean concatenated comma-separated values? If so, and assuming column_value can't contain commas:

           

          select * from table where parameter is null or ',' || parameter || ',' like '%,' || column_value || ',%'

           

          SY.

          • 2. Re: sql query
            a_cute_person

            i need something like this

             

            select * from table where column_value in (comma seperated values) or get all values if parameter is null

            • 3. Re: sql query
              Frank Kulash

              Hi,

               

               

              :) wrote:

               

              i need something like this

               

              select * from table where column_value in (comma seperated values) or get all values if parameter is null

              Isn't that exactly what Solomon's code does?


              • 4. Re: sql query
                a_cute_person

                NO

                 

                query where condition must be something like this

                 

                if parameter is null get all records from the table

                -----------or----------------

                get all records from table where column_value in (comma_seperated_parameter)

                • 5. Re: sql query
                  Etbin

                  NO

                  query where condition must be something like this

                   

                  if parameter is null get all records from the table

                  -----------or----------------

                  get all records from table where column_value in (comma_seperated_parameter)



                  select nvl(:parameter,'null') input_string,

                         emp.*

                    from emp

                  where instr(','||:parameter||',',','||to_char(empno)||',') > 0

                      or :parameter is null

                   

                  Regards

                   

                  Etbin

                  • 6. Re: sql query
                    a_cute_person

                    thats awesome

                     

                    can you do the same on this pseudo code

                     

                    select * from (select sysdate+rownum eval from all_objects where rownum <= 10) where to_char(eval,'dd-mm-yyyy') = your logic

                    • 7. Re: sql query
                      Etbin

                      Maybe

                       

                      select eval

                        from (select sysdate + rownum eval

                                from all_objects

                                where rownum <= 10

                             )

                      where instr(:parameter,to_char(eval,'dd-mm-yyyy')) > 0

                          or :parameter is null

                       

                      Regards

                       

                      Etbin

                      • 8. Re: sql query
                        a_cute_person

                        Etbin wrote:

                         

                        Maybe

                         

                        select eval

                          from (select sysdate + rownum eval

                                  from all_objects

                                  where rownum <= 10

                               )

                        where instr(:parameter,to_char(eval,'dd-mm-yyyy')) > 0

                            or :parameter is null

                         

                        Regards

                         

                        Etbin

                         

                        when i apply the same logic as below and pass 100 as parameter i am getting 3 records which is wrong

                         

                        select eval

                          from (select rownum eval

                                  from all_objects

                                  where rownum <= 1000

                               )

                        where instr(:parameter,eval) > 0

                            or :parameter is null

                         

                        and thank you, your reply was almost there

                        • 9. Re: sql query
                          Etbin

                          You must decide what you want in the first place.

                          In your original post it seemed you wanted to use strings (Solomon's suggestion was correct)

                          then you mentioned dates (sysdate + rownum)

                          now you're dealing with numbers ...

                           

                          select eval

                            from (select rownum eval

                                    from all_objects

                                    where rownum <= 1000

                                 )

                          where instr(:parameter,eval) > 0

                              or :parameter is null

                           

                          EVAL
                          1
                          10
                          100

                           

                          The above is not wrong, maybe it's not what you want, but it works as requested:

                          the inner query generates consecutive integers between 1 and 1000 that are fed to INSTR function (each number is implicitly converted to a string) and the numbers appearing in the result (their string value to be exact) are by all means substrings of the submitted parameter string '100'.

                           

                          select eval

                            from (select rownum eval

                                    from all_objects

                                    where rownum <= 1000

                                 )

                          where instr(','||:parameter||',',','||to_char(eval)||',') > 0

                              or :parameter is null

                           

                          EVAL
                          100

                           

                          both INSTR parameters are delimited by the delimiter to avoid the mother in chemotherapy case.

                           

                          Regards

                           

                          Etbin