Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: IN operator help Urgent pls

    Hi friends,

    Need a help. I am trying to check whether recordset value matches to the result of query. It is showing error that 'sub query is not allowed here'

    ( Here the exact value matched are the operators '+' vs +,-,*,- . So if + matches any of the col values then it shd raise an exception. Please help


    IF p_rec1.p_input1_operator NOT IN (SELECT *
    FROM table1 b,
    table2 d , table3 e
    WHERE b.col1 = d.col1 AND d.col2 = e.col2
    and b.pname = 'xyz') THEN
    RAISE ;
    END IF;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "SELECT *" from 3 tables will return as many columns as there are in those 3 tables. Are you sure you want to compare P_REC1.P_INPUT1_OPERATOR to all of those columns? I suppose not. Therefore, you have to pick one which can be compared to it.

    Furthermore, as Oracle already told you, SELECT statement can't be used in this context. Workaround might look like this (I'm checking whether department number exists in Scott's DEPT table or not):
    Code:
    SQL> select deptno from dept;
    
        DEPTNO
    ----------
            10
            20
            30
            40
    
    SQL> declare
      2    l_dummy varchar2(1);
      3  begin
      4    select 'x'
      5      into l_dummy
      6      from dept
      7      where deptno = &par_deptno;
      8
      9    dbms_output.put_line('It exists');
     10  exception
     11    when no_data_found then
     12       dbms_output.put_line('It does not exist');
     13  end;
     14  /
    Enter value for par_deptno: 20
    It exists
    
    PL/SQL procedure successfully completed.
    
    SQL> /
    Enter value for par_deptno: 99
    It does not exist
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Try to adjust this example to your problem.

  3. #3
    Join Date
    Apr 2007
    Posts
    63
    Thank you for reply..I want to compare one value with n number of rows with same col. ( n may differ according to number of values ) and if it does not matches, I need to throw an exception

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is what I was talking about:
    Code:
    select 'x' 
      into l_dummy
      from table1 b,
           table2 d, 
           table3 e
      where b.col1 = d.col1 
        and d.col2 = e.col2
        and b.pname = 'xyz'
        --
        and <b/d/e.some_column> = p_rec1.p_input1_operator

  5. #5
    Join Date
    Apr 2007
    Posts
    63
    thank you. if we extend the above example to the reqt:

    Need to look for the existence of a small list of comma seperated values (all values should exist in second list ) from another list of comma seperated values. if all the values in first list exist in second list, then it is ideal situation. else - need to raise exception. Can any one suggest

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by prem18 View Post
    thank you. if we extend the above example to the reqt:

    Need to look for the existence of a small list of comma seperated values (all values should exist in second list ) from another list of comma seperated values. if all the values in first list exist in second list, then it is ideal situation. else - need to raise exception. Can any one suggest
    what does above have to do with Oracle?
    How would you do above in Java?

    >Can any one suggest
    I suggest that the inDUHvidual who insists on using comma separated listed be terminated immediately.
    I suggest that you learn both SQL & PL/SQL so you stop depending upon strangers to do your work for you for free.
    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.

  7. #7
    Join Date
    Apr 2007
    Posts
    63
    Thank you for your suggestion. The second one posted here is my own query out of my interest and not part of my work anywhere. I hope this forum is open for questions and postings are not done as a sudden. Please stop responding if anyone doesnt wish to reply.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >*** Prem ***
    >Oracle Certified Associate - SQL & PL/SQL

    above is a real world demonstration for the validity & usefulness for Oracle certificate
    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.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, you are doing this bass ackwards. Why would you select all the possible whatever column to then see if any match your variable. You should be stating in the SQL that you only want to fetch those where whatever column equals your variable. as an example of why you would do this. Your table has 100 rows, you are fetching 100 rows back to your program to see if they match your variable, why not query the table with equating a column to your host variable and you only fetch back n rows???

    Quote Originally Posted by prem18 View Post
    Hi friends,

    Need a help. I am trying to check whether recordset value matches to the result of query. It is showing error that 'sub query is not allowed here'

    ( Here the exact value matched are the operators '+' vs +,-,*,- . So if + matches any of the col values then it shd raise an exception. Please help


    IF p_rec1.p_input1_operator NOT IN (SELECT *
    FROM table1 b,
    table2 d , table3 e
    WHERE b.col1 = d.col1 AND d.col2 = e.col2
    and b.pname = 'xyz') THEN
    RAISE ;
    END IF;
    Dave

Posting Permissions

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