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

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

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