subquery

[hilbrink]

when trying to execute the query

SELECT *
FROM A, B
WHERE A.id = B.idA
AND (SELECT count(*) FROM A WHERE A.id=B.idA) < 2

i get the message ORA-00936: missing expression. And the point where the exception is given is the SELECT statement in the subquery.

Does anybody know why i get this message? In the syntax description of the SELECT statement it says i can use a subquery...

 

[MikeJones]

Try...

SELECT *
FROM A, B
WHERE A.id = B.idA
AND (SELECT count(*) FROM A2 WHERE A2.id=B.idA) < 2

I think the duplicate aliases are confussing it all.

 

[hilbrink]

nope, that's not it..

 

[MikeJones]

OK Try having your subquery on the right hand side of the equals...

 

[Dmytro]

select * from a,b
where a.id=b.id and
exists(select id from a where a.id=b.id group by id having count(*)<2)

 

[MikeJones]

OK Try having your subquery on the right hand side of the equals...

Sorry not the equals the less than sign, although you will need to change your logic slightly...

 

[hilbrink]

yes it works!
thank you mike and dmytro