Hi Julie,

Your question is unclear. Do you want to translate SQL query into similar VFP code?

If yes, then ISNULL would translate into NVL() function in VFP.

>Having trouble converting this sql code to FoxCode. Sections 1, 2, 3 I am fine with. It's when I get to section 4 that the trouble comes into play. I get 'Command contains unrecognized phrase/keyword' and it highlights

>    sum(isnull(num, 0)) as Num
>    into hidesedan
>    from (select T1.*, T2.Num
>    from (select Halfhour
>    from @HalfHours_S) as T1
>

>from the query.
>
>Any help would be great.
>
>
>Section 1:
>SELECT r.*, v.vehicletypeid
>into routelfa
>from routearchive r join vehicle v on r.vehicleid = v.id
>
>Section 2:
>select *
>into sedanroutelfa
>from routelfa where vehicletypeid = '400'
>
>Section 3:
>declare @HalfHours_S table (HalfHour int)
>declare @intCounter_S int set @intCounter_s = 0
>while @intCounter_s < 66
>
>Section 4:
>begin insert into @HalfHours_S
>select @intCounter_S as HalfHour
>SET @intCounter_S = @intCounter_S + 1
>end declare @Shifts_S table (ShiftSt int, ShiftEnd int)
>insert into @Shifts_S
>select (cast(left(shiftstarttime,2) as int) * 60 + cast(right(shiftstarttime,2) as int))/30 as ShiftSt,
>(cast(left(shiftendtime,2) as int) * 60 + cast(right(shiftendtime,2) as int))/30 as ShiftEnd
>from sedanroutelfa
>where tripdate = '5/22/2007' and (status is null or status <> 'deleted')
>select right('0' + cast(HalfHour*30/60 as varchar), 2) + ':' + right('0' + cast(HalfHour*30 % 60 as varchar),2) + '-' + right('0' + cast((HalfHour*30+30)/60 as varchar), 2) + ':' + right('0' + cast((HalfHour*30+30) % 60 as varchar),2) as TimeSliceSedan,
>sum(isnull(num, 0)) as Num
>into hidesedan
>from (select T1.*, T2.Num
>from (select Halfhour
>from @HalfHours_S) as T1
>left join (select 1 as Num, ShiftSt, ShiftEnd
>from @shifts_S) as T2 on T1.Halfhour >= T2.ShiftSt and T1.HalfHour < T2.ShiftEnd) results
>group by HalfHour