Hold on to something..this is pretty ugly.

from
ExternalIdEquipMap e
select e.externalId, e.accountNo
from (select replace(e.externalId,,' ', '') e.externalId, e.accountNo
from (select translate(e.externalId,':-CcSsDd',' ') e.externalId, e.accountNo
where e.externalIdType = 2
and e.inactiveDate is null
and e.accountNo = :accoutNo)
where e.externalId like ('800%')
OR e.externalId like ('866%')
OR e.externalId like ('877%')
OR e.externalId like ('888%')
)
where length(e.externalId) = 20)

My dialect is Oracle
The SQL run fine in oracle, but I have been trying for a week now to re-structure it for use with a model object. I know its a syntax issue, but I have run out of ways to re-structure. The code above used to be in a find() but no success, then I tried the createSQL() and also the addCriteria. The above code is now in a query.xml as a named query. Any help would be greatly appreciated.

BTW-
I know I am doing alot of cleanup using this SQL, it sorts begs the question as to why we dont just clean up the database. From your lips, to Gods ears to managements brain is all I can say. Its the best I have to work with right now.

Here is my error:
net.sf.hibernate.QueryException: in expected: select [ from com.uslec.isg.domain.model.account.ExternalIdEquipMap e
select e.externalId, e.accountNo
from (select replace(e.externalId,,' ', '') e.externalId, e.accountNo
from (select translate(e.externalId,':-CcSsDd',' ') e.externalId, e.accountNo
where e.externalIdType = 2
and e.inactiveDate is null
and e.accountNo = 108554)
where e.externalId like ('800%')
OR e.externalId like ('866%')
OR e.externalId like ('877%')
OR e.externalId like ('888%')
)
where length(e.externalId) = 20)
]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:120)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:146)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:133)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:352)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:330)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1368)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1332)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1322)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1314)
at net.sf.hibernator.HibernateConnection.query(HibernateConnection.java:188)
at net.sf.hibernator.views.QueryView$QueryTask.run(QueryView.java:255)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:101)

Hum, can't even understand your request. Can't blame Hibernate ;-)

you do 'from blah select blah', what is the purpose of that, this is not allowed

have a look at 10.11. Subqueries.

I did say: "I know its a syntax issue, but I have run out of ways to re-structure."

Hmm..thanks for you input..

You have broken shema "key like ('800%') " It breaks the first normal form and it is not a good thing (it is killer for performance too).
I think "CREATE VIEW" can help. It must be better to "hide" this kind of queries in View any way, with or without hibernate.