7 Replies Latest reply on Jul 12, 2012 9:56 AM by sandeepmallik

    PL/SQL HELP

    sandeepmallik
      Hi,

      I have to develop a report on based on the following query -

      SELECT
      TRIP_NUMBER,
      ADVANCE_AMOUNT,
      ((SELECT NVL(TRAVEL_MASTER.ADVANCE_AMOUNT,0) FROM TRAVEL_MASTER WHERE TRIP_NUMBER=85)-
      (NVL((SELECT SUM(TRAVEL_DETAIL.TICKET_AMOUNT) FROM TRAVEL_DETAIL WHERE TRAVEL_DETAIL.TICKET_BOOKED_BY='Self' and TRAVEL_DETAIL.TRIP_NUMBER=85),0) +
      (SELECT SUM(NVL(LOADGING_BOARDING.HOTEL_AMOUNT,0)+NVL(LOADGING_BOARDING.FOOD_EXPENSE,0)+NVL(LOADGING_BOARDING.MISC,0)) FROM LOADGING_BOARDING
      WHERE LOADGING_BOARDING.TRIP_NUMBER=85)+(SELECT NVL (SUM(CONVEYANCE.AMOUNT),0) FROM CONVEYANCE WHERE CONVEYANCE.TRIP_NUMBER =TRAVEL_MASTER.TRIP_NUMBER))) "PAYABLE/RECEIVABLE"
      FROM
      TRAVLE_MASTER
      WHERE TRIP_NUMBER = 85

      If you look at the query, the trip number '85' is hard coded in this example....within the subquery as well as in the main query in where condition.

      I have to generate the report based on all the trip numbers those are having status open in the travel_master table...

      How to achieve the result?

      I was thinking of - 1. Writing a cursor to fetch all the trip numbers those are open 2. creating a loop and passing the trip numbers in the loop

      If this is a suitable solution please let me know how can I write a cursor and create a loop? Please let me know the syntax in detail as I am not a techie.

      Regards,
      Sandeep
        • 1. Re: PL/SQL HELP
          Venkadesh Raja
          refer this

          http://sql-plsql.blogspot.in/2007/05/oracle-plsql-cursor-for-loops.html


          http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/cursor_for_loop_statement.htm

          http://www.techonthenet.com/oracle/loops/cursor_for.php
          • 2. Re: PL/SQL HELP
            sandeepmallik
            Hi,

            Thanks for the links,

            However I could not find how to use a select statement inside a loop......

            It would be great if you can give one example on the same.

            Regards,
            Sandeep
            • 3. Re: PL/SQL HELP
              Venkadesh Raja
              sandeep this ?
              begin
              for i in ( select ename from emp ) loop
              dbms_output.put_line(i.ename);
              end loop;
              end;
              
              
              KING
              BLAKE
              CLARK
              JONES
              SCOTT
              FORD
              SMITH
              ALLEN
              WARD
              MARTIN
              TURNER
              • 4. Re: PL/SQL HELP
                N_i_R_v_A_n_A
                Is Doing this with PL/SQL necessary ??, i guess this could be done through simple SQL.

                If only you could provide we with the create Table scripts for your table and some sample data.

                And your desired output as well.


                Am not clear on the requirements , but here goes,

                with open_data as (select trip_number from travel_master where status = 'open')
                SELECT
                TRIP_NUMBER,
                ADVANCE_AMOUNT,
                ((SELECT NVL(TRAVEL_MASTER.ADVANCE_AMOUNT,0) FROM TRAVEL_MASTER WHERE TRIP_NUMBER= o.trip_number)-
                (NVL((SELECT SUM(TRAVEL_DETAIL.TICKET_AMOUNT) FROM TRAVEL_DETAIL WHERE TRAVEL_DETAIL.TICKET_BOOKED_BY='Self' and TRAVEL_DETAIL.TRIP_NUMBER= o.trip_number),0) +
                (SELECT SUM(NVL(LOADGING_BOARDING.HOTEL_AMOUNT,0)+NVL(LOADGING_BOARDING.FOOD_EXPENSE,0)+NVL(LOADGING_BOARDING.MISC,0)) FROM LOADGING_BOARDING 
                WHERE LOADGING_BOARDING.TRIP_NUMBER= o.trip_number)+(SELECT NVL (SUM(CONVEYANCE.AMOUNT),0) FROM CONVEYANCE WHERE CONVEYANCE.TRIP_NUMBER =TRAVEL_MASTER.TRIP_NUMBER))) "PAYABLE/RECEIVABLE"
                FROM
                TRAVLE_MASTER ,open_data o
                WHERE TRIP_NUMBER = o.trip_number
                
                Not tested!!!!
                Edited by: N_i_R_v_A_n_A on Jul 6, 2012 11:50 PM
                • 5. Re: PL/SQL HELP
                  Etbin
                  Maybe
                  select tm.trip_number,
                         tm.advance_amount,
                         nvl(tm.advance_amount,0) - td.ticket_amount + lb.other_expences + ce.conveyance_amount) "PAYABLE/RECEIVABLE"
                    from (select trip_number 
                            from travel_master
                           where lower(status) = 'open'
                         ) tn
                         left outer join
                         travel_master tm
                      on tn.trip_number = tm.trip_number
                         left outer join
                         (select trip_number,
                                 nvl(sum(ticket_amount),0) ticket_amount
                            from travel_detail 
                           where ticket_booked_by = 'Self'
                           group by trip_number
                         ) td
                      on tn.trip_number = td.trip_number 
                         left outer join
                         (select trip_number,
                                 sum(nvl(hotel_amount,0) + nvl(food_expense,0) + nvl(misc,0)) other_expences
                            from loadging_boarding
                           group by  trip_number 
                         ) lb
                      on tn.trip_number = lb.trip_number 
                         left outer join
                         (select trip_number,
                                 nvl(sum(amount),0) conveyance_amount
                            from conveyance
                           group by trip_number 
                         ) ce
                      on tn.trip_number = ce.trip_number
                  Regards

                  Etbin
                  1 person found this helpful
                  • 6. Re: PL/SQL HELP
                    sandeepmallik
                    Thanks for the query, I am able to generate the required output.

                    Regards,
                    Sandeep
                    • 7. Re: PL/SQL HELP
                      sandeepmallik
                      Thanks, it was very helpful and I got answer to my query.


                      Regards,
                      Sandeep