Thanks, it worked a treat. Looks like I will have to find time to school and learn SQL (Delphi style). -----Original Message----- From: BJ <[EMAIL PROTECTED]> To: Multiple recipients of list delphi <[EMAIL PROTECTED]> Date: Friday, 27 November 1998 10:08 Subject: RE: [DUG]: Converting from Access and heterogenous SQL Queries >Hi Maurice. > >Looks to me like you don't really care about the data in F59JL02, just that >a record exists there that joins with ItemExport. At the end of the day, >you're going to update the ItemExport table only (you can only ever update >a single table at a time), and any other tables which are of use must be >referenced in the WHERE clause: > >UPDATE ":jetline_Export:ItemExport.dbf"Itemexport > SET Itemexport.EXPORTED = :P_Exported > WHERE Itemexport.EXPORTED IS NULL > AND EXISTS (SELECT 1 FROM ":OWData:testdta.F59JL02"f59jl02 > WHERE (f59jl02.UDBAR = >Itemexport.BARCODE) > AND (f59jl02.UDPLUT = >Itemexport.UNIT) > AND (f59jl02.UDCART = >Itemexport.CTN_NUMBER) > ) > > >Cheers. > BJ... > > >---------- >From: Maurice Butler[SMTP:[EMAIL PROTECTED]] >Reply To: [EMAIL PROTECTED] >Sent: Thursday, 26 November, 1998 19:55 >To: Multiple recipients of list delphi >Subject: [DUG]: Converting from Access and heterogenous SQL Queries > ><<File: ATT00000.html>> >I am trying to transfer data from local databases to a server, the server >then runs a UDF, and I copy a results field back. >The following is an update query that I had in Access converted to DBE type >SQL. It marks the local copy that it was transferred successfully to the >server. The problem is it does not like having a join with an update query. > > >Is there a way I can do this with a select query then an updatequery ? (but >the update query does not appear to support parameters) > > >UPDATE ":jetline_Export:ItemExport.dbf"Itemexport > INNER JOIN ":OWData:testdta.F59JL02"f59jl02 > ON (f59jl02.UDBAR = Itemexport.BARCODE) > AND (f59jl02.UDPLUT = Itemexport.UNIT) > AND (f59jl02.UDCART = Itemexport.CTN_NUMBER) >SET Itemexport.EXPORTED = :P_Exported >WHERE Itemexport.EXPORTED IS NULL > >I have also noted that the DBE does not appear to be as efficent as Access >in reducing the network traffic to the server with the following Query, the >BDE brings all the fields from the server for the 'join' and 'where' while >access only brings what is necessary > >INSERT INTO ":OWDATA:testdta.F59JL02" > ( UDBAR, UDPLGP, UDPLSP, > UDPUP, > UDPLUT, UDCART, UDCYPH, > UDPLGR, > UDUNWT, UDFCNB, > UDEPO, > UDSMTP, UDSPVR, UDPLLC, > UDPLBT, > UDSESN, UDMDTS, UDEV01 ) > >SELECT Palletexport.BARCODE, Itemexport."GROUP", Itemexport.SPEC, > Itemexport.PUP, > Itemexport.UNIT, Itemexport.CTN_NUMBER, Itemexport.CYPHER, > Itemexport.GRADE, > (Itemexport.WEIGHT / 1000) as Kgs, Itemexport.FACTORY, > Itemexport.EPO_NUMBER, > Itemexport.SAMPLE, Itemexport.SPEC_VER, Itemexport.LOCATION, >Itemexport.BATCH, > Itemexport.SEASON, Itemexport.MAN_DATE, "2" as UBEFlag > >FROM ":OWDATA:testdta.F59JL02" f59jl02 > RIGHT OUTER JOIN ":jetline_export:ItemExport.DBF" Itemexport > ON (Itemexport.BARCODE = f59jl02.UDBAR) > AND (Itemexport.UNIT = f59jl02.UDPLUT) > AND (Itemexport.CTN_NUMBER = f59jl02.UDCART) > INNER JOIN "PalletExport.DBF" Palletexport > ON (Palletexport.BARCODE = Itemexport.BARCODE) >WHERE (Itemexport.EXPORTED IS NULL) > AND (f59jl02.UDBAR IS NULL) > AND (Palletexport.NUM_ITEMS > 0) > >NOTES >:OWData is the MS SQL server >:Jetline_Export is the local dbf tables > >------------------------------------------------------------------------ >-------- >Maurice Butler Like Magic Ltd. (025) 273 9248 > > >--------------------------------------------------------------------------- > New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] > Website: http://www.delphi.org.nz > --------------------------------------------------------------------------- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
