Search code examples
javasqlhibernatehql

How to convert SQL nested query that deals with multiple tables to corresponding HQL?

I have a SQL query defined in Java as:

String queryString =
        "select payee_id from ("
            + "select distinct d.company_name, d.business_type, d.service_type,m.payee_id "
            + " from BOOKER.PC_PAYEE_BACKFILL_DATA d, BOOKER.PC_PAYEE_BACKFILL_METADATA m "
            + " where d.DATA_RECORD_ID=m.DATA_RECORD_ID "
            + " and d.status = '"
            + PayeeBackfillStatus.VM_ACCEPTED
            + "' and d.canceled = '"
            + AppConstants.FALSE_CHAR
            + "' and d.wave_num = "
            + waveNumber
            + " ) "
            + "group by payee_id having count(*) > 1 ";

I need to convert it to HQL. But HQL does not support inner queries. So what can be the alternative of doing it? One way is to run the inner query and then pull out payee_id using Java's collections. But that is a work around. You can assume any mapping class name for PC_PAYEE_BACKFILL_DATA and PC_PAYEE_BACKFILL_METADATA. Please help.

Solution

  • There is no way in HQL to do so.