I have several FoxPro prg's that have been in use for many years. Now we are slowly trying to implement the use of SQL Server and I'm trying to implement that with the prg's. I have created 2 linked servers to the .dbf files that we are using and that all works great. Now I'm trying to figure out how to convert, or translate, the prg code so I can use it in a Crystal Reports query. I know the basic SQL queries and if I test the linked servers with a simple query it all works fine in Crystal. But some of the prg's are quite complex and use cursors, and that's what I'm struggeling with. Here is an example:
Select soheader.partno As Item,;
soheader.sono As sono,;
soheader.rev As sorev,;
soheader.sqty As sqty,;
soheader.need_date As needdate,;
soheader.priority,;
soheader.salesno,;
soheader.crea_date,;
soheader.start_date,;
soheader.remark1,;
soheader.remark2,;
soheader.instr1,;
soheader.instr2,;
soheader.plandate,;
soheader.rev,;
soheader.fgloc,;
soheader.mtlloc,;
soheader.solineno,;
soheader.Userid,;
soheader.part_desc,;
Soroute.opno As routeopno,;
Soroute.loadcenter,;
Soroute.Descrip As Routedes,;
Immaster.misc04 As lottrack,;
immaster.upccode As upccode;
FROM soheader Inner Join Soroute On soheader.sono = Soroute.sono;
LEFT Join immaster On soheader.partno = immaster.Item;
Into Cursor c1
Select c1.*,;
Somater.partno As partno,;
STR(Asc(Somater.Phanref),3)As Phanref,;
STR(Asc(Somater.Phanid),3)As Phanid,;
VAL(Somater.qty_assy) As qty_assy,;
VAL(Somater.qty_aloc) As qty_aloc,;
Somater.Delmark As Delmark;
FROM c1 Left Join Somater On c1.sono = Somater.sono And c1.routeopno = Somater.opno;
INTO Cursor c2
Select c2.*,;
immaster.Descrip As Descrip,;
immaster.stockum As stockum,;
immaster.misc04 As misc04,;
immaster.lotstat As lotstat;
FROM c2 Left Join immaster On c2.partno = immaster.Item;
Into Cursor c3
Select c3.*,;
imstock.lonhand As lotqty,;
imstock.locid As lotloc,;
imstock.lot As lotnum;
FROM c3 Left Join imstock On imstock.Item = c3.partno AND c3.mtlloc = imstock.locid;
Into Cursor c4
Select c4.*, Iif(Empty(Bmrev.fgparent), Bmrev.itemparent, Bmrev.fgparent ) As fg;
FROM c4 Left Join Bmrev On c4.Item + c4.sorev = Bmrev.itemparent + Bmrev.rev;
into Cursor C5
Select C5.*, bmsl.findno, bmsl.scrpad;
FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev;
INTO Cursor C6
How do I deal with the different cursors in SQL so I get the correct end result?
EDIT:
After some great help from David we have come to a point where the query works, but now there is an issue with the conversion from varchar to bigint. I have changed the query to get rid of a couple of issues and here is the current query:
SELECT
SOH.partno AS Item
,SOH.sono AS sono
,SOH.rev AS sorev
,SOH.sqty AS sqty
,SOH.need_date AS needdate
,SOH.priority
,SOH.salesno
,SOH.crea_date
,SOH.start_date
,SOH.remark1
,SOH.remark2
,SOH.instr1
,SOH.instr2
,SOH.plandate
,SOH.rev
,SOH.fgloc
,SOH.mtlloc
,SOH.solineno
,SOH.Userid
,SOH.part_desc
,SOR.opno AS routeopno
,SOR.loadcenter
,SOR.Descrip AS Routedes
,SO.partno AS partno
,STR(ASCII(SO.Phanref), 3) AS Phanref
,STR(ASCII(SO.Phanid), 3) AS Phanid
,(
CASE WHEN ISNUMERIC(SO.qty_assy) = 1
THEN CONVERT(DECIMAL, SO.qty_assy)
ELSE NULL
END
) AS qty_assy
,(
CASE WHEN ISNUMERIC(SO.qty_aloc) = 1
THEN CONVERT(DECIMAL, SO.qty_aloc)
ELSE NULL
END
) AS qty_aloc
,SO.Delmark AS Delmark
,IMM.misc04 AS lottrack
,IMM.upccode AS upccode
,IMM.Descrip AS Descrip
,IMM.stockum AS stockum
,IMM.misc04 AS misc04
,IMM.lotstat AS lotstat
,IMS.lonhand AS lotqty
,IMS.locid AS lotloc
,IMS.lot AS lotnum
,(
CASE WHEN ISNULL(BMR.fgparent, 0) = 0
THEN BMR.itemparent
ELSE BMR.fgparent
END
) AS fg
,BMS.findno
,BMS.scrpad
FROM [LinkedWS]...[Soheader] AS SOH
INNER JOIN [LinkedWS]...[Soroute] AS SOR
ON SOH.sono = SOR.sono
LEFT OUTER JOIN [LinkedAC]...[immaster] AS IMM
ON SOH.partno = IMM.Item
LEFT OUTER JOIN [LinkedWS]...[Somater] AS SO
ON 1 = 1
AND SOH.sono = SO.sono
AND SOR.opno = SO.opno
LEFT OUTER JOIN [LinkedAC]...[imstock] AS IMS
ON 1 = 1
AND SOH.partno = IMS.item
AND SOH.mtlloc = IMS.locid
LEFT OUTER JOIN [LinkedWS]...[Bmrev] AS BMR
ON SOH.partno + SOH.rev = BMR.itemparent + BMR.rev
LEFT OUTER JOIN [LinkedWS]...[bmsl] AS BMS
ON (
CASE WHEN ISNULL(BMR.fgparent, 0) = 0
THEN BMR.itemparent
ELSE BMR.fgparent
END
) + SO.partno + SOH.rev = BMS.itemparent + BMS.itemchild + BMS.rev
When I run this query I get the error that it can't convert the varchar to bigint. If I change bigint to int, I get an error "Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value ' 0.350' to data type int." The fields qty_assy and qty_aloc are both character fields in the FoxPro tables, but as far as I was able to tell there are no characters in those fields. The leading spaces in front of the 0.350 though lead me to believe that I need to maybe add a TRIM to get rid of them?
EDIT 2:
Changed the conversion from BIGINT to DECIMAL and that solved the issue. I'm still getting one error message but that doesn't seem to affect anything. Problem solved!
Cursors represent temporary tables (for more information check this article).
The translated code for SQL Server will look like the following:
SELECT
SOH.partno AS Item
,SOH.sono AS sono
,SOH.rev AS sorev
,SOH.sqty AS sqty
,SOH.need_date AS needdate
,SOH.priority
,SOH.salesno
,SOH.crea_date
,SOH.start_date
,SOH.remark1
,SOH.remark2
,SOH.instr1
,SOH.instr2
,SOH.plandate
,SOH.rev
,SOH.fgloc
,SOH.mtlloc
,SOH.solineno
,SOH.Userid
,SOH.part_desc
,SOR.opno AS routeopno
,SOR.loadcenter
,SOR.Descrip AS Routedes
,SO.partno AS partno
,STR(ASCII(SO.Phanref), 3) AS Phanref
,STR(ASCII(SO.Phanid), 3) AS Phanid
,IIF(ISNUMERIC(SO.qty_assy) = 1, CONVERT(BIGINT, SO.qty_assy), NULL) AS qty_assy
,IIF(ISNUMERIC(SO.qty_aloc) = 1, CONVERT(BIGINT, SO.qty_aloc), NULL) AS qty_aloc
,SO.Delmark AS Delmark
,IMM.misc04 AS lottrack
,IMM.upccode AS upccode
,IMM.Descrip AS Descrip
,IMM.stockum AS stockum
,IMM.misc04 AS misc04
,IMM.lotstat AS lotstat
,IMS.lonhand AS lotqty
,IMS.locid AS lotloc
,IMS.lot AS lotnum
,IIF(ISNULL(BMR.fgparent, 0) = 0, BMR.itemparent, BMR.fgparent) AS fg
,BMS.findno
,BMS.scrpad
FROM Soheader AS SOH
INNER JOIN Soroute AS SOR
ON SOH.sono = SOR.sono
LEFT OUTER JOIN immaster AS IMM
ON SOH.partno = IMM.Item
LEFT OUTER JOIN Somater AS SO
ON 1 = 1
AND SOH.sono = SO.sono
AND SOR.opno = SO.opno
LEFT OUTER JOIN imstock AS IMS
ON 1 = 1
AND SOH.partno = IMS.Item
AND SOH.mtlloc = IMS.locid
LEFT OUTER JOIN Bmrev AS BMR
ON SOH.Item + SOH.sorev = BMR.itemparent + BMR.rev
LEFT OUTER JOIN bmsl AS BMS
ON IIF(ISNULL(BMR.fgparent, 0) = 0, BMR.itemparent, BMR.fgparent) + SO.partno + SOH.sorev = BMS.itemparent + BMS.itemchild + BMS.rev
I've guessed that the fgparent column has numeric data type and that why the line
Empty(Bmrev.fgparent)
has been translated to
ISNULL(BMR.fgparent, 0) = 0
In case that it's alphanumeric, the conversion can be changed based on the information provided in this link.
Equivalence of the functions
FoxPro SQL SQL SERVER
----------------------------
STR STR
ASC ASCII
VAL CONVERT(BIGINT, ...)
IIF IIF
Useful links:
EDIT
Check the below code if you want to use CASE instead of IFF
SELECT
SOH.partno AS Item
,SOH.sono AS sono
,SOH.rev AS sorev
,SOH.sqty AS sqty
,SOH.need_date AS needdate
,SOH.priority
,SOH.salesno
,SOH.crea_date
,SOH.start_date
,SOH.remark1
,SOH.remark2
,SOH.instr1
,SOH.instr2
,SOH.plandate
,SOH.rev
,SOH.fgloc
,SOH.mtlloc
,SOH.solineno
,SOH.Userid
,SOH.part_desc
,SOR.opno AS routeopno
,SOR.loadcenter
,SOR.Descrip AS Routedes
,SO.partno AS partno
,STR(ASCII(SO.Phanref), 3) AS Phanref
,STR(ASCII(SO.Phanid), 3) AS Phanid
,(
CASE WHEN ISNUMERIC(SO.qty_assy) = 1
THEN CONVERT(BIGINT, SO.qty_assy)
ELSE NULL
END
) AS qty_assy
,(
CASE WHEN ISNUMERIC(SO.qty_aloc) = 1
THEN CONVERT(BIGINT, SO.qty_aloc)
ELSE NULL
END
) AS qty_aloc
,SO.Delmark AS Delmark
,IMM.misc04 AS lottrack
,IMM.upccode AS upccode
,IMM.Descrip AS Descrip
,IMM.stockum AS stockum
,IMM.misc04 AS misc04
,IMM.lotstat AS lotstat
,IMS.lonhand AS lotqty
,IMS.locid AS lotloc
,IMS.lot AS lotnum
,(
CASE WHEN ISNULL(BMR.fgparent, 0) = 0
THEN BMR.itemparent
ELSE BMR.fgparent
END
) AS fg
,BMS.findno
,BMS.scrpad
FROM Soheader AS SOH
INNER JOIN Soroute AS SOR
ON SOH.sono = SOR.sono
LEFT OUTER JOIN immaster AS IMM
ON SOH.partno = IMM.Item
LEFT OUTER JOIN Somater AS SO
ON 1 = 1
AND SOH.sono = SO.sono
AND SOR.opno = SO.opno
LEFT OUTER JOIN imstock AS IMS
ON 1 = 1
AND SOH.partno = IMS.Item
AND SOH.mtlloc = IMS.locid
LEFT OUTER JOIN Bmrev AS BMR
ON SOH.Item + SOH.sorev = BMR.itemparent + BMR.rev
LEFT OUTER JOIN bmsl AS BMS
ON (
CASE WHEN ISNULL(BMR.fgparent, 0) = 0
THEN BMR.itemparent
ELSE BMR.fgparent
END
) + SO.partno + SOH.sorev = BMS.itemparent + BMS.itemchild + BMS.rev