How arrange this join ??

[Guest]

In the below query I need to do the first 2 SET statements even if the inner
join finds no match n tblPriority, is this possible?


UPDATE tblSSRData As D INNER JOIN tblPriority As P ON D.TASK_PRI_CD_1 = P.Code
SET D.CLOSDATE = ED(PRO_FROM,CLS_FROM),
D.COMPDATE = ED(PRO_FROM,CLS_FROM,CAN_FROM), D.PRITARG = P.Target
WHERE (((D.REPORT_FROM_DATE)=#1/1/2005#) AND
((D.REPORT_THRU_DATE)=#1/31/2005#));

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


What's the ED() function? Are PRO_FROM, CLS_FROM and CAN_FROM columns
in tblPriority? If they are and you want to set ClosDate and CompDate,
even if there isn't a Code = Task_Pri_CD_1 in tblPriority, you'll have
to do it in a separate UPDATE. Think about it. Where would the values
come from if there isn't a row (record) in tblPriority w/ the same Code?

If the parameters in the ED() function are columns in tblSSRData then
try a LEFT JOIN between the tables. This will mean that D.PRITARG will
be set to NULL when there isn't a matching row in tblPriority.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkSHfoechKqOuFEgEQLE5wCfTdrrv8kUaHQADdyQjpB1J4IIqkYAoOqU
2iA8CdepmubPHb/yPMn+B5Hg
=8f09
-----END PGP SIGNATURE-----

 

[Guest]

Thanks for the response, this should make it clearer:

strSQL = "UPDATE tblSSRData As D INNER JOIN tblPriority As P" & _
" ON D.[TASK_PRI_CD_1] = P.Code SET" & _
" D.CLOSDATE = ED(D.PRO_FROM,D.CLS_FROM)," & _
" D.COMPDATE = ED(D.PRO_FROM,D.CLS_FROM,D.CAN_FROM)," & _
" D.PRITARG = P.Target" & _
" WHERE REPORT_FROM_DATE = #" & DF & "#" & _
" AND REPORT_THRU_DATE = #" & DT & "#"

ED is a function that returns the earliest of multiple dates. I need the
first 2 set statements to run even if the tblPriority join fails since only
the 3rd SET statement depends on that join. I also have a second table I need
to join on and an additional (4th) set statement that will also be
independent.

 

[Van T. Dinh]

May be simpler to run 2 separate SQL Strings. One to update CLOSDATE and
COMPDATE which doesn't involve tblPriority (as far as I can see from your
SQL). The second one to update only PRITARG with data from tblPriority.