0

I have got success from my previous query SQL server query returns but function does not,

USE [darshandb]
GO
DROP FUNCTION [dbo].[testfunction]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    CREATE FUNCTION [dbo].[testfunction] (@empId INT,@siteId INT)
     RETURNS TABLE
     WITH SCHEMABINDING
     AS
     RETURN
     (
      WITH treeResult(id) AS 
        (SELECT pt.id FROM myschema.art_artwork_tree AS pt WHERE pt.id in 
          (select node_id from myschema.art_brand_user_mapping where emp_id = $empId)
         UNION ALL 
         SELECT pa.id FROM treeResult AS p, myschema.art_artwork_tree AS pa 
         WHERE pa.parent_node = p.id and pa.site_id = $siteId) SELECT id FROM treeResult AS n
    );
GO

SELECT * FROM [dbo].[testfunction] (4,3);//show list of id

My goal is to call this user defined function in my JPQL query. For that I have experimented below steps :

1.select a from Address a where nodeId in dbo.testfunction(4,3); //not working but similar things works fine in Postgres

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.testfunction", or the name is ambiguous.

2.select a from Address a where nodeId in (select id from dbo.testfunction(4,3)); //not working

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: (

3.select a from Address a,dbo.testfunction(4,3) b where a.nodeId=b.id //not working

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: (

Reference link,

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Splitfn", or the name is ambiguous

Please suggest.

Darshan Patel
  • 2,839
  • 2
  • 25
  • 38
  • Since JPQL does NOT allow invocation of random functions then hardly a surprise. JPQL != SQL. Use JPA NATIVE queries for such things. –  Oct 30 '17 at 14:40
  • I have done same for Postgres successfully. – Darshan Patel Oct 31 '17 at 04:59
  • Post the code where you invoke the jpql –  Oct 31 '17 at 06:02
  • `em.createQuery("Above mentioned queries").getResultList();` – Darshan Patel Oct 31 '17 at 08:28
  • Kindly read my first comment on the subject then. That method is for inputting JPQL. You are not inputting JPQL, since JPQL has no such construct "dbo.testfunction ...". Any JPA docs would reveal this –  Oct 31 '17 at 08:41

0 Answers0