Yes I was not thinking right. I had that subquery before I developed the whole query and left it there and then didn't think it was not required once I brought more tables into the query.
I changed it exactly to what you have down there when Jarl pointed out what I was not looking at :-). Joe _____ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Thad Esser Sent: Friday, May 15, 2015 1:37 PM To: [email protected] Subject: Re: Sort of OT: Help with an SQL query on the AR System database... ** 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 <[email protected]> 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_ _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"
