Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    6

    Unanswered: need to change my query

    I have a query like this

    select * from table1
    where entitlement in (select sno from table2 where )----------1
    and category in (select sno from table2 where)----------2
    and clazz in (select sno from table2 where)----------3
    and subclass in (select sno from table2 where)----------4

    the inner select statements will return either a single row or no rows at all


    when it returns no rows i want to equade the outer where clause filter column to NULL.

    i.e example

    when the inner select return no row for the line marked -----1

    the query should be
    select * from table1
    where entitlement is null ---------------------------------------------------------------------------------------> i want some thing like this if the inner select doesnt return any rows
    and category in (select sno from table2 where)----------2
    and clazz in (select sno from table2 where)----------3
    and subclass in (select sno from table2 where)----------4


    i tried to use decode statement in side the inner select but when it returns null i have to use "is " clause in the outer where condition
    when it returns a value i cannot use "is" clause

    how to change the query

    regards
    raj

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    This problem statement is as clear as mud.
    Alternatively you could provide the following information.

    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2009
    Posts
    6
    sorry for being not clear ..

    what i want is that

    if the inner select query does not return any result then i should be able to use NULL as the value for the filter

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select * from table1
    where nvl(entitlement,'999') in (select '999' from dual union all select sno from table2 where )
    and nvl(category,'999') in (select '999' from dual union all select sno from table2 where)----------2
    and nvl(clazz,'999') in (select '999' from dual union all select sno from table2 where)----------3
    and nvl(subclass,'999') in (select '999' from dual union all select sno from table2 where)----------4
    or, this would probably be more understood by other developers. Avoid the use of distinct in the subqueries if it doesn't make sense in your data:

    Code:
    select table1.* 
    from table1,
          (select distinct sno from table2 where ...) ent,
          (select distinct sno from table2 where ...) cat,
          (select distinct sno from table2 where ...) clz,
          (select distinct sno from table2 where ...) subcls
    where table1.entitlement = ent.sno (+) and
          table1.category    = cat.sno (+) and
          table1.clazz       = clz.sno (+) and
          table1.subclass    = subcls.sno (+)
    --=Chuck
    Last edited by chuck_forbes; 04-14-09 at 19:48.

  5. #5
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    select table1.*
    from table1

    left Join table2 ON (
    table1.entitlement = ent.sno or table1.category = table2.sno or
    table1.clazz = table2.sno or table1.subclass = table2.sno )
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  6. #6
    Join Date
    Apr 2009
    Posts
    1

    Check this may work a bit faster

    select distinct(table1.* )
    from table1
    where table1.entitlement = ent.sno (+) and
    table1.category = cat.sno (+) and
    table1.clazz = clz.sno (+) and
    table1.subclass = subcls.sno (+)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •