Steve: There was a thread a few days ago about LAVG not calculating averages correctly when there was null value. You can use the same approach that was suggested for that situation; you can use the IFNULL function in conjunction with the LMAX function for your calculated column... ( LMAX(IFNULL( col1,0, col1), IFNULL( col2,0,col2), ....)) You get the idea... Javier,
Javier Valencia, PE President Valencia Technology Group, L.L.C. 14315 S. Twilight Ln, Suite #14 Olathe, Kansas 66062-4578 Office (913)829-0888 Fax (913)649-2904 Cell (913)915-3137 ================================================ Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from all system and destroy all copies. ====================================================== -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of Steve Vellella Sent: Sunday, September 25, 2005 10:48 PM To: RBG7-L Mailing List Subject: [RBG7-L] - RE: LMAX Function I have tried it with eqnull on and off and it doesn't seem to make a difference. I also altered CONCOMP transmaster table with two additional trandate fields and I get the same result. Steve Steve Vellella Office: 520-498-2256 Cell: 520-250-6498 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of A. Razzak Memon Sent: Sunday, September 25, 2005 8:57 PM To: RBG7-L Mailing List Subject: [RBG7-L] - RE: LMAX Function At 11:11 PM 9/25/2005, Steve Vellella wrote: >.. it only calculates the max paydate if all 6 paydate columns have data >in them. Is this what I should expect or is this not functioning properly. Steve, First, you need to check the EQNULL settings of your database. SHOW EQNULL or SET VAR vCheckEQNULL = (CVAL('EQNULL')) SHOW VAR vCheckEQNULL FYI, the function LMAX is not the same as the function MAX, which is used only with the SELECT command. LMAX function returns the MAXIMUM value in LIST. Example: SET VAR vLMax = (LMAX(2,10,99,88,75,3,16)) Variable vLMax will return the value of 99, the highest number in the LIST. Very Best R:egards, Razzak.
