Update-SQL subquery problem

[MichaelHawksworth]

Hi Folks

The following code produces a "Function Name is missing )" error when run.

I am setting ctd_sPostCode from the postcode field from the curUpdate cursor where the account codes match.

UPDATE vw_contactdetails		                             ;
 SET vw_contactdetails.ctd_sPostCode   =                     ;
     (select                                                 ;
          curUpdate.postcode                                 ;
      from                                                   ;
          curUpdate                                          ;
      WHERE                                                  ;
          ALLTRIM(curUpdate.accountcode) ==                  ;
          ALLTRIM(vw_contactdetails.ctd_sOrg) )	          ;
    where		              			                  ;
    	ALLTRIM(ctd_sContact) == "HEADOFFICE"      AND       ;
    	ctd_sOrg in (select accountcode from CurUpdate )

Anyone any ideas?


Regards

Michael Hawksworth

 

[Mike Gagnon]

Wouldn'it be :
UPDATE vw_contactdetails ;
SET vw_contactdetails.ctd_sPostCode = ;
(select ;
curUpdate.postcode ;
from ;
curUpdate ;
WHERE ;
ALLTRIM(curUpdate.accountcode) == ;
ALLTRIM(vw_contactdetails.ctd_sOrg) ) ;
and ;
ALLTRIM(ctd_sContact) == "HEADOFFICE" AND ;
ctd_sOrg in (select accountcode from CurUpdate )


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ramani]

Hi,
The error is because...
(select ;
curUpdate.postcode ;
from ;
curUpdate ;
WHERE ;
ALLTRIM(curUpdate.accountcode) == ;
ALLTRIM(vw_contactdetails.ctd_sOrg) )

does not amount to an expression.


Probably, you can use..
UPDATE vw_contactdetails ;
SET vw_contactdetails.ctd_sPostCode = ;
myFunction(ctd_sOrg) ;
WHERE ;
ALLTRIM(ctd_sContact) == "HEADOFFICE" AND ;
ctd_sOrg IN (SELECT accountcode from CurUpdate)
********************************
FUNCTION myFunction
LPARAMETERS tctd_sOrg
SELECT postcode from curUpdate ;
WHERE ALLTRIM(accountcode) == ALLTRIM(tctd_sOrg)
RETURN postCode
********************************



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[MichaelHawksworth]

Thanks guys, I used scan..endscan instead in the end.

The code works fine in Informix and Update should allow sub-queries... 'should' is always to be taken with a pinch of salt tho!



Regards

Michael Hawksworth