12 Replies Latest reply on Mar 23, 2017 7:32 PM by A Raj

    Displaying results from dynamic SQL joining multiple tables

    A Raj

      Hello,

       

      I want to join 3 tables and display the results in a Region. Example query shown below. The columns from TableC below can change based on requirement (E.g. Below sql below has TableC - Column1, Column2, Column3, Column4, Column5. It could change to TableC - Column16, Column17, Column18, Column19, Column20, next run time to different set of columns). I want the results each time to display appropriate column headings based on the columns chosen in the dynamic SQL.

       

            SELECT TableA.Column1,

                   TableA.Column2,

                   TableB.Column1,

                   TableB.Column2,

                   TableB.Column3,

                   TableB.Column4,

                   TableC.Column1,

                   TableC.Column2,

                   TableC.Column3,

                   TableC.Column4,

                   TableC.Column5

              FROM TableA,

                   TableB,

                   TableC

             WHERE TableA.DateColumn = '10-FEB-17'

               AND TableA.Column1 = TableB.Column1

               AND TableB.Column1 = TableC.Column1

          ORDER BY TableA.Column1;

       

       

      Any help is much appreciated.

        • 1. Re: Displaying results from dynamic SQL joining multiple tables
          joejetta

          How are you planning on making the query dynamic?  By using a bind variable in the where clause?  Are you using a classic or interactive report for this?  If you just write a query that includes all the Table C columns you can place conditions on the columns so they either display or don't display based on the "dynamic" ability you hope to use.

           

          Since you're being vague in your description I'll just make up a scenario.  Pretend you're trying to query a employees by location.  You could have a page item (:P1_LOCATION) that you set when going to that page.  In the query you could filter by WHERE LOCATION =:P1_LOCATION  and you could create conditions on the columns (:P1_LOCATION IN (123,234,345)  so that the column only displayed when it was location 123, 234 or 345.

          • 2. Re: Displaying results from dynamic SQL joining multiple tables
            mark123

            Classic report is fairly straightforward

             

            function returning sql as  query source

            and

            function returning  colon delimited headings in the report attributes

             

            You could probably work out the headings at the same time you're creating the dynamic SQL  for the  query.

             

            Otherwise you'd need to either parse the SQL to get the headings or use dbms_sql, describe_columns.

            • 3. Re: Displaying results from dynamic SQL joining multiple tables
              m_yasirali

              You can easily select columns based on any condition. You don't need complex dynamic statement OR Function Returning query to do that as following:

               

              SELECT TableA.Column1,

                           TableA.Column2,

                           TableB.Column1,

                           TableB.Column2,

                           TableB.Column3,

                           TableB.Column4,

                           case :P1_CONDITION_ITEM

                             WHEN '1st_condition' Then

                             TableC.Column1

                             WHEN '2nd_condition' Then

                             TableC.Column16

                             Else

                             TableC.Column26

                           End as Column_Label_1,

                           case :P1_CONDITION_ITEM

                             WHEN '1st_condition' Then

                             TableC.Column2

                             WHEN '2nd_condition' Then

                             TableC.Column17

                             Else

                             TableC.Column27

                           End as Column_Label_2,

                          .

                          .

                          .

                          Other TableC.Columns

                      FROM TableA,

                           TableB,

                           TableC

                     WHERE TableA.DateColumn = '10-FEB-17'

                       AND TableA.Column1 = TableB.Column1

                       AND TableB.Column1 = TableC.Column1

                  ORDER BY TableA.Column1;

              • 4. Re: Displaying results from dynamic SQL joining multiple tables
                Mike Kutz

                Why are you not selecting all columns and teach the users how to use the Action button on the IR?

                You know you can save those preferences. .. right?

                 

                Oh...and the ORDER BY is going to get overridden by the end users when they figure out they can adjust the Sort too.  I like to include the ORDER BY I'm the default report and drop it from the SQL.

                 

                My $0.02

                 

                MK

                • 5. Re: Displaying results from dynamic SQL joining multiple tables
                  mark123

                  You obviously missed

                   

                  I want the results each time to display appropriate column headings based on the columns

                   

                  which is the difficult bit he is actually asking about.

                   

                  In order to achieve that, you  need to know separately which columns have  actually been used and the logic for working that out is  going to be exactly the same as working out which data needs to be used.  It may well be that the column selection in reality is somewhat more complex than his example in which case, given that all Apex SQL is "dynamic" anyway,  you have simply ended up with  a very complex but static lump of dynamic SQL which still needs a chunk of code to work out the headings instead of a single chunk of code which churns out  some SQL at runtime which only  includes the columns needed and the corresponding headings at the same time.

                  • 6. Re: Displaying results from dynamic SQL joining multiple tables
                    A Raj

                    Let me explain my actual requirement. I should have given the proper requirement during my first post itself; I apologize for not doing so. When I pass a day+hour, the query has to select the past 8 individual hours and give its details. The results come from a "hour_data" table which has the surrogate key load_date.

                     

                    E.g1. Passing day : 03/19/2017 hour : 21, should give me the results of 03/19/2017 below hours

                     

                    hour21  hour20   hour19  hour18  hour17  hour16  hour15  hour14 

                     

                    E.g2. Passing day : 03/19/2017 hour : 3, should give me the results of 03/19/2017 and 03/18/2017 as below

                     

                    hour3  hour2   hour1  hour24  hour23  hour22  hour21  hour20

                     

                    a) I have created a pl/sql script; it takes the day+hour as parameters and generates the target dynamic sql selecting from hour_data table.

                    b) I have created a table like "day_hour_results" with 8 generic columns hour8_result,hour7_result,hour6_result,hour5_result,hour4_result,hour3_result,hour2_result,hour1_result

                    c) I execute the dynamic sql in the pl/sql script and store the result in day_hour_results table

                    d) At apex, I select from the day_hour_results table

                    e) I also generate the column heading in the pl/sql script (but I am not sure how to pass these column headings back into Apex)

                     

                    So, when displaying the results in Apex, I need to show the actual columns as in Eg2 based on requirement

                     

                    hour3  hour2   hour1  hour24  hour23  hour22  hour21  hour20

                     

                    and not generic hour8_result,hour7_result,hour6_result,hour5_result,hour4_result,hour3_result,hour2_result,hour1_result

                     

                    Please let me know how I can achieve this. Let me know if there is a better way of doing this as well.

                     

                    I am primarily a DBA and I have been assigned to do the Apex reports; so I am relatively new to Apex.

                     

                    Thank you all for your patient replies and time.

                    • 7. Re: Displaying results from dynamic SQL joining multiple tables
                      Mike Kutz

                      I still don't see why you are doing "Dynamic SQL".

                      If you PIVOT your data, then, you just need to "Dynamically show/hide/[order]" the columns in an IR.

                       

                       

                      WHERE clause doesn't need it

                      APEX Bind variables are always bound as VARCHAR2.

                      Creating the first parameter for the TO_DATE() should be simple

                      to_date( p5_date || ' ' || lpad( p5_hour, 2, '0'), 'DD-MM-YYYY HH24')
                      

                       

                      No dynamic SQL needed for WHERE clause.

                       

                      Projection of Data doesn't need it

                      An example using PIVOT based on the Hour of some DATE column:

                      with data as (
                        select t.*, extract(hour from t.date_column) hour_value
                        from source_table t
                        where date_column between
                          to_date( p5_date || ' ' || lpad( p5_hour, 2, '0'), 'DD-MON-YYYY HH24') - 8/24 -- you'll need to adjust based on date format from APEX
                          and to_date( p5_date || ' ' || lpad( p5_hour, 2, '0'), 'DD-MON-YYYY HH24')
                      )
                      select <other column>, "Hour 1", "Hour 2", ..., "Hour 24"
                      from data
                      Pivot (
                       sum( some_data )
                       for hour_value
                       in (
                        1 as "Hour 1",
                        2 as "Hour 2",
                        3 as "Hour 3",
                        4 as "Hour 4",
                        5 as "Hour 5",
                        6 as "Hour 6",
                        7 as "Hour 7",
                        8 as "Hour 8",
                        9 as "Hour 9",
                        10 as "Hour 10",
                        11 as "Hour 11",
                        12 as "Hour 12",
                        13 as "Hour 13",
                        14 as "Hour 14",
                        15 as "Hour 15",
                        16 as "Hour 16",
                        17 as "Hour 17",
                        18 as "Hour 18",
                        19 as "Hour 19",
                        20 as "Hour 20",
                        21 as "Hour 21",
                        22 as "Hour 22",
                        23 as "Hour 23",
                        24 as "Hour 24"
                      );
                      

                       

                      See?  Dynamic SQL is not needed.

                       

                      Now all you have to do is to dynamically show-hide those static columns.

                      I've heard of people doing this before.

                       

                      I don't know if you can set the order. (so that "Hour 24" comes after "Hour 1" for your example)

                       

                      Worst case, I'm sure you can create 24 Reports with the correct columns in the correct order and dynamically choose the correct report.

                       

                      I recommend a different thread for this capability.

                       

                       

                      My $0.02

                       

                       

                      MK

                       

                      PS - to change your screen name

                      https://community.oracle.com/thread/3650233

                      • 8. Re: Displaying results from dynamic SQL joining multiple tables
                        A Raj

                        Thank Mike Kutz. Appreciate your prompt reply.

                        I see your query. Very nice one. However my requirement is slightly different.

                         

                        I have numerous rows returned for each day+hour based on facility (sorry I haven't explained it previously).

                        Example of dynamic query generated for day 03/19/2017 hour 2. For hours 1,2 I take from current day, hours 24-hour19 take from previous day.

                        Note these hour columns will change as per the passed day+hour.

                         

                         

                                  SELECT c.facility_key,

                                         c.facility_name,

                                         d.max_value,

                                         d.min_value,

                                         d.nmax_value,

                                         d.nmin_value,

                                         HOUR_2,               -- run time col1

                                         HOUR_1,               -- run time col2

                                         HOUR_24,               -- run time col3

                                         HOUR_23,               -- run time col4

                                         HOUR_22,               -- run time col5

                                         HOUR_21,               -- run time col6

                                         HOUR_20,               -- run time col7

                                         HOUR_19,               -- run time col8

                                         HOUR_2 - HOUR_1 "1 HOUR DIFF",               -- run time col9

                                         HOUR_2 - HOUR_19 "8 HOUR DIFF"               -- run time col10

                                    FROM (SELECT facility_key, HOUR_2, HOUR_1, uom_key, facility_equip_key FROM facility_value WHERE operating_date = '19-MAR-17') a,

                                         (SELECT facility_key, HOUR_24, HOUR_23, HOUR_22, HOUR_21, HOUR_20, HOUR_19, uom_key, facility_equip_key FROM facility_value WHERE operating_date = '18-MAR-17') b,

                                         facility_site c,

                                         min_max_spill_elv d

                                   WHERE a.facility_key = b.facility_key

                                     AND b.facility_key = c.facility_key

                                     AND a.facility_equip_key = b.facility_equip_key

                                     AND a.facility_key = d.facility_key

                                     AND a.uom_key = b.uom_key

                                     AND a.facility_equip_key = 4

                                     AND a.uom_key = 4

                                   ORDER BY a.facility_key;

                         

                        For now, a) I have created 8 hidden columns in a region1 b) Generate the column headings in the pl/sql script; store it in the same temp table where I store the sql result (but with a unique key like -10000). c) Fetch the generated column headings into the 8 hidden columns. d) At regions2 reporting, have the final columns headings refer to the hidden column values (like &display_hour8_heading.)

                        That seems to work. Though it may not be the right/better way of doing. Thanks all.

                        • 9. Re: Displaying results from dynamic SQL joining multiple tables
                          Mike Kutz

                          Even without a CREATE TABLE statement, I'm saying that you have a bad schema design (for this type of report).

                           

                          You need to UNPIVOT your data so that you have

                          FACILITY_KEY, FACILITY_UOM_KEY, FACILITY_EQUIP_KEY, data_column

                           

                          Remember, you are working with Oracle.  DATE data types include TIME.

                           

                          Once you UNPIVOT your table, you can calculate "date_column" based on the <column_name> and OPERATING_DATE

                          trunc(operating_date) + (to_number( substr(column_name,6))  - 1)/ 24

                           

                          After you do all of that, then you can use the previously mentioned PIVOT.

                           

                          Oh - you will want to hide the complexity of this SQL with a CREATE VIEW.

                           

                          Reminder - I prefer to define the initial sort order [ORDER BY] in the Default Report.

                           

                          My $0.02

                           

                          MK

                          • 10. Re: Displaying results from dynamic SQL joining multiple tables
                            A Raj

                            Thanks MK. I have no control over the schema as it is already in production . Your ideas are good; I'll keep in mind.

                            • 11. Re: Displaying results from dynamic SQL joining multiple tables
                              Bozzy-Oracle

                              Why not select all the columns and then use a Condition on your columns, you could group them into different values which referencesomething like a select list item, depending on the selection, different columns are rendered.

                               

                              Thanks,

                               

                              Clive.

                              • 12. Re: Displaying results from dynamic SQL joining multiple tables
                                A Raj

                                Thanks for your reply. This scenario may not work for my requirement. I have to do self join on same table to get required rows. E.g. Get 03/20/2017 hour4,hour3,hour2,hour1 + Get 03/19/2017 hour24,hour23,hour22,hour21 from same table.