Nevermind.. I was not thinking right..
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; I think this should give me the result I was after. Joe _____ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Jarl Grøneng Sent: Friday, May 15, 2015 1:29 PM To: arslist@ARSLIST.ORG Subject: Re: Sort of OT: Help with an SQL query on the AR System database... ** Hi This subquery will list all the fields from all the forms.... You need to include the schemaid (A.FIELDID in (select B.FIELDID from FIELD_CHAR B where B.MAXLENGTH = 0 or B.MAXLENGTH >= 1000)) -- J 2015-05-15 19:13 GMT+02:00 Joe D'Souza <jdso...@shyle.net>: ** 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? Im hoping someone would spot what I cant 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_ _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"
