Category Archives: HQL/SQL

LISTAGG vs XMLAGG

Posted on by
Reply

LISTAGG gives “java.sql.SQLException: ORA-01489: result of string concatenation is too long” due to limitation for VARCHAR2.

select
  CONCAT(CONCAT(t2.REF_NUM_TYPE,':'), (listagg(t2.REF_NUM,',') within group (order by t2.REF_NUM asc))) as REF_NUMS_BK
from
(
	select
		max(s.PIPELINE_TX_ID) as PIPELINE_TX_ID,
		rn.ref_num_type,
		rn.ref_num
	from 
		reference_numbers rn, 
		ouk_shipment_activity_cmpny s 
	where 1=1
	and rn.pipeline_tx_id = s.pipeline_tx_id
	and s.PIPELINE_TX_ID = '123132123123'
	group by
		rn.ref_num_type,
		rn.ref_num
	order by
		rn.ref_num_type,
		rn.ref_num
) t2
where 1=1
group by
  t2.ref_num_type

Therefore, XMLELEMENT can be workaround.

select
  CONCAT(CONCAT(t2.REF_NUM_TYPE,':'),(RTRIM(XMLAGG(XMLELEMENT(e,t2.REF_NUM,',').EXTRACT('//text()')).GetClobVal(),','))) AS REF_NUMS
from
(
	select
		max(s.PIPELINE_TX_ID) as PIPELINE_TX_ID,
		rn.ref_num_type,
		rn.ref_num
	from 
		reference_numbers rn, 
		ouk_shipment_activity_cmpny s 
	where 1=1
	and rn.pipeline_tx_id = s.pipeline_tx_id
	and s.PIPELINE_TX_ID = '123132123123'
	group by
		rn.ref_num_type,
		rn.ref_num
	order by
		rn.ref_num_type,
		rn.ref_num
) t2
where 1=1
group by
  t2.ref_num_type

It can be trim as well.

select
 CONCAT(
  CONCAT(t2.REF_NUM_TYPE,':'),
   SUBSTR(
    (RTRIM(XMLAGG(XMLELEMENT(e,t2.REF_NUM,',').EXTRACT('//text()')).GetClobVal(),',')
   ),1,1000
  )
 ) AS REF_NUMS
from
(
	select
		max(s.PIPELINE_TX_ID) as PIPELINE_TX_ID,
		rn.ref_num_type,
		rn.ref_num
	from 
		reference_numbers rn, 
		ouk_shipment_activity_cmpny s 
	where 1=1
	and rn.pipeline_tx_id = s.pipeline_tx_id
	and s.PIPELINE_TX_ID = '30277174594' -- 202501494 202498201 30277174594(too long)
	group by
		rn.ref_num_type,
		rn.ref_num
	order by
		rn.ref_num_type,
		rn.ref_num
) t2
where 1=1
group by
  t2.ref_num_type

Example for XMLAGG with order by

with ref_base as (
    select 
     max(a.PIPELINE_TX_ID) as ppl
    ,a.REF_NUM_TYPE
    ,a.REF_NUM
    from REFERENCE_NUMBERS a where 1=1
    and a.PIPELINE_TX_ID = '30277174594'
    group by a.REF_NUM_TYPE, a.REF_NUM
    order by a.REF_NUM_TYPE, a.REF_NUM
)
select
(
    select RTRIM((XMLAGG(XMLELEMENT(e, b.REF_NUM,',') order by b.REF_NUM).EXTRACT('//text()')).GetClobVal(),',') AS REF_NUMS
    from   ref_base b where  b.REF_NUM_TYPE = 'ED1'
    group  by b.REF_NUM_TYPE
) as REF_NUMS_ED1
from dual

Example for LISTAGG with limited elements

select
  LISTAGG(
   case
      when rownum <= 300 
      then (
        case
           when rownum = 300
           then rn.ref_num || ' MORE....'
           else rn.ref_num
        end
      )
      else null
   end,
   ','
  ) within group (order by rn.ref_num)
  as ref_nums
from reference_numbers rn
where rn.pipeline_tx_id = '123456'

HQL and Native SQL in Hibernate

Posted on by
Reply

hiv-mvc-img
In Hibernate, there are number of ways to accessing a data: HQL, native SQL and others.
This section shows the implementation of HQL and native SQL.
These method are all defined in the DAO implementation classes.
hql-vs-sql-header-img1
The syntax of HQL is very much like SQL, but HQL is Hibernate object oriented.

SELECT * 
FROM RTRN_TP_RSN_CMBN  
WHERE rtrn_tp_cd ='01'
AND rtrn_rsn_cd ='001'

The above SQL is implemented as HQL as follow: (Source Code)

public RTRN_TP_RSN_CMBN getTpRsnCmbnByHql(String rtrnTpCd, String rtrnRsnCd) {

	String hqlStr = "from RTRN_TP_RSN_CMBN " 
			+ "where rtrn_tp_cd = :rtrnTpCd "
			+ "and   rtrn_rsn_cd = :rtrnRsnCd ";
	
	Query query = super.getCurrSession().createQuery(hqlStr);
	query.setParameter("rtrnTpCd", rtrnTpCd);
	query.setParameter("rtrnRsnCd", rtrnRsnCd);

	List<RTRN_TP_RSN_CMBN> list = query.list();
	if (list != null && list.size() > 0) {
		return (RTRN_TP_RSN_CMBN) list.get(0);
	}
	return null;
}

Native SQl: The result will be a list of Object Array.

SELECT 
 a1.rtrn_rsn_cd 
,a1.rtrn_rsn_nm 
,a1.sort_num 
,'' as rtrn_tp_cd 
FROM rtrn_rsn a1 
WHERE NOT EXISTS ( 
   SELECT '*' 
   FROM rtrn_rsn a2 
   JOIN rtrn_tp_rsn_cmbn cmb 
   ON a2.rtrn_rsn_cd = cmb.rtrn_rsn_cd 
   WHERE cmb.rtrn_tp_cd  = '02'
   AND a2.rtrn_rsn_cd = a1.rtrn_rsn_cd 
) 
ORDER BY a1.sort_num

The above SQL is implemented as follow: (Source Code)

public List<Object[]> getListRsnsUnBoundTpBySql(String tpCd) {
	
	StringBuilder sb = new StringBuilder();
	sb.append("\n");
	sb.append("SELECT \n");
	sb.append(" a1.rtrn_rsn_cd \n");
	sb.append(",a1.rtrn_rsn_nm \n");
	sb.append(",a1.sort_num \n");
	sb.append(",'' as rtrn_tp_cd \n");
	sb.append("FROM rtrn_rsn a1 \n");
	sb.append("WHERE NOT EXISTS ( \n");
	sb.append("   SELECT '*' \n");
	sb.append("   FROM rtrn_rsn a2 \n");
	sb.append("   JOIN rtrn_tp_rsn_cmbn cmb \n");
	sb.append("   ON a2.rtrn_rsn_cd = cmb.rtrn_rsn_cd \n");
	sb.append("   WHERE cmb.rtrn_tp_cd  = :tpCd \n");
	sb.append("   AND a2.rtrn_rsn_cd = a1.rtrn_rsn_cd \n");
	sb.append(") \n");
	sb.append("ORDER BY a1.sort_num");

	Query query = super.getCurrSession().createSQLQuery(sb.toString());
	query.setParameter("tpCd", tpCd);

	List<Object[]> list = query.list();
	return list;
}