FW: SELECT(MAX) doesn't work in a temp. table

---------- From: cfrancis To: 'Chcuk Francis' Subject: RE: SELECT(MAX) doesn't work in a temp. table Date: Tuesday, March 14, 1995 8:02AM Here is your answer. Oh yes the code works and yes I rechecked it. My test DB had two fields. f1 char(1), i1 integer when f1 is "a" and i1 is 5 the statement "Cnt = 5" is diplayed. When there is no record where f1 = "a" the statement "No Record Found" is displayed. Always keep in mind the data a function returns. Chuck Francis _________________________________________________________ database cwf main define cnt integer let cnt = 0 select max(i1) into cnt from cwf where f1 = "a" if cnt > 0 then display "Cnt = ",cnt else display "No Record Found" end if end main ---------- From: ilist To: internet!rmy.emory.edu!informix-list Subject: SELECT(MAX) doesn't work in a temp. table Date: Monday, March 13, 1995 11:37PM Hi folks, I think I have had problems with outgoint stuff to c.d.i, therefore I post this again and hope for some help...... I have encountered some strange behavior with SELECT(MAX) in the following 4gl-function and don't know where the error is. I am using IBM RS/6000, AIX 3.25 INFORMIX-SE V.4.1x INFORMIX-4GL RDS V.4.11 Here is the part of the code that doesn't work. Function xyz(); DEFINE mmtknr INTEGER ...... ...... ----------------------------------------------------------------- -- Build temp. table ----------------------------------------------------------------- SELECT stg.mtknr, stg.hrst, stg.stufrm FROM stg WHERE stg.semester = 951 AND stg.stgnr = "11" INTO TEMP t_lepodemo WITH NO LOG ----------------------------------------------------------------- -- This selcect-statement matches a row ----------------------------------------------------------------- LET mmtknr = NULL SELECT MAX(t_lepodemo.mtknr) INTO mmtknr FROM t_lepodemo WHERE hrst = "Z" IF STATUS != 0 THEN error "not availible." ELSE display "o.k. mmtknr =", mmtknr END IF -- The above works fine and displays -- e.g. "o.k. mmtknr = 12345" on the screen ----------------------------------------------------------------- -- This selcect-statement does NOT match a row -- because there is no row with hrst = "y" -- in the temp. table t_lepodemo ----------------------------------------------------------------- LET mmtknr = NULL SELECT MAX(t_lepodemo.mtknr) INTO mmtknr FROM t_lepodemo WHERE hrst = "y" IF STATUS != 0 THEN error "not availible." ELSE display "o.k. mmtknr =", mmtknr END IF -- But her the IF-Statement does not seem to work -- it displays "o.k. mmtknr = " on the screen Normally there should be "not availible" on the screen! But the "ELSE"-Statement is executed!!! WHY? I also tried this instead of the above IF-Statement: -- -- mmtknr is initialized to NULL before the SELECT-Statement. -- Therefore mmtknr should be NULL if no record is found -- LET mmtknr = NULL SELECT MAX(t_lepodemo.mtknr) INTO mmtknr FROM t_lepodemo WHERE hrst = "y" IF mmtknr IS NULL THEN error...... ELSE display...... END IF But it didn't work either. Any suggestions? Please e-mail me directly. If there is a solution I'll post it to the net. Tia, Christian ---------------------------------------------------------------------- Christian Heiker | "The solution to the problem Dortmund, Germany | changes the problem" email: heiker@quietsch.ping.de | (Murphy's Law)