multi field select using IN...

here is what i am wanting to do but is it possible to use multi fields with an IN statement?

SELECT *
FROM usmastf
WHERE usm_book, usm_acct IN
(SELECT usac_book, usac_acct
FROM uscommf
WHERE usac_code = 'O/W')i finally found it|||Not with an IN function. Use a standard JOIN instead:
SELECT usmastf.*
FROM usmastf
inner join uscommf
on usmastf.usm_book = uscommf.usac_book
and usmastf.usm_acct = uscommf.usac_acct
WHERE uscommf.usac_code = 'O/W'|||What does the pipe sign actually represent in the following

select * from usmastf
where ((usm_book | usm_acct) in (select usac_book | usac_acct from uscommf where usac_code = 'O/W'))|||The pipe character represents a boolean "OR" evaluation, but I don't think it is syntactically correct in your statement.|||in particular, the vertical bar means *bitwise* or, not logical or (same as in C/C++).

logical or is just the word "or" in sql (this would be || in C/C++).|||oh you silly microsoft peoples

hicpics, i think you're thinking of two vertical pipes, which is the sql standard for concatenation

... where usm_book||usm_acct in (select usac_book||usac_acct from ...|||oh you silly microsoft peoplesI can't imagine why people would assume that the post was talking about Microsoft SQL in this forum! ;)

You have a good point, and I thought of that myself, but we need to assume that the poster really is working with Microsoft SQL when they post a question here. If they are using a different SQL or SQL-like language then one of the moderators ought to move the post for them.

-PatP|||I too thought of concatenation, as I am unfortunately mired in the Oracle world at the moment. But the poster asked about the meaning of a single pipe, not double-pipes.|||actually the original poster never said "single pipe" -- and he was clearly using it as a concatenation operator in post #4

and being mired in oracle would only be an interesting coincidence, the double pipes are standard sql -- are you suggesting that oracle peoples have a better understanding of standard sql than microsoft peoples?

:)|||can one of you educate me? I have never heard of | being used as a concatenation operator. Do you mean string concatenation? Every reference in BOL I can find about | says it's bitwise or.

the | means bitwise or in most languages I am familiar with (except befunge, but I can't say I'm that familiar with it :))

and this don't work at all: select 'asdf' || 'qwerty'

:)|||Double-pipes concatenation is not allowed syntax for SQL Server.

You are trying to say "If A OR B is in C OR D", but this is a logical comparison and not a bitwise "OR" which is what the pipe character stands for. So your syntax will not work.

Did you try the code I posted?|||Who uses a single pipe as a conact operator?

MySQL?

I doubt it...

Oracle and DB2 are ||

I always thought + was a silly conact operator for chars|||I always thought + was a silly conact operator for chars

really? makes perfect sense to me.

||, on the other hand, makes no sense to me as concat. || has always meant logical or to me. But I was raised on C, not sql.

If you were implementing a string class in C++ and overloaded || to mean string concat, I'd have to, well, I won't say it. :)

EDIT: actually, you should never overload || for any reason, but that's a separate issue.|||really? makes perfect sense to me. oh?

what is the result of SELECT 2 + '2' then?

:)|||IMO, that *should* return a cast error. Only in nasty languages such as vb or javascript would I expect such things to be permitted.

it appears the ms (or maybe sybase) devs thought otherwise.

EDIT: SELECT 2 + '0.2' -- now there's a result I like (well, sort of)