unexpected AST node: query

Question

I have two tables one of them is ESERVICE and another is WORKFLOW, eservice have a workflow id as a foreign key. Not all eservices will have workflow so I want to fetch workflow name when exist otherwise an empty or null value fine for me.

so I wrote sql as below which is working fine for me. (tested with oracle)

SELECT e.EE_ESERVICE_ID,
       CASE
          WHEN EXISTS
                  (SELECT 1
                     FROM WORKFLOWS w
                    WHERE w.WORKFLOW_ID = e.WORKFLOW_ID)
          THEN
             (SELECT i.I18ND_TRANSLATION
                FROM WORKFLOWS w, I18N_DICTIONARY i
               WHERE     WORKFLOW_ID = e.WORKFLOW_ID
                     AND i.I18N_ID = w.WORKFLOW_NAME
                     AND i.I18N_LOCALE_ID = 1)
          ELSE
             COALESCE ('', '')
       END
          AS WORKFLOW_NAME
  FROM ESERVICES e

Now I need to convert it into hql so I wrote as

StringBuffer hql = new StringBuffer("select e.eserviceId as eserviceId,"
                + " (case when exists(select 1 from Workflow w where w.workflowId = e.workflowId)"
                + " then (select i.i18ndTranslation from Workflow w,I18nDictionary i where w.workflowId = e.workflowId and i.i18nId = w.workflowName and i.i18nLocaleId = :localeId)"
                + " else coalesce('', '') end) as workflowName"
                + " from Eservice as e");

But when I am using this then I am getting org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node

I know we can achieve it directly from left join but due to some limitation we are using cross join, so I need go this way...

Please share your thoughts Suggestions are most welcome

Update 1 We are not using association that's why we are strict to cross join.

Eservice.java

    @Entity
    @Table(name = "ESERVICES")
    public class Eservice implements java.io.Serializable {

        private static final long serialVersionUID = -4088905661029802626L;

        public Eservice() {}

        @Id
        @SequenceGenerator(name = "escSequence", sequenceName = "SQ_ESERVICES",allocationSize=1)
        @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "escSequence")
        @Column(name = "EE_ESERVICE_ID", unique = true, nullable = false)
        private BigDecimal eserviceId;

        @Column(name = "WORKFLOW_ID")
        private BigDecimal workflowId;

    @Transient
    private String workflowName;

        // setter and getter
    }

Workflow.java

@Entity
@Table(name = "WORKFLOWS")
public class Workflow implements java.io.Serializable { 

    private static final long serialVersionUID = -4863237070153860617L;

    public Workflow() {}

    @Id
    @SequenceGenerator(name = "wfSequence", sequenceName="SQ_WORKFLOWS", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE , generator = "wfSequence")
    @Column(name = "WORKFLOW_ID", unique = true, nullable = false)
    private BigDecimal workflowId;

    @Column(name = "I18N_WORKFLOW_NAME")
    private BigDecimal workflowName;

    // setter and getter
}

I18nDictionary.java

@Entity
@Table(name="I18N_DICTIONARY")
@Cache(usage=CacheConcurrencyStrategy.READ_WRITE)
public class I18nDictionary implements Serializable{

    /**
     * The serialization runtime associates with each serializable class a version number called serialVersionUID.
     */
    private static final long serialVersionUID = -2587075034303056842L;

    @Id
    @SequenceGenerator(name = "seqDictGenerator", sequenceName = "SQ_I18N_DICTIONARY",allocationSize=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seqDictGenerator")
    @Column(name="I18N_DICT_ID", unique = true, nullable = false)
    private BigDecimal i18nDictId;

    @Column(name="I18N_ID")
    private BigDecimal i18nId;

    @Column(name="I18ND_TRANSLATION")
    private String i18ndTranslation;

    @Column(name="I18N_LOCALE_ID")
    private BigDecimal i18nLocaleId;

    // setter and getter    
}

EserviceHibDAO.java

Query query = getSession()
                    .createQuery(hql.toString().trim())
                    .setBigDecimal("localeId", i18nLocale.getI18nLocaleId())
                    .setResultTransformer(Transformers.aliasToBean(Eservice.class));

            List<Eservice> resultSet = query.list();

Answer 1

For me you can try this:

select e.eserviceId as eserviceId,
    case
        when e.workflowId is null then ''
        else
        (select i.i18ndTranslation from Workflow w,I18nDictionary i
        where w.workflowId = e.workflowId
        and i.i18nId = w.workflowName and i.i18nLocaleId = :localeId)
    end as workflowName
from Eservice as e

EDIT

Try this:

Your query will become:

String hql = 
    "select " + new Eservice.class.getName() + 
    "(e.eserviceId, " +
    "case " +
        " when e.workflowId is null then '' " +
        " else " +
        " (select i.i18ndTranslation from Workflow w,I18nDictionary i " +
        " where w.workflowId = e.workflowId " +
        " and i.i18nId = w.workflowName and i.i18nLocaleId = :localeId) " +
    "end)" +
" from Eservice as e "

In your Eservice class you must define an appropriate constructor as follow:

public Eservice(BigDecimal eserviceId, String workflowName) {
     this.eserviceId = eserviceId;
     this.workflowName = workflowName;
}

And finally, your retrieve code will become:

Query query = getSession().createQuery(hql.toString().trim())
                .setBigDecimal("localeId", i18nLocale.getI18nLocaleId());

        List<Eservice> resultSet = (List<Eservice>)query.list();