Not getting results in Hibernate native query using WITH Clause

Question

I have a following query which starts with "WITH" clause. And If I fire same query in SQuirrel which is having DB2 connection - It works. But same query returns blank array in Hibernate Native SQL.

 WITH edd_translated_article_tmp (article_id, tran_article_id, tran_article_title, 
 article_url, source, tran_content ) 
 AS (SELECT M.original_article_id  AS article_id, 
            M.article_id           AS TRAN_ARTICLE_ID, 
            M.article_title        AS TRAN_ARTICLE_TITLE, 
            M.article_url, 
            M.primary_source       AS SOURCE, 
            M.article_content_html AS TRAN_CONTENT 
     FROM   db2admin.edd_searched_article_main M 
     WHERE  M.original_article_id = 5239415) 
SELECT A.article_id, 
   A.article_title, 
   A.article_url, 
   A.primary_source       AS SOURCE, 
   A.article_content_html AS CONTENT, 
   M.tran_article_id      AS TRAN_ARTICLE_ID, 
   M.tran_article_title   AS TRAN_ARTICLE_TITLE, 
   M.tran_content         AS TRAN_CONTENT
FROM   db2admin.edd_searched_article_main AS A 
   LEFT OUTER JOIN edd_translated_article_tmp M 
                ON A.article_id = m.article_id
WHERE  A.article_id = 5239415

Hibernate Native Query Code :

Query query = getSession().createSQLQuery(sqlNativeQuery);
query.list()

The only explanation which comes to mind is that there is a problem with the DB2 dialect you are using. — Tim Biegeleisen, Feb 19 '18 at 14:14

score 1 · Answer 1 · answered Feb 19 '18 at 14:13

Your query should run as far as I know, assuming you are using the correct DB2 dialect with Hibernate. If, for example, you were using a really old dialect which did not support CTE, then it might not work.

For a workaround, you can always just inline the CTE into the raw query:

SELECT
    a.article_id, 
    a.article_title, 
    a.article_url, 
    a.primary_source       AS SOURCE, 
    a.article_content_html AS CONTENT, 
    m.tran_article_id      AS TRAN_ARTICLE_ID, 
    m.tran_article_title   AS TRAN_ARTICLE_TITLE, 
    m.tran_content         AS TRAN_CONTENT
FROM db2admin.edd_searched_article_main AS a
LEFT OUTER JOIN
(
    SELECT
        original_article_id  AS article_id,
        article_id           AS TRAN_ARTICLE_ID, 
        article_title        AS TRAN_ARTICLE_TITLE, 
        article_url, 
        primary_source       AS SOURCE, 
        article_content_html AS TRAN_CONTENT 
    FROM db2admin.edd_searched_article_main
    WHERE original_article_id = 5239415
) m
    ON a.article_id = m.article_id
WHERE
    a.article_id = 5239415 ;

You make silly mistakes when you have lot of things to focus on. I was connecting to QA DB instead of Dev - which resolved my issue. +1 for the work around ;) — Harsh Goswami, Feb 19 '18 at 17:45

Not getting results in Hibernate native query using WITH Clause

1 Answers1