topic Re: How to use SQL function in joins in QlikView

How to use SQL function in joins

rahulken — Wed, 25 Nov 2020 16:16:04 GMT

Currently facing problem while using sql function in joins. It is giving mw the error as "Invalid object name dbo.<functionname>

I have following query as

Select E.EmpId,

D.DeptName

from Employees E cross join dbo.GetEmployeeDetails(E.EmpId)

Re: How to use SQL function in joins

rupamjyotidas — Fri, 04 Nov 2016 17:16:46 GMT

Maybe this

Select E.EmpId,

D.DeptName

from Employees E cross join

Select your fieldname

From dbo.GetEmployeeDetails(E.EmpId)

But are you sure you want to do cross join. I fear that join.

Re: How to use SQL function in joins

Fri, 04 Nov 2016 23:37:00 GMT

Use apply instead of join when joining table valued functions.

Select E.EmpId,

D.DeptName

from Employees E cross apply dbo.GetEmployeeDetails(E.EmpId)

Re: How to use SQL function in joins

Anil_Babu_Samineni — Sat, 05 Nov 2016 01:04:46 GMT

Does GetEmployeeDetails is a table name or what.

Would you describe more means share the screen which you get the error.

Re: How to use SQL function in joins

jayanttibhe — Sat, 05 Nov 2016 03:23:04 GMT

Hi,

Maybe you want something like this :

Select E.EmpId,

D.DeptName

from Employees E

cross join

dbo.GetEmployeeDetails D

Select E.EmpId,

D.DeptName

from Employees E ,

dbo.GetEmployeeDetails D

I dint get why you want to specify dbo? normally if we want to specify specific table in specific Data base then we use this syntax : dbo.DB_Name.Table_Name

Thanks

Jayant

Re: How to use SQL function in joins

Gysbert_Wassenaar — Sun, 06 Nov 2016 15:28:29 GMT

Try asking this question on a SQL Server support site. It has nothing to do with Qlikview. Once you have figured out what the correct sql statement is you can use it in Qlikview as it is. Qlikview only passes the sql statement to the oledb or odbc driver and receives the results from the driver.

Re: How to use SQL function in joins

rahulken — Wed, 16 Nov 2016 04:37:03 GMT

I am still facing the problem (I tried all the flows mentioned here). Please find the screen shot of the error.

Re: How to use SQL function in joins

rahulken — Wed, 16 Nov 2016 04:37:58 GMT

Here I tried apply also still faced this error.

Re: How to use SQL function in joins

jonathandienst — Wed, 16 Nov 2016 04:42:05 GMT

You did try a SQL support site as Gysbert suggested right?

Re: How to use SQL function in joins

rahulken — Wed, 16 Nov 2016 04:43:36 GMT

I tried it on SQL and it works in query analyser.

Re: How to use SQL function in joins

rahulken — Wed, 16 Nov 2016 04:47:32 GMT

Query properly works in SQL.

Re: How to use SQL function in joins

jonathandienst — Wed, 16 Nov 2016 04:54:44 GMT

The error that you are seeing is a SQL error. The SQL statement is passed as written to SQL and an error occurred in the ODBC driver or in SQL itself. Nothing was done in QV as no data was returned from the database. Did you check out the ODBC error message on the SQL web site?

Re: How to use SQL function in joins

Anil_Babu_Samineni — Wed, 16 Nov 2016 06:23:44 GMT

As you said, What you have check with SQL Team. Have you asked this "dbo". If so, Then Try to share full script

Re: How to use SQL function in joins

rahulken — Wed, 16 Nov 2016 06:37:40 GMT

I have not yet check with SQL but the script works properly in SQL. I will check with SQL Team regarding ODBC.

Re: How to use SQL function in joins

Thu, 17 Nov 2016 01:34:09 GMT

Did you run T-SQL on same MSSQL server where QlikView document is connecting to?

To me table function dbo.GetEmployeeDetails doesn't exists on MSSQL server where Qlikview document is connecting to from shown error message or account used by Qlikview document doesn't have permission to table function.

Re: How to use SQL function in joins

Anil_Babu_Samineni — Thu, 17 Nov 2016 05:24:28 GMT

Where this table GetEmployeeDetails Situated in. Can you add few points and try to share script with your child table and cartezion table.

Re: How to use SQL function in joins

Thu, 17 Nov 2016 06:25:52 GMT

Can you check in database the table is there or not, i too faced this in many times while extracting the data, my dba guys drop those tables in dev environment. may be check via sql first then try

Re: How to use SQL function in joins

rahulken — Thu, 17 Nov 2016 06:28:49 GMT

The function and tables are present. I executed the query in sql server and got the results also.

Re: How to use SQL function in joins

rahulken — Thu, 17 Nov 2016 06:30:01 GMT

Sorry! First I placed "GetEmplyeeDetais" as placeholder function name. But the actual is given in the screen shot.