9 Replies Latest reply on Jul 27, 2013 2:48 AM by aliceg

    "LOV query is invalid"

    503828
      Any ideas what is wrong with the query below? I can't figure it out.
      Here is the full 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 disp_val d, ret_val r
      FROM (SELECT 'ES Value of ' || TO_CHAR(round(ve_value,-2),'$99,999,999') disp_val, 'ES' ret_val
                FROM QUERY_MAIN qm
                WHERE query_id = nv('P310_QUERY_ID')
                AND subject_propid = nv('P309_BLS_PROPID')
                AND nvl(ve_value,0) > 0

                UNION
                
                SELECT 'PV Value of ' || TO_CHAR(round(APEX_GET_BV(nv('P309_BLS_PROPID'), TO_DATE(:p0_base_date, 'MM/DD/YYYY')),-2),'$99,999,999') disp_val,'PV' ret_val
                FROM dual)

      Thanks in advance!

      Alex

      BTW- this is on APEX 3.1
        • 1. Re: "LOV query is invalid"
          MarilynBT
          Hi,
          Have you tried running your query in SQL Command just to check if the query is good? Sometimes the error message that APEX is displaying is not the actual error. Just don't forget to substitute your parameters with actual values when you run this in sql command.

          -Marilyn
          • 2. Re: "LOV query is invalid"
            135285
            Alex,

            that doesn't solve your problem, but you should not use V or NV in your SQL statements. They can have a massive performance impact. Just use the bind variable syntax with TO_NUMBER(:P310_QUERY_ID) which is the best approach for performance.

            See http://inside-apex.blogspot.com/2006/11/caution-when-using-plsql-functions-in.html for details.

            Patrick
            --------------------------------------------------------------------------------------------------------------------
            *** New *** Oracle APEX Essentials *** http://essentials.oracleapex.info/
            My Blog, APEX Builder Plugin, ApexLib Framework: http://www.oracleapex.info/
            • 3. Re: "LOV query is invalid"
              503828
              Thanks for the tip Patrick! I had no idea.

              Alex
              • 4. Re: "LOV query is invalid"
                503828
                Anybody else have any thoughts on the query? I still can't get the darn thing to run.

                Alex
                • 5. Re: "LOV query is invalid"
                  60437
                  Alex,

                  Is the function apex_get_bv valid?

                  Scott
                  • 6. Re: "LOV query is invalid"
                    135285
                    Alex,

                    have you already tried to run the query in SQL Command window as Marilyn suggested? That should give you a more clear exception what's wrong with the statement.

                    Patrick
                    --------------------------------------------------------------------------------------------------------------------
                    *** New *** Oracle APEX Essentials *** http://essentials.oracleapex.info/
                    My Blog, APEX Builder Plugin, ApexLib Framework: http://www.oracleapex.info/
                    • 7. Re: "LOV query is invalid"
                      9626
                      Hello just had a similiar problem with defining LOV and subquery.

                      Here's the one which I got an error:
                      "1 error has occurred
                      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 VMOD_BEZEICHNUNG d
                      ,VMOD_ID r
                      from VMOD
                      where VMOD_VERW_ID in
                      (Select VERWADM_VERW_ID
                      from VERWADM
                      where VERWADM_PERS_ID = GET_PERSID_FROM_USER(:APP_USER))
                      and VERA_GET_VERWID(VMOD_ID,'vmod') = :P400_SELECT_VERW
                      order by VMOD_BEZEICHNUNG;
                      ---

                      And here's the one which cen be defined without error
                      ---
                      Select VMOD_BEZEICHNUNG d
                      ,VMOD_ID r
                      from VMOD
                      where VMOD_VERW_ID in
                      (Select VERWADM_VERW_ID
                      from VERWADM
                      where VERWADM_PERS_ID = GET_PERSID_FROM_USER(:APP_USER))
                      and VERA_GET_VERWID(VMOD_ID,'vmod') = :P400_SELECT_VERW
                      order by VMOD_BEZEICHNUNG
                      ---

                      The only difference ist the Semikolon(;) at the end of the statement.
                      Bug?

                      I am using APEX version 2.2.1.00.04.

                      Regards,
                      Joerg
                      • 8. Re: "LOV query is invalid"
                        135285
                        Hi Joerg,

                        SQL statements (not just in lovs) in APEX do not require a trailing semi-colon at the end.

                        Patrick
                        --------------------------------------------------------------------------------------------------------------------
                        *** New *** Oracle APEX Essentials *** http://essentials.oracleapex.info/
                        My Blog, APEX Builder Plugin, ApexLib Framework: http://www.oracleapex.info/
                        • 9. Re: "LOV query is invalid"
                          aliceg

                          I have the same situation: I put it in a function with parameters, and I placed it as a query in the field I would like populated, to no avail (fyi, my query works fine in sql). I was able to go around the error mentioned herein but I get no data. ( I only get one ID for one of the departments, with no other details).

                          Here is what I have:

                          My Apex version is 4.0.2.00.06

                          A tabular form with free-hand supervisors, and another column with the supervisor ID which I want to populate with all employees working for a particular department

                          The query:

                          SELECT DISTINCT

                            gnr_emply_id "Dept Emp ID" which goes into DPT_SUPERV_ID,

                            d.dpt_seq_num ||' '||d.dpt_name||' '||gnr_last_name||' '||gnr_first_name||' '||poz "Employee Details"

                          FROM smc_department_t d,

                            per_emply_gnrl_t,

                          ( SELECT crt_empid empid,

                              CASE when nvl(crt_emp_type,'*')='A' and nvl(crt_chair,'*') !='X' THEN 'A'

                              when nvl(crt_emp_type,'*') !='A' and nvl(crt_chair,'*') ='X' then 'C'

                              else ' ' END as poz,

                              to_number(crt_dept) dept

                            FROM per_cert_all_v

                            WHERE crt_sem_code=semester.current_semester

                          UNION

                            SELECT

                              clf_empid empid,

                              CASE when nvl(clf_mgr,'*')='X' then 'M'

                              ELSE ' '

                              END as poz,

                              to_number(clf_work_dept) dept

                            FROM per_clsf_curr_v

                          ),

                          sfa_emp_dept_t e

                          WHERE d.dpt_seq_num=dept

                          AND gnr_emply_id=empid

                          AND NOT NVL(gnr_in_dir,' ') IN ('D','N')

                          AND d.dpt_seq_num=to_number(nvl(ltrim(SUBSTR(e.DPT_ACCT,17,5),0),0))

                          AND e.dpt_seq_num=:DPT_SEQ_NUM

                          AND e.dpt_acct=:DPT_ACCT

                          ORDER BY 2

                          Don't get any error, but no data either.

                          Any insights ? My Apex version is 4.0.2.00.06