6 Replies Latest reply: Mar 29, 2013 5:17 PM by 726704 RSS

    Report Column Attributes LOV using a report field within PL/SQL LOV

    726704
      Hello

      I wish to used a field in report within LOV PL/SQL.

      e.g.

      Select NO_ROOMS, NO_ROOMS_AVAIL from Rooms.

      The LOV PL/SQL for field NO_ROOMS

      select * from (select count_no from count2 order by count_no) rooms where rownum <= NO_ROOM_AVAIL

      The syntax for this statement is wrong for the NO_ROOM_AVAIL . Can you help?

      Thanks, Pete
        • 1. Re: Report Column Attributes LOV using a report field within PL/SQL LOV
          Tobias Arnhold
          Hi Pete,

          if I understand you right. You have a report and one column should use a LOV based on SQL Select.

          First each LOV needs a display and a return column. Second your select is wrong because the outer select doesn't know NO_ROOM_AVAIL!? If no_room_avail is a column from your main report then it cannot be part in your LOV.

          You need to describe the tables and the data more clearly. We cannot only guess what you want you must be more detailed in your explanation.

          Cheers

          Tobias
          • 2. Re: Report Column Attributes LOV using a report field within PL/SQL LOV
            726704
            Hello

            The current List of values definition from NO_ROOM in the select statement ..

            Select NO_ROOM, NO_ROOM_AVAIL from ROOM;

            LOV ....

            select * from (select count_no from count2 order by count_no) Fred where rownum <= 6

            This works fine. I now wish to replace the "6" with a variable contained with the report. It this case NO_ROOM_AVAIL

            So the statement will look something like this

            select * from (select count_no from count2 order by count_no) Fred where rownum <= NO_ROOM_AVAIL

            but the Syntax is wrong

            I hope this makes better sense?

            Pete
            • 3. Re: Report Column Attributes LOV using a report field within PL/SQL LOV
              Tobias Arnhold
              Ok that is your report:
              Select NO_ROOM, NO_ROOM_AVAIL from ROOM;

              And NO_ROOM is the column where a LOV should be used in. But then this cannot be a valid LOV:
              select * from (select count_no from count2 order by count_no) Fred where rownum <= 6

              To be valid it should look like that:
              select count_no as d, count_no as r from (select count_no from count2 order by count_no) Fred where rownum <= 6

              You cannot add NO_ROOM_AVAIL inside a LOV.
              But for me this doesn't make sense at all.

              If it would be the other way around:
              select * from (select count_no from count2 order by count_no) Fred where rownum <= NO_ROOM_AVAIL

              Why don't you do like that:
              select * from (select count_no from count2 order by count_no) Fred where rownum <= (Select NO_ROOM_AVAIL from ROOM)

              Actually I'm still confused. Why don't you create an example on apex.oracle.com

              Or define it even more clearly!
              Report:
              select ... 
              
              LOV:
              select...
              
              For what report column should the LOV be used? 
              Cheers,

              Tobias
              • 4. Re: Report Column Attributes LOV using a report field within PL/SQL LOV
                726704
                Hello

                I tried many different combinations of SQL / LOV without sucess. So let me back to the requirements

                Booking a hotel room ... The table contains

                Hotel Type (e.g. single bed)
                Price
                number of room available NO_ROOM_AVAIL (already calculation based on number of day stay, etc)
                Room Requierd NO_ROOM_REQ

                What like to do is display the number of room required as LOV 0,1,2,3,etc up to the NO_ROOM_AVAIL.

                What is the LOV coding?

                I Understand cannot used column NO_ROOM_AVAIL in the LOV for column NO_ROOM_REQ?

                E.G. For column NO_ROOM_REQ I have creating a count table (count2) which has just contains number 1,2,3,4,etc and then setting the LOV to:- select count1, count_key from count2 where rownum <= NO_ROOM_AVAIL

                Hope this now better sense?

                Pete

                Edited by: Pete 66 on 29-Mar-2013 13:11
                • 5. Re: Report Column Attributes LOV using a report field within PL/SQL LOV
                  emma-apex
                  I might be misunderstanding you, but...

                  If you have a field in your report containing NO_ROOM_AVAIL, let's call it P1_NO_ROOMS, then you can reference this field using bind variable syntax in your LOV as long as you define your LOV within Page 1 and not as a separate component.

                  So within Page 1 you would use SQL to define your LOV (i.e you are not defining a LOV in components and then selecting it from the dropdown).

                  The SQL would run something like this: SELECT column1 d, column2 r from TABLE where column2 < :P1_NO_ROOMS. Don't forget to include P1_NO_ROOMS within the Page Items to Submit under the LOV query (very easy to forget!).

                  I'm a bit confused what the problem is but hope this might help!

                  Emma
                  • 6. Re: Report Column Attributes LOV using a report field within PL/SQL LOV
                    726704
                    Dear Emma

                    Thank for replying.

                    I see where you coming from.

                    In this Report Form there are many rows been displayed. The Item option (:P1_etc) is not available as there are multible values. A different value for each row.

                    select room_type_key, room_price, no_room_avail, no_room_req from rooms_avail

                    no_room_reg at this stage in zero.

                    In the report form you are asking the users to select the number of rooms required. The maximum value for this field is no_room_avail

                    Does this make more snese?

                    Pete