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

Ian,

Why is JobTickets refred 2 times after "from" ?

--
Fernando Dias
[Team Elevate]

Fernando


>Why is JobTickets refred 2 times after "from" ?

He's setting the alias of JobTickes to JobTickets. I don't know why but maybe some systems require the alias rather than it being optional. I doubt it makes much if any difference either way.

Roy

Also, I've found another issue - the TRIM syntax is not correct: it should be TRIM(BOTH ' ' FROM JobTickets.msn)

--
Fernando Dias
[Team Elevate]

Fernando


>Also, I've found another issue - the TRIM syntax is not correct: it should be TRIM(BOTH ' ' FROM JobTickets.msn)

I ignored that one cos its got a good error message which Ian will see when he sorts out his other problem <VBG>

Roy

Fernando


As a bit of a chuckle - at least Ian doen't have to worry about null vs ''

Roy

Roy, Fernando,
Yes I saw that one and was aware of the change required.
Regards,
Ian

Ian,

The main issue is the DISTINCT that is not supported in COUNT.
To do what you want you will have to create a temporary table and drop
it after, like this:


DROP TABLE IF EXISTS "Memory\JobTickets_tmp" ;

SELECT
  JobType, Msn,
  COUNT(Msn) AS UnitsByMsn
INTO
  "Memory\JobTickets_tmp"
FROM
  JobTickets
WHERE
  (TRIM(BOTH ' ' FROM Mmsn) <> '') AND
  (JobTickets.Date_in >= :StartDate) AND
  (JobTickets.date_in <= :EndDate) AND
  (jobtickets.buscode = :buscode)
GROUP BY
  JobType, Msn ;

SELECT
  JobType AS [Job Type],
  SUM(UnitsByMsn) AS [Total Units],
  COUNT(UnitsByMsn) AS [Unique Units],
  SUM(UnitsByMsn) - COUNT(UnitsByMsn) AS Repeats
FROM
  "Memory\JobTickets_tmp"
GROUP BY
  JobType;


After closing the resulting Dataset, dont forget to drop the temporary
table "Memory\JobTickets_tmp"

--
Fernando Dias
[Team Elevate]