What is the equivalent of (+)<2 in ANSI SQL?

Question

I want to turn this

TableA.ColumnA(+)<2

into ANSI SQL.

I already tried:

(TableA.ColumnA<2 OR TableA.ColumnA IS NULL)

It missed one row. Despite the fact that its ColumnA is (null).

Edit (more context): Here is the query

SELECT * FROM a, c 
WHERE a.status(+)<2
AND a.rank(+)=1
AND c.id=a.id(+)

If that's a joining condition then you need to use a `LEFT`, `RIGHT` or `FULL OUTER JOIN` to make this condition work. An `INNER JOIN` will not help in this case. — Rachcha, Jan 31 '14 at 02:03
It's of course some type of condition, but there is no `TableB` to join to. I don't understand this condition at all. In my understanding there has to be a table next to the < instead of a number. — ekip, Jan 31 '14 at 02:46
You need to show more context, so we have a clue what you're asking about so we can suggest a replacement; without more context, this question is difficult to answer. It's like asking "why doesn't a=b?" with no additional information. You need to be more specific and provide more details. — Ken White, Jan 31 '14 at 02:47
Hi Ken. I've added the full query. I hope it's specific enough. — ekip, Jan 31 '14 at 03:09

score 2 · Accepted Answer · edited Jan 31 '14 at 05:47

2

give this a try

SELECT * FROM c LEFT JOIN a
ON c.id = a.id
AND a.status < 2
AND a.rank = 1

edited Jan 31 '14 at 05:47

Jeffrey Kemp

answered Jan 31 '14 at 02:07

M.Ali

There is no `TableB` in my case. Why would I join a Table to a number? Cause that's what `TableA.ColumnA(+)<2` do in my humble opinion. – ekip Jan 31 '14 at 02:22
1

ekip, the idea is to take the sample query and apply it to your situation. Obviously your schema doesn't literally have a `TableB`. I hope my edit helps you to see. – Jeffrey Kemp Jan 31 '14 at 05:48
Thanks M.Ali and Jeffrey! It turned out, that you are right. – ekip Feb 09 '14 at 00:56

1 Answers1