>Dudes and Dudettes,
>
>I'm having a bit of a problem translating a VFP SELECT statement to MySQL.
>The following is the code I have so far. The "//" lines are the original
>VFP code, and the lines right underneath the //'s are the MySQL translation.
>Here's what I have so far:
>
>

>//SELECT x.dsid, x.rdt, HOUR(x.rdt) as rdt_hour,
>SELECT x.dsid, x.rdt, date_format(x.rdt,'%h') as rdt_hour, ;
>
>//MINUTE(x.rdt) as rdt_min, ;
>date_format(x.rdt,'%i') as rdt_min,
>
>//x.ttyp, x.tnum, t.cname as tco, ;
>x.ttyp, x.tum, t.cname as tco,
>
>//ALLTRIM(org.ccity)+", "+ALLTRIM(org.cstate) as origin_city, ;
>concat(TRIM(org.ccity), ", ", TRIM(org.cstate)) as origin_city,
>
>//org.cname as origin_loc, ;
>org.cname as orgin_loc,
>
>//dst.cname as dest_loc, ;
>dst.cname as dest_loc,
>
>//ALLTRIM(dst.ccity)+", "+ALLTRIM(dst.cstate) as dest_city, ;
>concat(TRIM(dst.ccity), ", ",TRIM(dst.cstate) as dest_city,
>
>//cmp.cname as client, ;
>cmp.cname as client,
>
>//y.afe, y.type, y.bkname, y.rgname, y.wname, SUBSTR(CMONTH(rdt),1,3)+SUBSTR(ALLTRIM(str(YEAR(rdt))),3,2)+SUBSTR(dsid,2,10) as dnum, ;
>y.afe, y.type, y.bkname, y.rgname, y.wname, CONCAT(date_format(rdt,'%b'), date_fomat(rdt,'%y'),substring(dsheet.dsid,2,10)) as dnum,
>
>//x.fsc-x.fsc as bRate, ;
>x.fsc-x.fsc as bRate,
>
>//x.fsc-x.fsc as ISC, ;
>x.fsc-x.fsc as ISC,
>
>//x.fsc-x.fsc as FSC, ;
>x.fsc-x.fsc as FSC,
>
>//x.fsc-x.fsc as per, ;
>x.fsc-x.fsc as per,
>
>//x.fsc-x.fsc as nen, ;
>x.fsc-x.fsc as nen,
>
>//x.fsc-x.fsc as haz, ;
>x.fsc-x.fsc as haz,
>
>//x.fsc-x.fsc as hr, ;
>x.fsc-x.fsc as hr,
>
>//x.fsc-x.fsc as det, ;
>x.fsc-x.fsc as det,
>
>//x.fsc-x.fsc as other, ;
>x.fsc-x.fsc as other,
>
>//x.fsc-x.fsc as sTotal, ;
>x.fsc-x.fsc as sTotal,
>
>//x.fsc-x.fsc as bHaul, ;
>x.fsc-x.fsc as bHaul,
>
>//x.fsc-x.fsc as Total, ;
>x.fsc-x.fsc as Total,
>
>//x.fsc-x.fsc as Savings, ;
>x.fsc-x.fsc as Savings,
>
>//x.edt, HOUR(x.edt) as edt_hour, ;
>x.edt, date_format(x.edt,'%h') as edt_hour,
>
>//MINUTE(x.edt) as edt_min, ;
>date_format(x.edt,'%i') as edt_min,
>
>//ALLTRIM(uo.fname)+" "+ALLTRIM(uo.lname) as orderedby, ;
>concat(TRIM(uo.fname), " ", TRIM(uo.lname)) as orderedby,
>
>//ALLTRIM(ue.fname)+" "+ALLTRIM(ue.lname) as enteredby ;
>concat(TRIM(ue.fname), " ",TRIM(ue.lname)) as enteredby
>//FROM c_d!dsheet x ;
>FROM dsheet x
>
>//LEFT OUTER JOIN c_d!accounts y ON x.acc = y.afeid ;
>LEFT OUTER JOIN accounts y ON x.acc = y.afeid
>
>//LEFT JOIN c_d!companies cmp ON cmp.cmpid = y.cmpid ;
>LEFT JOIN companies cmp ON cmp.cmpid = y.cmpid
>
>//LEFT JOIN c_d!companies org ON org.cmpid = x.ploc ;
>LEFT JOIN companies org ON org.cmpid = x.ploc
>
>//LEFT JOIN c_d!companies dst ON dst.cmpid = x.sloc ;
>LEFT JOIN companies dst ON dst.cmpid = x.sloc
>
>//LEFT JOIN c_d!companies t ON t.cmpid = x.tco ;
>LEFT JOIN companies t ON t.cmpid = x.tco
>
>//LEFT JOIN c_d!users uo ON uo.uid = x.dby ;
>LEFT JOIN users uo ON uo.uid = x.dby
>
>//LEFT JOIN c_d!users ue ON ue.uid = x.eby ;
>LEFT JOIN users ue ON ue.uid = x.eby
>
>//WHERE cmp.cname like lcCname ;
>WHERE cmp.cname like lcCname   ***** NOTE: this is a local variable in VFP! *****
>
>//AND TTOD(rdt) >= ldDateLow ;
>
>//AND TTOD(rdt) <= ldDateHigh ;
>
>//AND DELETED() = .F. ;
>
>//AND x.acc = lcAccID ;
>
>//INTO CURSOR curDnumList READWRITE
>
>[NOTE: Decided to stop here when I saw the "INTO CURSOR" code. Hold all horses! :^)]
>

>
>Okay. I have a couple of questions. First, what the heck is "x.fsc-x.fsc as Savings"
>and the other lines like it? Is this just an easy way to set these values to zero?
>If so, why would the original programmer want to do this? This is a query is used
>in report generation, and the code does and OLEControl call to Crystal Reports 10.
>Does this have anything to do with this, folks?

I think so. It get zero but keep field type, becuase if you put: 0 AS Savings you will get an integer field, not float or money.


>
>Also, I know that MySQL supports cursors using embedded SQL (in C), but I have
>yet to find out via books like "SQL: The Complete Reference 2nd Ed" and "MySQL 2nd Edition"
>(and web search) if SQL or MySQL supports VFP-like cursors natively. If they don't,
>would anyone out there suggest the best way to pull this off (besides the way that
>uses uses a billon variables and doesn't really act like a cursor? :^) )??? I guess
>I could just throw the cursor away and not use it, but would that be to my
>advantage?
>
>TIA!
>
>Randall

What do you mean with VFP like cursor? and where you wan tot use it? I am not so familiar with MySQL but all articles I read about SQL Sever said: Stay away from cursors :-) they decrease speed dramaticly.