9 Replies Latest reply: Apr 23, 2013 9:14 PM by 493815 RSS

    LOV Query Invalid

    EEG
      Hello.

      I am uisng Apex 4.1 on Oracle 10.2.0.5 and Oracle App Server.

      Would someone please tell me why the following LOV query is causing the dreaded error:
      LOV query is invalid, a display and a return value are needed, the column names need to be different. If your query contains an in-line query, the first FROM clause in the SQL statement must not belong to the in-line query.
      select d, r
      from
      (
      select
      '(select)' as d,
      'javascript:void(0)' as r
      from dual 
      union
      select
      initcap(INFO_NAME) as d,
      'javascript:popUp2(''' || INFO_LINK || ''', 1000, 800);' as r 
      from INFO_LINKS
      ) 
      order by 1
      Running this query in the SQL Commands section returns the result set without problem.

      Related to this, I've noticed that I am getting this same error for many similar LOV "complex" queries that involve things like UNIONs or sub-queries. In fact, when I bring up an already working LOV query in the LOV editor and then simply click the "OK" button without modifying the query, I get this same error. WHY?

      Thanks in advance for any help on this.

      Elie
        • 1. Re: LOV Query Invalid
          Sashio
          Why did you use an inline view at all? The following query returns exactly the same result as yours:
          select
            '(select)' as d,
            'javascript:void(0)' as r
            from dual
          union
          select
            initcap(INFO_NAME) as d,
            'javascript:popUp2(''' || INFO_LINK || ''', 1000, 800);' as r
            from INFO_LINKS
          order by 1
          What happens when you use it instead of your statement?
          • 2. Re: LOV Query Invalid
            EEG
            Hello Everyone.

            I just realized what my mistake is.

            Many of the individual lines in my LOV query are ending with a carriage-return character. And so, the following code line is literally concantonated with the previous line.

            Once I added one or more space characters to the end of each code line, Apex accepted the query without problem.

            Whew! That was tricky, indeed.

            And this is why many of my other already working queries suddenly caused the error when I clcik the OK button in the LOV editor. For some reason, bringing up these already existing, working queries stripped awawy any space characters from the very end of the code codes.

            I hope this helps others facing the same issue.

            Elie
            • 3. Re: LOV Query Invalid
              EEG
              Hi Littlefoot.

              I just now saw your reply after posting my own "solution".

              I originally did write my LOV to be as you have it. But this caused the same error. Because I did not know what was causing this, I then tried "wrapping" my UNION with a surrounding LOV. Again, the same error.

              I finally posted this last incarnation in my Apex help request.

              And so, I agree, your query is simpler.

              In any case, as I've already mentioned, I figured out what was cauisng the error and hope others are helped by it.

              Thanks, again.

              Elie
              • 4. Re: LOV Query Invalid
                EEG
                I thought I had discovered why I was getting the "Invalid LOV" error (due to the lack of adequate spacing between SQL keywords in the LOV query). But I recently found that I am getting this same error for simplistic (previously working) LOV queries such as:
                select ename d, empno r from emp order by 1
                I'm beginning to think this is a bug in 4.1.

                Has anyone else encountered this strange behavior?

                Thank you.

                Elie
                • 5. Re: LOV Query Invalid
                  DanielB
                  are you tried with
                  ??

                  select T.d, T.r
                  from (select'(select)' as d,'javascript:void(0)' as r
                  from dual
                  union
                  select initcap(INFO_NAME) as d,
                  'javascript:popUp2(''' || INFO_LINK || ''', 1000, 800);' as r
                  from INFO_LINKS) T
                  order by 1
                  • 6. Re: LOV Query Invalid
                    fac586
                    EEG wrote:

                    I'm beginning to think this is a bug in 4.1.
                    Could be bug 13520001: {thread:id=2323933}

                    Try the workaround of specifying the LOV as a function body returning a SQL query.
                    • 7. Re: LOV Query Invalid
                      EEG
                      Hi, Daniel.

                      Thanks for your advice.

                      Yes. I surrounded by LOV query with the outer SELECT. But, un-fortunately, this did not work until I embedded extra space characters after the end-of-lines in my SELECT query. Only then did trying the outer SELECT work and the LOV editor "accepted" my LOV.

                      But, as already mentioned above, embedding extra spaces does not work for other already existing/working LOV queries, even those quite simple in structure.

                      Thank you.

                      Elie
                      • 8. Re: LOV Query Invalid
                        EEG
                        Hi.

                        Thanks very much for giving more credence to the suspicion that I'm running into a 4.1 bug.

                        I'll try out the suggested solution.

                        Thank you.

                        Elie
                        • 9. Re: LOV Query Invalid
                          493815
                          There's a very simple workaround to this problem described in the following article http://technologydribble.info/2013/01/08/lov-query-is-invalid-in-oracle-apex-4-1-when-using-subquery/

                          Check it out.

                          Cheers,
                          Ariel