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
