Thanks for the reply Mitesh. The SQL executes fine in Oracle client. One of the team members came with the reason that it might be because we are joining with another table (or same table in this case) and glassfish is getting confused in picking the columns. All other queries in the ejb-jar.xml don't have joins with other ejbs. This is the only one different from rest. So we had to write two different ejb-qls and merge the results in java code.
I know the reason sounds too absurd :-) . But I couldn't come up with a better reasoning. And yeah, deployed descriptor is same as the one shown.
Tx
Suneel
[Message sent by forum member 'suneelgv' (suneelgv)]
http://forums.java.net/jive/thread.jspa?messageID=293583