6 Replies Latest reply: Apr 8, 2013 1:50 PM by Frank Kulash RSS

    Need suggestions for best way to do this

    1001770
      I hava a table table A
      select column b from table A where column b = value 1 and column c = value 2
      I get value 1 from a select statement with where condition.
      I get value 2 from 1 more different select statement using where conditions.

      Kindly suggest me the best way to do this.
        • 1. Re: Need suggestions for best way to do this
          1001770
          I know nested queries but that will make it clumpsy...is dere any other better approach to do this
          • 2. Re: Need suggestions for best way to do this
            rp0428
            Welcome to the forum!

            Whenever you post provide your 4 digit Oracle version.
            >
            I hava a table table A
            select column b from table A where column b = value 1 and column c = value 2
            I get value 1 from a select statement with where condition.
            I get value 2 from 1 more different select statement using where conditions.
            >
            Then write a query that does just what you described above. Why is that an issue?
            • 3. Re: Need suggestions for best way to do this
              sybrand_b
              select column b from table A where column b = (select value from tableb where ...) and column c = (select ... from ....)

              You need to review the subquery concept

              general example

              select ename
              from emp
              where deptno =
              (select deptno from dept where location='DALLAS')

              If there are more you need to use IN instead of =

              ------------------
              Sybrand Bakker
              Senior Oracle DBA
              • 4. Re: Need suggestions for best way to do this
                1001770
                yeah...I will do nested or subquery......just verifying for better approaches.....when should we prefer stored procedures???
                • 5. Re: Need suggestions for best way to do this
                  rp0428
                  When you can't use SQL to get the job done.
                  • 6. Re: Need suggestions for best way to do this
                    Frank Kulash
                    Hi,
                    998767 wrote:
                    yeah...I will do nested or subquery......just verifying for better approaches.....
                    Depending on your requirements, you could use WITH clauses instead of nested sub-queries.
                    when should we prefer stored procedures???
                    Mainly when something can't be done in pure SQL, or when the only way to do it in pure SQL is very convoluted and/or inefficient.
                    Of course, it is debatable what "very convoluted" is. A 6-line sub-query might not sound complicated, but it certainly is more complicated than a 1-line function call. If you find that a user-defined function makes your code easier to debug and maintain, then go ahead and use one, assuming that performance is acceptable. There is a certain overhead cost involved in calling any user-defined function from SQL, no matter how efficient the function itself is. There is often a trade-off; you'll have to decide if the neat, clear code that you get from a user-defined function justifies slower performance.