Re: ORA-00936: missing expression

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Thu, 6 Sep 2001 20:17:00 +0400
Message-ID: <9n87ao$91s$1@babylon.agtel.net>

Can't be sure I'm completely right, but I think that 8.0.5 does not support direct use of subqueries as immediate scalar values in where clause. I'd suggest you to rewrite your query this way:

SELECT a.txtcode, b.txtcode FROM W76.TRANSLATION a, DUMP.TRANSLATIONTX b WHERE a.origtext = b.trantext AND b.langcode = 'FI'

    AND EXISTS(SELECT 1 FROM DUMP.TRANSLATIONTX

                       WHERE langcode = 'EN' AND txtcode = b.txtcode)

EXISTS() is in many cases the most effective way to sort out irrelevant rows, and it's the only way to get correct exclusion if checked dataset may contain NULLs (for example, NOT IN () will always return false if dataset in brackets contain at least one NULL value, while NOT EXISTS() will return correct results).

--

Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com

Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer.

"Alexander Shuvalov" <saturn_at_hot.ee> wrote in message news:c6f073f6.0109060628.637ac967_at_posting.google.com...
> Hi, all!!!
>
> Why do I get the following error: ORA-00936: missing expression, when
> exectuing this command on Oracle 8.0.5 server? The command is: SELECT
> a.txtcode, b.txtcode FROM W76.TRANSLATION a, DUMP.TRANSLATIONTX b WHERE
> a.origtext = b.trantext AND b.langcode = 'FI' AND (SELECT COUNT(*) FROM
> DUMP.TRANSLATIONTX WHERE langcode = 'EN' AND txtcode = b.txtcode)>0. But
> the same command works fine on ORacle 8.1.6 server!!!
>
> What is the problem here?
>
> TIA
Received on Thu Sep 06 2001 - 11:17:00 CDT