CODEWITHSUNDEEP
sqltranslationfoxprovisual-foxpro
sinkien
sinkien

Reputation: 299

Translate FoxPro's SQL code to SQL Server code

I'm trying to translate a bit of code from FoxPro to SQL Server and I'm facing very strange behavior.

This code written in FoxPro's application:

lcZnak1 = "HM,P6";

SELECT Zakslozkap.datum,ABS(Zakslozkap.hodnota) hodnota, ABS(Zakslozkap.koruny)
koruny, Zakslozka.sekce;
FROM Zakslozkap, Zakslozka; 
WHERE (Zakslozkap.SW $ lcZnak1) AND;
BETWEEN(Zakslozkap.datum,Thisform.cDatOd,Thisform.cDatDo) AND ;
Zakslozkap.ide_slozka = Zakslozka.ide_slozka AND ;
Zakslozka.ide_zak = "6065" ;
INTO CURSOR QueryNakladMZD

SUM (koruny) FOR  sekce $ ('REZIE4') TO lnostat
SUM (koruny) FOR  sekce $ ('REZIE3') TO lnRezie
SUM (koruny) FOR  sekce $ ('SEKTOR') TO lnSekce
SUM (koruny) TO lnPodil

lnPodil= lnPodil - lnostat - lnRezie - lnSekce

produces a result of lnPodil = 1 721 761,07.

My code written in SQL :

declare @lnOstat decimal(18,5) = 0
declare @lnRezie decimal(18,5) = 0
declare @lnSekce decimal(18,5) = 0
declare @lnPodil decimal(18,5) = 0  

select p.datum, abs(p.hodnota) as 'hodnota', abs(p.koruny) as 'koruny', z.sekce into #tmp
from [DOCHAZKA]...[zakslozkap] p, [DOCHAZKA]...[zakslozka] z
where (p.SW = 'HM' or p.SW = 'P6')
      and p.datum between @datestart and @dateend
      and p.ide_slozka = z.ide_slozka
      and z.ide_zak = '6065'

select @lnOstat = SUM(koruny) from #tmp where sekce = 'REZIE4'
select @lnRezie = SUM(koruny) from #tmp where sekce = 'REZIE3'
select @lnSekce = SUM(koruny) from #tmp where sekce = 'SEKTOR'
select @lnPodil = SUM(koruny) from #tmp

select @lnPodil = isnull(@lnPodil,0) - isnull(@lnOstat,0) - isnull(@lnRezie,0) - isnull(@lnSekce,0) 
drop table #tmp

produces a result of @lnPodil = 1 623 779.67.

So, there is o 100k difference and because this is about money, it is a lot. I'm desperate in searching for a solution so I'm asking there. Is my SQL translation exactly reflecting the code from FoxPro ?

Tables are same, so the data. In SQL, I'm using linked server for access those dbfs. The field "koruny" is stored in dbf as float data type.

Upvotes: 1

Views: 408

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23867

In your filters there is a date based filtering. When you need to filter on dates (datetimes) you shouldn't use between() or Between in SQL server. It is a source for error and there is a chance that you would miss records which should be in select list or you might get more records than you should see (because there is no way to define a datetime range correctly using "between").

Also you should keep in mind that, likely in SQL server your values are datetime values while you may have used date in VFP. Be sure you are selecting the same range.

Consider this case:

You have sales where saleTime represents the datetime the sale has occurred. You want to get all sales that are made in January 2013, how could you write that using between? No way.

set @dateStart = '2013/1/1' set @dateEnd = ???

If you set @dateEnd: - to '2013/2/1' then you might be including sales from february. - to '2013/1/31' then likely you are missing sales on the last day of month - to '2013/1/31 23:59:59.xxx' then you may miss some sales on last day (slim chance but it happens and SQL server is sensitive down to 3 ms resolution).

The correct way is not to use BETWEEN and set the dateEnd to the minimum time that is greater than the desired max time. IOW to get january 2013 correctly:

set @dateStart = '2013/1/1' set @dateEnd = '2013/2/1' -- means 1 Feb 2013 00:00:00. Minimum time that we want to exclude

select ... from ... where saleTime >= @dateStart and saleTime < @dateEnd

is the correct query with a datetime range.

Upvotes: 1

DRapp
DRapp

Reputation: 48179

If someone converted the data from VFP up to SQL server, I'm wondering if there are records marked for deletion in VFP that were NOT uploaded to SQL server. This is handled in VFP by using "SET DELETED ON" to hide records marked for deletion. "SET DELETED OFF" to ALLOW deleted records to be seen (and ultimately included in the query you have).

That said, I would then confirm the COUNT of records being processed in your query by checking the @@rowcount from SQL to see if it matches too.

Now, there are some things not "Exact"ly how VFP does things, but based on probability, I think you are otherwise ok and that is with respect to the "$" in VFP. The "$" is used to say is the thing on the left side ANYWHERE in the string to the right...

lcZnak1 = "HM,P6";

WHERE (Zakslozkap.SW $ lcZnak1)

your conversion to

where p.SW = 'HM' or p.SW = 'P6'

is probably ok, but here's the difference

Lets say you have a value in the "SW" column of an "M" or a "P", or even an "M," or "P," or even "HM,P", "HM,", ",P6", etc ALL of them would qualify. It's almost like a LIKE command in SQL. Similarly in your summations at the bottom. But, if your columns are of "x" characters wide to match what you are looking for, you are probably in good shape.

So, all that being said, I would consider the "DELETED" status of records from the conversion. You can also test your VFP side query just by doing the following first...

SET DELETED ON

Do the rest of your var and querying.

with DELETED ON, you are "IGNORING" any deleted records.

Upvotes: 2

Related Questions