This query runs fine in VFP Sql, but it does not work when I try to use it against Sql Server.
All I know is that I'm getting this error:
Incorrect syntax near '='. [1526:102]
I'm guessing the problem(s) lie in the Cast() and IIF() functions, and maybe other areas.
I'm sure I'm about to get my tail spanked with this project... I'm preparing for conversion of my app from dbf table to SqlServer back end. The basic Selects are easy to convert, but I have a few like this, which I can tell are going to cause me some pain.
Anybody know where the problems are?
The tables were upsized into Sql Server from VFP DBC using the ltest Sedna Upsizing Wizard. VFP Date fields were converted to DateTime, but I assume you can still use Max() and Min() on DateTime fields, so let's hope that will work fine.
Select Min(Workday) as MinDate,
Max(Workday) as MaxDate,
MergedTime.mach_num as Mach_Num,
Machines.mach_name as Mach_Name,
Cast(sum(hrs_worked) as Numeric(10,2)) as Hrs_Worked,
Max(ActiJobs.Mach_rate) as Mach_Rate,
Cast(sum(hrs_worked*actijobs.mach_rate) as Numeric(10,2)) as Total
From ActiJobs MergedTime
Join prempl02 employees on MergedTime.emp_num = employees.prempl
Join machines on MergedTime.mach_num = machines.mach_num
Where job_num='4033X'
Group by 3,4
Union
Select Min(Date),
Max(Date),
TimeClock.mach_num,
Machines.mach_name,
Cast(sum(Iif(TimeClock.hrs=0, 23.5667 - TimeClock.start, TimeClock.hrs)) as Numeric(10,2)),
Max(machines.Mach_rate),
Cast(sum(Iif(TimeClock.hrs=0, 23.5667 - TimeClock.start, TimeClock.hrs) * machines.mach_rate) as Numeric(10,2))
From Clock TimeClock
Join prempl02 employees on TimeClock.emp_num=employees.prempl
Join machines on TimeClock.mach_num=machines.mach_num
Where job_num='4033X' and polled=.f.
Group by 3,4
Order by 1,3
Here's the actual method that created this Sql :
LPARAMETERS tuParentKeyOrRef, tcMatchField, tcFieldList, tcOrderByFieldList, tcCursor, tlReadWrite, tlCloseCursor
Local loReturn, lcGroupByClause, lcSql, lcTimeClockAlias, lnCurrentTime
If Empty(tcOrderByFieldList)
tcOrderByFieldList = '1,3'
Endif
lcTimeClockAlias='Clock'
lcGroupByClause='Group by 3,4'
lnCurrentTime = CurrentTimeInDecimal()
this.PrepareSqlCommandParts(tuParentKeyOrRef, tcMatchField, tcFieldList, tcOrderByFieldList, tcCursor, tlReadWrite, tlCloseCursor)
Text to lcSql PRETEXT 15 TEXTMERGE NOSHOW
Select Min(Workday) as MinDate,
Max(Workday) as MaxDate,
MergedTime.mach_num as Mach_Num,
Machines.mach_name as Mach_Name,
Cast(sum(hrs_worked) as Numeric(10,2)) as Hrs_Worked,
Max(ActiJobs.Mach_rate) as Mach_Rate,
Cast(sum(hrs_worked*actijobs.mach_rate) as Numeric(10,2)) as Total
From <<this.cFromAlias>> MergedTime
Join prempl02 employees on MergedTime.emp_num = employees.prempl
Join machines on MergedTime.mach_num = machines.mach_num
Where <<this.cWhereClause>>
<<lcGroupByClause>>
<<this.cIntoCursor>> <<this.cReadWrite>>
Union
Select Min(Date),
Max(Date),
TimeClock.mach_num,
Machines.mach_name,
Cast(sum(Iif(TimeClock.hrs=0, <<lnCurrentTime>> - TimeClock.start, TimeClock.hrs)) as Numeric(10,2)),
Max(machines.Mach_rate),
Cast(sum(Iif(TimeClock.hrs=0, <<lnCurrentTime>> - TimeClock.start, TimeClock.hrs) * machines.mach_rate) as Numeric(10,2))
From <<lcTimeClockAlias>> TimeClock
Join prempl02 employees on TimeClock.emp_num=employees.prempl
Join machines on TimeClock.mach_num=machines.mach_num
Where <<this.cWhereClause>> and polled=.f.
<<lcGroupByClause>>
<<this.cOrderBy>>
EndText
loReturn = this.LoadLineItems(.f., .f., .f., .f., .f., .f., .f., lcSql)
Return loReturn