Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
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