SQL Problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: SQL Problem

  1. #1
    Join Date
    Nov 1999
    Posts
    226
    Hi

    Inc ase i want to check out teh presence of two columns in a table how do i do that .

    select (ordernum,date ) from table_name A where (order_num,date) in (Select (order_num,date) from table_name B.

    Gives me an error
    and

    if I write

    select (ordernum,date ) from table_name A where exists (Select (ordernum,date ) from table_name B) gives me all orders and treats order_num and date as seperate columsn .

    Please help

    regards

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    To do it interactively from SQL*PLUS:

    desc my_table;


    To do it in code:

    set serveroutput on

    declare x number;
    begin
    select count(*) into x from dba_tab_columns where table_name = 'My_Table_Name' and (column_name = 'ORDER_NUM' or column_name='DATE');
    dbms_output.put_line(x);
    end;
    /


    If the number output by X is the same as the number of columns you listed in the where clause above, they all exist. If X is less than the number of columns you listed, some of them don't exist.

    Note: I used dbms_output.put_line solely to display the output for your use in SQL*PLUS. You could use this code in a procedure and test X with code rather than displaying it. Use capitals when searching the data dictionary for objects by name.

    Is this what you wanted?


  3. #3
    Join Date
    Nov 1999
    Posts
    226
    Thanks for your reply

    What I actually wanted was finding out the select using IN or EXIST of two columns together .

    eg. Select (a,b) from table where a,b in (select a,b from table B .

    What is the syntax of this

    regards



  4. #4
    Join Date
    Mar 2000
    Location
    india
    Posts
    54
    Hi Kmesser,
    There is a possibility it will give the wrong output.
    1.You have to mention the owner,as dba_tab_columns holds for all the users, if the same table exists in two users you will get the output different than what you expect

  5. #5
    Join Date
    Aug 2000
    Posts
    462
    raghucharan:

    You're exactly correct. Thanks.

    I appears as though the solution will be dynamic SQL, given puneet's most recent response . . .

  6. #6
    Join Date
    Nov 1999
    Posts
    226
    Guys

    The table name is not same but I want an IN clause or and EXIST for checking out two columns simultaneously together.

    Select a,b from table A where a,b in (select a,b from table b)

    regards


  7. #7
    Join Date
    Aug 2000
    Posts
    462
    puneet:

    Rather than describe how you want to do it, please provide more detail of the functionality you seek. Do you, for example want code that will:

    1. Look up data values in one table based upon those values existing in another table?

    2. Check the data dictionary to determine if columns exist in a table, and select the values in those columns if they do exist?

    3. Something else?

    Please elaborate.

    Thanks!

  8. #8
    Join Date
    Feb 2001
    Posts
    295
    [QUOTE][i]Originally posted by puneet [/i]
    [B]Guys

    The table name is not same but I want an IN clause or and EXIST for checking out two columns simultaneously together.

    Select a,b from table A where a,b in (select a,b from table b)

    regards

    [/B][/QUOTE]


    puneet, it's not very clear. Do you want to select columns a & b from table A when the values are equal in both tables? If that's the case, try to separate IN statements by a logical condition:

    select a,b
    from A
    where a in (select a from B)
    and b in (select b from B);


    From the performance point, it couldn't be the best way to treat the question (if depends on your table's nature).


    Adriano.


  9. #9
    Join Date
    Nov 1999
    Posts
    226

    Thanks

    Thanks guys

    for the help . Got it finally

Posting Permissions

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



Click Here to Expand Forum to Full Width