Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
Not getting expected records with query |
Wed, Mar 21 2012 4:41 PM | Permanent Link |
Robert Rowlands | Hi.
I have the following SQL in qryJobsLinks. I want to find the records in various tables that contain the value in the Param SearchKey. I then want records returned from tblJobs that are linked to these records. SELECT DISTINCT tblJobs.JobsAutoInc, tblJobs.ManagerName, tblJobs.FilePath, tblJobs.DateIn FROM tblJobs LEFT JOIN tblJobClient ON (tblJobs.JobsAutoInc = tblJobClient.MainAutoInc) LEFT JOIN tblJobArchitect ON (tblJobs.JobsAutoInc = tblJobArchitect.MainAutoInc) LEFT JOIN tblJobBCO ON (tblJobs.JobsAutoInc = tblJobBCO.MainAutoInc) LEFT JOIN tblJobCO ON (tblJobs.JobsAutoInc = tblJobCO.MainAutoInc) LEFT JOIN tblJobContacts ON (tblJobs.JobsAutoInc = tblJobContacts.MainAutoInc) LEFT JOIN tblJobContractor ON (tblJobs.JobsAutoInc = tblJobContractor.MainAutoInc) LEFT JOIN tblJobEngineer ON (tblJobs.JobsAutoInc = tblJobEngineer.MainAutoInc) LEFT JOIN tblJobHighways ON (tblJobs.JobsAutoInc = tblJobHighways.MainAutoInc) LEFT JOIN tblJobPlanner ON (tblJobs.JobsAutoInc = tblJobPlanner.MainAutoInc) LEFT JOIN tblJobQS ON (tblJobs.JobsAutoInc = tblJobQS.MainAutoInc) WHERE tblJobClient.GroupAutoInc =:SearchKey OR tblJobArchitect.GroupAutoInc =:SearchKey OR tblJobBCO.GroupAutoInc =:SearchKey OR tblJobCO.GroupAutoInc =:SearchKey OR tblJobContacts.ContactsAutoInc =:SearchKey OR tblJobContractor.GroupAutoInc =:SearchKey OR tblJobEngineer.GroupAutoInc =:SearchKey OR tblJobHighways.GroupAutoInc =:SearchKey OR tblJobPlanner.GroupAutoInc =:SearchKey OR tblJobQS.GroupAutoInc =:SearchKey I create the Param SearchKey and allocate the value to it. I have checked this value and it is as expected. Yet every time I get every record from tblJobs returned, which is not correct. If I change the end of the last 10 lines to ....... = 5 then I get the correct records returned. I'm creating the param SearchKey as follows. frmContactJobLink.qryJobsLinks.Params.Clear; frmContactJobLink.qryJobsLinks.Params.CreateParam(ftInteger, 'SearchKey'); frmContactJobLink.qryJobsLinks.ParamByName('SearchKey').AsInteger := SearchKey; frmContactJobLink.qryJobsLinks.Open; If I check the query in dbsys it always returns the correct records but of course I'm not using a param, I have to enter a value. Am I doing something wrong here? I guess I could create the SQL and allocate the value in code and it should then work. Thanks. |
Wed, Mar 21 2012 5:20 PM | Permanent Link |
Robert Rowlands | I think I've managed to sort this thanks.
It appears that the SQL creates 9 parameters all called SearchKey. I assumed the same one parameter would be used each time. If I go through the params from 0 to params.count -1 and allocate the same search value to all of them the query will then work. Thanks. |
Mon, Mar 26 2012 1:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Robert,
<< I'm creating the param SearchKey as follows. >> There's no need to do that. Just use this: frmContactJobLink.qryJobsLinks.Prepare; frmContactJobLink.qryJobsLinks.ParamByName('SearchKey').AsInteger := SearchKey; frmContactJobLink.qryJobsLinks.Open; -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |