Re: Theoretical question... very simple but complex problem deali ng with negation.

[Paige Bacile <pbacile@SELU.EDU>]

You might try an outer join with myTable(+) and temporary input table  that
would contain your input data

If the value is in both tables it will be returned,
otherwise a blank will be returned

select distinct j.keyvalue
from mytable j,input_temp_table p
where j.keyvalue(+) = p.keyvalue
and p.keyvalue = 7

if keyvalue = 7
sql returns 1 row with "blank" and you know a 7 did not exist in MYTABLE

if keyvalue = 2
sql returns 1 row with 2 and you know a 2 did exist in MYTABLE







At 03:22 PM 9/27/02 -0400, you wrote:
>select
>coalesce(mykey,x)
>from
>mytable
>where mykey = $input
>
>would return 'x' when it does not find a match. but i am not sure how you
>can get "no records" when there is a match
>
>-----Original Message-----
>From: Ken Boettger [mailto:BoettgeK@CWU.EDU]
>Sent: 09/27/2002 2:51 PM
>To: SQR-USERS@list.iex.net
>Subject: Theoretical question... very simple but complex problem dealing
>with negation.
>
>
>I have a table entitled MyTable. It has only one field, MyKey.
>
>There are only three records in the table with MyKey values of 1, 2 and 3
>respectfully.
>
>Write a SQL statement that RETURNS NO RECORDS when you successfully find a
>key value (aka, where MyKey = 2).
>
>Concurrently, the SQL should return one and only one record or data element
>(ie, select 'x') when the key value does not exist (aka, something should be
>returned when you test for MyKey = '7').
>
>
>I have a much more complex problem but this simple version sheds some light
>on the issue I am facing.  If someone knows how to address this, I would be
>interested in your solution.
>
>-Ken
>
>
>
>Ken Boettger
>boettgek@cwu.edu
>http://www.cwu.edu/~boettgek
>x2944

----------------------------------------------------------------------------------------------------------
Paige Bacile
Southeastern Louisiana University
Administrative Computing Services
Database Programmer
----------------------------------------------------------------------------------------------------------
Ph:985-549-3902          Fax:985-549-3470          Email:pbacile@selu.edu