>I have a table where a job number field can have multiple records. It has a field also that holds an archive flag.
>
>I need a SQL statement that will only result in a list of job numbers that when grouped together not one of them has an archive flag value of 0.
>
>The following will give me job numbers that I do not desire. Because for instance job number MES4000 is listed 3 times with two records with archive flag set to 1 an one record set to 0.
>So I would not want this in my result.
>
>

>
>testconnString = "Select Distinct jobno, archivef From webprddt1.resmngta0" +;
>				" Where posstr(jobno,'MES') > 0 And archivef = 1"
>
>

CREATE CURSOR Jobs (JobNo c(10), archiveF N(1))

INSERT INTO Jobs VALUES ('MES0', 1)
INSERT INTO Jobs VALUES ('MES1', 0)
INSERT INTO Jobs VALUES ('MES1', 1)
INSERT INTO Jobs VALUES ('MES2', 1)
INSERT INTO Jobs VALUES ('MES2', 1)
INSERT INTO Jobs VALUES ('MES3', 1)

SELECT DISTINCT JobNo, archiveF FROM Jobs WHERE PosStr(JobNo, 'MES') > 0 AND JobNo NOT in (SELECT JobNo FROM Jobs WHERE archiveF = 0)

FUNCTION PosStr (source AS String, locate AS String) AS Integer

	RETURN VAL(SUBSTR(m.source, AT(m.locate, m.source) + LEN(m.locate)))
	
ENDFUNC