3 Replies Latest reply on Sep 27, 2006 3:15 PM by Wilgeno_wnt

    Query of Query

    Wilgeno_wnt Level 1
      I used to be able to use the SQL upper function in a Query of Query. It seems this has been broken with the lastest updates. The queries below show before and after the 7.02 updates.

      The main problem is that the values can be numeric or alpha and I need to handle the fact that the IN is a case sensitive function.

      The error I get is

      The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.

      Null Pointers are another name for undefined values.

        • 1. Re: Query of Query
          MikerRoo Level 1
          Adobe really messed this up. They changed query and QofQ null behavior to make it more standard but didn't make the changes uniformly or well.

          Some of the values of listing_office_id are null. Upper() now chokes on nulls (unlike many RDBMS like Access, MS SQL, etc).

          You can use CAST, in the QofQ, to fix the error but then upper() no longer U cases the cast expression! {That is: UPPER ('abc') = 'ABC' but UPPER (Cast ('abc' AS VARCHAR)) = 'abc' still!}.

          The best, smartest, thing to do is to eliminate nulls and/or upper-case in the original query.

          A less desirable fix is to change your QofQ where clause to:

          WHERE listing_office_id IS NOT NULL
          AND upper (listing_office_id) IN ('168047','233840')

          Please feel free to voice your displeasure at: http://www.macromedia.com/support/email/wishform/
          • 2. Re: Query of Query
            Wilgeno_wnt Level 1
            I'd love to eliminate the NULLS, but we do not control the data. We are recipients of data from various MLS data vendors and we have to eat their crap. Our contracts with vendors prevent us from altering the data. We might be able to convert NULL to empty strings. Would that resolve this? I'm guess that since upper does not work around the cast taht lower will not either? Another possiblity is to use the upper function inthe original query that way the QofQ does not need to worry about the case.

            For now I will try using upper in the original query, and as a last resort the less desireable solution you suggested.

            Thanks for the response.

            • 3. Re: Query of Query
              Wilgeno_wnt Level 1
              I added isnull(properties.listing_office_id,'') as listing_office_id to the original query that I am doing a query of query on and it fixed the problem.