thank you for your help - I made a minor correction to your command to
eliminate the error msg null command incorrect)
I used the following - and everything work fine - I need to learn about
inner and outer join functions - where do you recommend I find this
information
J
DROP VIEW VWA9iInvHIST
CREATE VIEW `VWA9iInvHist` (InvNum, InvDate, INVTOT, TransDate, Payment)
AS +
SELECT t1.InvNum, t1.InvDate, t1.INVTOT,
(IFNULL(t2.TransDate,*NULL,T2.TRANSDATE*)),(IFNULL(t2.Payment,*0,PAYMENT*))
+
FROM VWA9iInvtot T1 +
LEFT OUTER JOIN VWA9InvPmt t2 ON t1.InvNum = t2.InvNum
RETURN
On 4/8/2011 1:24 PM, Albert Berry wrote:
You need to use a LEFT OUTER JOIN which lists all the entries in the
InvTot table whether or not there are entries in the payments table.
It will fill the payment side with nulls
CREATE VIEW `VWA9iInvHist` (InvNum, InvDate, Sale, TransDate, Payment)
AS +
SELECT t1.InvNum, t1.InvDate, t1.Sale, t2.TransDate,
(IFNULL(t2.Payment,0.00)) +
FROM vwInvTot +
LEFT OUTER JOIN VWA9InvPmt t2 ON t1.InvNum = t2.InvNum
Albert
On 08/04/2011 11:11 AM, J BLAUSTEIN wrote:
I have removed the extra ")" - I'm trying to combine both table with
all the information - but I need to allow for when no payments are made
J**
--
*J. Blaustein*
*J Blaustein Associates, Inc.*
* 12 Herrick Drive*
* Lawrence, NY 11559*
*516-371-3445 FAX 516-345-8009*
