Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
SQL Parsing error.. |
Mon, May 28 2018 6:34 PM | Permanent Link |
Ian Branch | Hi Team,
I have the following query imported form another DB. "select JobTickets.jobtype as [Job Type], count(JobTickets.MSN) as [Total Units], count(distinct JobTickets.MSN) as [Unique Units], count(JobTickets.msn)-count(distinct JobTickets.msn) as [Repeats] from JobTickets JobTickets where (trim(JobTickets.msn) <> '') and (JobTickets.Date_in >= :StartDate) and (JobTickets.date_in <= :EndDate) and (jobtickets.buscode = :buscode) group by JobType;" When I Prepare or Run the query I get an error.. "DBISAM Engine Error # 11949 SQL parsing error - Expected ) but instead found JobTickets in SELECT SQL statement at line 1, column 97" What is the correct way to make this query work in DBISAM please? Regards & TIA, Ian |
Tue, May 29 2018 3:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | First a plea: to all who submit queries with a question like this please, please format the sql so that each bit is on a separate line and then quote line & column numbers - its rather difficult counting along, especially as has happened on occasions line breaks have been inserted somewhere between posting and receipt.
It also make it easier for you to spot the problem Ian Its count(distinct JobTickets.MSN) DBISAM isn't happy with the distinct in there. In ElevateDB you'd use a subselect. Here I think your best option would be a script I'm not sure just what you're trying to achieve but something like SELECT COUNT(MSN) AS UNIQUE FROM JobTickets GROUP BY MSN INTO \Memory\uniques; select JobTickets.jobtype as [Job Type], count(JobTickets.MSN) as [Total Units], X.UNIQUE as [Unique Units], count(JobTickets.msn)-X.UNIQUE as [Repeats] from JobTickets JobTickets JOIN \Memory\Uniques X ON JobTickets.MSN = X.UNIQUE where (trim(JobTickets.msn) <> '') and (JobTickets.Date_in >= :StartDate) and (JobTickets.date_in <= :EndDate) and (jobtickets.buscode = :buscode) group by JobType Almost certainly wrong cos its a long time since I DBISAMed but it will give you a starting point Don't forget to dispose of Memory\uniques whn its no longer required Roy Lambert |
Tue, May 29 2018 5:00 AM | Permanent Link |
Ian Branch | Hi Roy,
Comments noted for the future in regard to formatting. The objective of the "count(distinct JobTickets.MSN)" is to count how many of each MSN type there were in the table. Thank you for the code. I have never done anything like that so I will have to study it. I am in fact moving it to EDB now so I will need to understand the subselect. Regards & Tks. Ian |
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 |