Using EXISTS in DECODE - What's the syntax? 1

[RottPaws]

I'm trying to add a calculated yes/no field to a working query using the the EXISTS operator in a DECODE function.

I want to run a sub-query to see if the order exists in another table, and if it does, then I want to flag it as "Special".

I'm getting a message that says ORA-00936: missing expression.

My SQL looks something like this:
select order_id,
username,
complete_date,
decode(exists(select field1
from table2
where order_id=table1.order_id), true, 'Y', 'N') SPECIAL
from table1
where complete_date is not null

What am I doing wrong? _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[Mockenrue]

I think EXISTS may only be used as part of a WHERE clause, not as an expression.

See if this approach works:

select distinct t1.order_id,
t1.username,
t1.complete_date,
decode(t2.field1 , null , 'N' , 'Y') SPECIAL
from table1 t1,
table2 t2
where t1.complete_date is not null
and t2.order_id (+) = t1.order_id

 

[RottPaws]

Ya know......sometimes the answer is just too obvious . . .

Thanks. That worked. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!