SQL Problem

[puneet]

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

[kmesser]

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?

[puneet]

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

[raghucharan]

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

[kmesser]

raghucharan:

You're exactly correct. Thanks.

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

[puneet]

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

[kmesser]

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!

[adrianomp]

[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.

[puneet]

Thanks guys

for the help . Got it finally