HQL Query exception while using SELECT clause in Oracle CCB Utilities framework

Question

I am getting an issue in hql (Oracle CC&B Utilities Framework)

My SQL query is

select msg.EXT_MSG_ID, msg.MKTMSG_ID
from  O2_IN_CA_MSG msg
where EXT_MSG_ID = (select to_char(max(to_number(msg1.EXT_MSG_ID)))
                          from O2_IN_CA_MSG msg1, F1_BUS_OBJ_STATUS bostatus 
                          where msg1.BUS_OBJ_CD = bostatus.BUS_OBJ_CD 
                                AND bostatus.BO_STATUS_COND_FLG <> 'F1FL' 
                                AND msg1.BO_STATUS_CD = bostatus.BO_STATUS_CD 
                                AND msg1.BUS_OBJ_CD = 'O2-OESPCusTarIn'
                                AND msg1.SRC_DOC_ID = '12345678' 
                                AND msg1.MKTMSG_ID <> '179977027857'
                                AND msg1.acct_id = '7565333022'
                  )

HQL Query1:

FROM InboundCustomerAdminMessage inboundCustomerAdminMessage
WHERE inboundCustomerAdminMessage.externalMessageId = 
    (TO_CHAR(MAX(TO_NUMBER(inboundCustomerAdminMessage1.externalMessageId))) 
    FROM InboundCustomerAdminMessage inboundCustomerAdminMessage1, BusinessObjectStatus businessObjectStatus 
    WHERE inboundCustomerAdminMessage1.businessObject.id = businessObjectStatus.id.businessObject.id 
    AND businessObjectStatus.condition <> :boStatusCond 
    AND inboundCustomerAdminMessage1.status = businessObjectStatus.id.status 
    AND businessObjectStatus.id.businessObject.id = :businessObjectId 
    AND inboundCustomerAdminMessage1.sourceDocumentId = :srcDocId 
    AND inboundCustomerAdminMessage1.id <> :mktMsgId )

The exception is: ORA-00934: group function is not allowed here

I tried second query by using SELECT HQL Query 2:

FROM InboundCustomerAdminMessage inboundCustomerAdminMessage
WHERE inboundCustomerAdminMessage.externalMessageId = 
    (SELECT TO_CHAR(MAX(TO_NUMBER(inboundCustomerAdminMessage1.externalMessageId))) 
    FROM InboundCustomerAdminMessage inboundCustomerAdminMessage1, BusinessObjectStatus businessObjectStatus 
    WHERE inboundCustomerAdminMessage1.businessObject.id = businessObjectStatus.id.businessObject.id 
    AND businessObjectStatus.condition <> :boStatusCond 
    AND inboundCustomerAdminMessage1.status = businessObjectStatus.id.status 
    AND businessObjectStatus.id.businessObject.id = :businessObjectId 
    AND inboundCustomerAdminMessage1.sourceDocumentId = :srcDocId 
    AND inboundCustomerAdminMessage1.id <> :mktMsgId )

This time exceptio is: Error creating hibernate query 'FROM InboundCustomerAdminMessage inboundCustomerAdminMessage WHERE inboundCustomerAdminMessage.externalMessageId = all (select TO_CHAR(MAX(TO_NUMBER(inboundCustomerAdminMessage1.externalMessageId))) FROM InboundCustomerAdminMessage inboundCustomerAdminMessage1, BusinessObjectStatus businessObjectStatus WHERE inboundCustomerAdminMessage1.businessObject.id = businessObjectStatus.id.businessObject.id AND businessObjectStatus.condition <> :boStatusCond AND inboundCustomerAdminMessage1.status = businessObjectStatus.id.status AND businessObjectStatus.id.businessObject.id = :businessObjectId AND inboundCustomerAdminMessage1.sourceDocumentId = :srcDocId AND inboundCustomerAdminMessage1.id <> :mktMsgId ) '

org.hibernate.QueryException: ( expected before ) in select [select inboundCustomerAdminMessage.externalMessageId FROM com.splwg.domain.mtm.inbound.inboundCustomerAdminMessage.InboundCustomerAdminMessage__ inboundCustomerAdminMessage WHERE inboundCustomerAdminMessage.externalMessageId = all (select TO_CHAR(MAX(TO_NUMBER(inboundCustomerAdminMessage1.externalMessageId))) FROM com.splwg.domain.mtm.inbound.inboundCustomerAdminMessage.InboundCustomerAdminMessage__ inboundCustomerAdminMessage1, com.splwg.domain.common.businessObject.BusinessObjectStatus__ businessObjectStatus WHERE inboundCustomerAdminMessage1.businessObject.id = businessObjectStatus.id.businessObject.id AND businessObjectStatus.condition <> :boStatusCond AND inboundCustomerAdminMessage1.status = businessObjectStatus.id.status AND businessObjectStatus.id.businessObject.id = :businessObjectId AND inboundCustomerAdminMessage1.sourceDocumentId = :srcDocId AND inboundCustomerAdminMessage1.id <> :mktMsgId ) order by inboundCustomerAdminMessage.externalMessageId desc]

Can you please help anyone on how to use SELECT clause in hql.

RakeshS RakeshS · Accepted Answer · 2016-05-05T11:53:44

0

votes

Remove "order by inboundCustomerAdminMessage.externalMessageId desc" in your HQL

HQL Query exception while using SELECT clause in Oracle CCB Utilities framework

1 Answers