topic Re: SQL Query not running with sub query in QlikView

SQL Query not running with sub query

sagar_vij99 — Sat, 16 Nov 2024 01:23:18 GMT

Hi All,

I am fetching some data from server (view is created on server) in my .qvw file. When i simply pull data using select statement it gives me the data.

But i modified the query inorder to pull data faster but now the query is not getting executed. Table with column gets created but no data is fetched. Can somebody help me here..?

Below is the query i am using:

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ACVSBIRS;Data Source=Ewrdb1464;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=R01-E7C6D9DE81;Use Encryption for Data=False;Tag with column collation when possible=False];
Ccure_SQL:

SQL Select C.*, case when C.Att<=5 then '0-5 Days'
when C.Att>=6 and C.Att<=10 then '6-10 Days'
when C.Att>=11 and C.Att<=15 then '11-15 Days'
else '>15 Days'
end as Bracket

from

(select A.*,B.Att
from

(
SELECT cast(SiteLocalTime as date) as date1
,PrimaryObjectName
,[SecondaryPartitionName]
,[CardNo]
,[FacilityNo]
,[Personnel_Type]
,[Home_Location_]
,[LastName]
,[FirstName],
count(distinct [CardNo]) as D_Att,
month(cast(SiteLocalTime as date)) as Month_Field
FROM [ACVSBIRS].[dbo].[vQJournal]
where PrimaryObjectName = 'Vij, Sagar,' --and month(cast(SiteLocalTime as date)) = 11
group by
cast(SiteLocalTime as date)
,PrimaryObjectName
,[SecondaryPartitionName]
,[CardNo]
,[FacilityNo]
,[Personnel_Type]
,[Home_Location_]
,[LastName]
,[FirstName],
month(cast(SiteLocalTime as date))
)as A

left join

(select count(D_Att) as Att , [CardNo],Month_Field,PrimaryObjectName
from
(
SELECT cast(SiteLocalTime as date) as date1
,PrimaryObjectName
,[CardNo]
, count(distinct [CardNo]) as D_Att,
month(cast(SiteLocalTime as date)) as Month_Field
FROM [ACVSBIRS].[dbo].[vQJournal]
--where PrimaryObjectName = 'Vij, Sagar,'-- and month(cast(SiteLocalTime as date)) = 11
group by
cast(SiteLocalTime as date)
,[CardNo],
month(cast(SiteLocalTime as date)),
PrimaryObjectName
)x
group by
[CardNo],
Month_Field,
PrimaryObjectName
) as B

on A.[CardNo] = B.[CardNo] and A.Month_Field = B.Month_Field and A.PrimaryObjectName = B.PrimaryObjectName
) as C;

Exit Script

Re: SQL Query not running with sub query

Gysbert_Wassenaar — Thu, 30 Jan 2020 10:37:05 GMT

Qlikview does not execute the sql itself. It passes the entire statement to the oledb driver and that sends it to the database. If you don't get a result then first check that your query works at all. Try another sql client that let's you execute it directly on the database.

Re: SQL Query not running with sub query

sagar_vij99 — Fri, 31 Jan 2020 10:58:20 GMT

Hi Gysbert.

Thanks for the reply. Actually i am fetching data from a view but not directly from table as i don't have rights to access table directly.

The thing is when i run this query in sql server, it is giving me desired results but it is not giving results when executed through qlikview. Is there any way i can use this through qlikview or shall i ask the database owner to create another view using my query.

Re: SQL Query not running with sub query

Brett_Bleess — Mon, 24 Feb 2020 20:13:46 GMT

Sagar, I believe Gysbert was on the right track on this one, let me try to explain things another way here! The underlying issue is most likely the 'Connector' you are using to communicate with the View in the Database is likely not able to translate those additional SQL statements, that is the first place I would look on this one, you will likely need to hit the Connector Vendor's support site to try to find limitations etc. The other thing you could do is simply try to run that same query via say Microsoft Excel etc., as I would expect it is going to generate the same error, which if it does, you then have confirmation it is not on the QlikView/Excel side, it is most likely the Connector not liking the extra pieces in the Connect string or the SQL calls, and we are not going to be the ones to be able to help much if that is the case. One thing to check is the Connector version you are running is the most current version for the backend Database you are hitting, as sometimes that is the issue, there is a newer client that needs to be installed in order to handle those new calls etc. Hopefully this puts you on the right track such that you can get it resolved.

Regards,
Brett