This discussion is archived
6 Replies Latest reply: Apr 8, 2013 11:50 AM by Frank Kulash RSS

Need suggestions for best way to do this

1001770 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    When you can't use SQL to get the job done.
  • 6. Re: Need suggestions for best way to do this
    Frank Kulash Guru
    Currently Being Moderated
    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.

Legend

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