CODEWITHSUNDEEP
javaspringpostgresqljpa
Pearl
Pearl

Reputation: 414

Unable to use ':' as delimeter in native query

Using the ":" as delimeter to split the timings from the date. Its works when we run a query in sql. When it comes with JPA, jpa considered this ":" delimeter as named parameter, how to overcome this issue.

Query:

select (case when SPLIT_PART(
                     SPLIT_PART(inq.suggest_time::text, ' ', 2),
                     ':', 1
                  )::int <= 10 and
                  SPLIT_PART(
                     SPLIT_PART(inq.suggest_time::text, ' ', 2),
                     ':', 2
                  )::int < 30
             then CONCAT(
                     (SPLIT_PART(
                         SPLIT_PART(inq.suggest_time::text, ' ', 1),
                         '-', 1
                     )::int)::text,
                     '-',
                     (SPLIT_PART(
                         SPLIT_PART(inq.suggest_time::text, ' ', 1),
                         '-', 2
                     )::int)::text,
                     '-',
                     (SPLIT_PART(
                         SPLIT_PART(inq.suggest_time::text, ' ', 1),
                         '-', 3
                     )::int + 1)::text)
             else (SPLIT_PART(inq.suggest_time::text, ' ', 1))::text
             end) as dateValue,
       count(inq.suggest_time )
from inquiry_table inq
where inq.assigned_to= 63
  and (inq.suggest_time is not null)
  and inq.inquiry_status IN (select ld.lookup_detail_id
                             from lookup_detail ld
                             where ld.lookup_detail IN ('New','In Process'))
group by dateValue
ORDER BY dateValue;

Error in app:

Caused by: org.springframework.web.util.NestedServletException: Request processing failed;
nested exception is org.hibernate.QueryException: Not all named parameters have been set: [:text, :int] [select (case when SPLIT_PART(SPLIT_PART(inq.suggest_time::text, ' ', 2), ':', 1)::int <= 10 and SPLIT_PART(SPLIT_PART(inq.suggest_time::text, ' ', 2), ':', 2)::int < 30 then CONCAT((SPLIT_PART(SPLIT_PART(inq.suggest_time::text, ' ', 1), '-', 1)::int)::text, '-',(SPLIT_PART(SPLIT_PART(inq.suggest_time::text, ' ', 1), '-', 2)::int)::text, '-',(SPLIT_PART(SPLIT_PART(inq.suggest_time::text, ' ', 1), '-', 3)::int + 1)::text) else (SPLIT_PART(inq.suggest_time::text, ' ', 1))::text end) as dateValue,count(inq.suggest_time )  from inquiry inq where inq.assigned_to= 63 and (inq.suggest_time is not null) and inq.inquiry_status IN (select ld.lookup_detail_id from lookup_detail ld where ld.lookup_detail IN ('New','In Process')) group by dateValue ORDER BY dateValue]

Upvotes: 1

Views: 288

Answers (1)

Rajib Biswas
Rajib Biswas

Reputation: 882

Escape the :: in ::text & ::int as shown below:

\\:\\:text
\\:\\:int

Refer: https://stackoverflow.com/a/29161044/6294804

Upvotes: 1

Related Questions