Passthrough query error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I posted this at the Programming community and did not get a reply. Perhaps
someone here can help.

I am trying to update an SQL table using a pass through query. I want to
populate two of the fields conditionally, depending on the Source of Payment
and I am using IF...THEN...ELSE. I get errors back when I try to run this. I
get "Error near IF" and "Error on line 4 near p". I cannot figure out my
errors and wonder if anyone can help me. The script is:

DELETE tblSuspendedPayments
INSERT INTO tblSuspendedPayments (category, FinanceClass, ProviderCode,
PatPaid, InsPaid) SELECT f.category, p.FinanceClass, p.ProviderCode,
IF source_of_payment = '1'
BEGIN
p.amount_of_payment As PatPaid, 0 AS InsPaid
END
ELSE
BEGIN
0 As PatPaid, p.amount_of_payment AS InsPaid
END
FROM Payments p INNER JOIN FinanceClass f ON p.FinanceClass = f.FinanceClass
WHERE suspended = 'S' ;
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the 2 CASE statements instead of IF...Else. E.g.:

INSERT INTO tblSuspendedPayments (category, FinanceClass, ProviderCode,
PatPaid, InsPaid)

SELECT f.category, p.FinanceClass, p.ProviderCode,

CASE WHEN source_of_payment = '1'
THEN p.amount_of_payment
ELSE 0
END As PatPaid,

CASE WHEN source_of_payment != '1'
THEN 0
ELSE p.amount_of_payment
END As InsPaid

FROM Payments p INNER JOIN FinanceClass f
ON p.FinanceClass = f.FinanceClass

WHERE suspended = 'S'


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


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

iQA/AwUBQcFDSYechKqOuFEgEQKxpwCgsskpYsfNKlY4/yGvDcXQ+WSy990AnR4P
ofoeelDiw7eDWOMe8KIZT5MF
=/xOh
-----END PGP SIGNATURE-----
 
Many thanks. While waiting for a reply I actually did it the way you suggest
and got the query to work. Your syntax for the CASE is more concise than mine
and I learned something thanks. rmb
 
Back
Top