Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
sagar_vij99
Creator
Creator

SQL Query not running with sub query

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

Labels (3)
1 Solution

Accepted Solutions
sagar_vij99
Creator
Creator
Author

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.

View solution in original post

3 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
sagar_vij99
Creator
Creator
Author

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.

Brett_Bleess
Employee
Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.