The same field id could exist on multiple forms with different field lengths, and as Jarl said, you need to include the schema id. Curious though, do you need the subquery? How about:
select C.SCHEMAID, C.NAME, A.FIELDNAME, A.FIELDID, B.MAXLENGTH from FIELD A, FIELD_CHAR B, ARSCHEMA C where (A.SCHEMAID = C.SCHEMAID) and (A.SCHEMAID = B.SCHEMAID) and (A.FIELDID = B.FIELDID) and (A.FIELDID >= 1000000 AND A.FIELDID <= 1999999) and (B.MAXLENGTH = 0 or B.MAXLENGTH >= 1000) order by C.SCHEMAID, A.FIELDID; On Fri, May 15, 2015 at 10:13 AM, Joe D'Souza <jdso...@shyle.net> wrote: > ** > > I have developed this query for listing all global fields (regular and > window specific) that are either of 0 length or input length above 1000. > > > > select C.SCHEMAID, C.NAME, A.FIELDNAME, A.FIELDID, B.MAXLENGTH from FIELD > A, FIELD_CHAR B, ARSCHEMA C where > > (A.SCHEMAID = C.SCHEMAID) and > > (A.SCHEMAID = B.SCHEMAID) and > > (A.FIELDID = B.FIELDID) and > > (A.FIELDID >= 1000000 AND A.FIELDID <= 1999999) and > > (A.FIELDID in (select B.FIELDID from FIELD_CHAR B where B.MAXLENGTH = 0 > or B.MAXLENGTH >= 1000)) order by C.SCHEMAID, A.FIELDID; > > > > Strangely in the MAXLENGTH column, I even see results that have a > MAXLENGTH of 200, 255 and some other values under 1000 that are not 0.. > What do you see wrong in my query? > > > > I’m hoping someone would spot what I can’t seem to on what I might have > done wrong in my query. > > > > Cheers > > > > Joe > _ARSlist: "Where the Answers Are" and have been for 20 years_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
