SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Missing expression Error

702665May 29 2009 — edited May 29 2009

Hi all,
I'm using big dynamic sql query inside my package. When I compile code , it does successfully. During run time, it throws an 'Missing expression '
I usually use SQL DEVELOPER for debugging.

Message: ORA-00936: missing expression
ORA-06512: at "HDOT.EDR_RPT_SPEED_BY_HOUR_PACKAGE", line 761
ORA-06512: at line 1

As its dynamic sql I find hard to figure out where the exception occurs.How could I figure out that.

my_report_data_statement :=
       'SELECT "Rank", '
    || '       "Row Type", '
    || '       edr_rpt_tmp_grouping_table.interval_start_date_time "Date", '
    || '       TO_CHAR(results."Date/Time", ''hh24:mi:ss.ff''), '
    || '       TO_CHAR(results."Date/Time", ''hh24'') "Chart X-Axis", '
    || '       ''-1'' "Group Id" , '
 	|| '       ''None'' "Group Name", '
    || '       "Hour" '
    ||         my_speed_sum_text || ', '
    || '       SUM(" ") "Total" '
    || '  FROM ( '
    || '         SELECT 3 "Rank", '
    || '                ''Final Row'' "Row Type", '
    || '                TRUNC(edr_rpt_tmp_bin_periods.bin_start_date_time ) "Date", '
    || '                MAX(edr_rpt_tmp_bin_periods.bin_end_date_time) - numtodsinterval(0.000000001, ''SECOND'') "Date/Time", '    
    || '                ''Total'' "Hour"  '
    ||                  my_speed_sum_text || ' , '
    || '                SUM(NVL(" ", 0)) " " '
    || '           FROM ( '
    || '                  SELECT edr_speed_by_hour_report_data.bin_start_date_time start_date_time '
    ||                           my_speed_select_text || ', '
    || '                         SUM(NVL(edr_speed_by_hour_report_data.speed_count, 0)) " " '
    || '                    FROM edr_speed_by_hour_report_data '
    || '                   GROUP BY edr_speed_by_hour_report_data.bin_start_date_time '
    || '                ) results '
    || '          RIGHT OUTER JOIN edr_rpt_tmp_bin_periods '
    || '             ON results.start_date_time >= edr_rpt_tmp_bin_periods.bin_start_date_time '
    || '            AND results.start_date_time <  edr_rpt_tmp_bin_periods.bin_end_date_time '
    || '          GROUP BY TRUNC(edr_rpt_tmp_bin_periods.bin_start_date_time ) '
    || '          UNION ALL '
    || '         SELECT 2 "Rank", '
    || '                ''Summary Row'' "Row Type", '
    || '                TRUNC(edr_rpt_tmp_bin_periods.bin_start_date_time ) "Date", '
    || '                MAX(edr_rpt_tmp_bin_periods.bin_end_date_time) - numtodsinterval(0.00000001, ''SECOND'') "Date/Time", '  
    || '                '''' "Hour"  '
    ||                  my_speed_sum_text || ', '
    || '                SUM(NVL(" ", 0)) " " '
    || '           FROM ( '
    || '                  SELECT edr_speed_by_hour_report_data.bin_start_date_time start_date_time '
    ||                           my_speed_select_text || ', '
    || '                         SUM(NVL(edr_speed_by_hour_report_data.speed_count, 0)) " " '
    || '                    FROM edr_speed_by_hour_report_data '
    || '                   GROUP BY edr_speed_by_hour_report_data.bin_start_date_time '
    || '                ) results '
    || '          RIGHT OUTER JOIN edr_rpt_tmp_bin_periods '
    || '             ON results.start_date_time >= edr_rpt_tmp_bin_periods.bin_start_date_time '
    || '            AND results.start_date_time <  edr_rpt_tmp_bin_periods.bin_end_date_time '
    || '          GROUP BY TRUNC(edr_rpt_tmp_bin_periods.bin_start_date_time ), '
    || '                CASE '
    || '                  WHEN CAST(TO_CHAR(edr_rpt_tmp_bin_periods.bin_start_date_time, ''hh24'') AS NUMBER) < 6  THEN ''00-06'' '
    || '                  WHEN CAST(TO_CHAR(edr_rpt_tmp_bin_periods.bin_start_date_time, ''hh24'') AS NUMBER) < 12 THEN ''06-12'' '
    || '                  WHEN CAST(TO_CHAR(edr_rpt_tmp_bin_periods.bin_start_date_time, ''hh24'') AS NUMBER) < 18 THEN ''12-18'' '
    || '                  WHEN CAST(TO_CHAR(edr_rpt_tmp_bin_periods.bin_start_date_time, ''hh24'') AS NUMBER) < 24 THEN ''18-24'' '
    || '                END '
    || '          UNION ALL '
    || '         SELECT 1 "Rank", '
    || '                ''Data Row'' "Row Type", '
    || '                TRUNC(edr_rpt_tmp_bin_periods.bin_start_date_time ) "Date", '
    || '                edr_rpt_tmp_bin_periods.bin_start_date_time "Date/Time", '     
    || '                TO_CHAR(edr_rpt_tmp_bin_periods.bin_start_date_time, ''hh24'') '
    || '                || '' - '' || '
    || '                DECODE(TO_CHAR(edr_rpt_tmp_bin_periods.bin_end_date_time, ''hh24''), ''00'', ''24'', TO_CHAR(edr_rpt_tmp_bin_periods.bin_end_date_time, ''hh24'')) "Hour" '
    ||                  my_speed_sum_text || ', '
    || '                SUM(NVL(" ", 0)) "Total" '
    || '           FROM ( '
    || '                  SELECT edr_speed_by_hour_report_data.bin_start_date_time start_date_time '
    ||                           my_speed_select_text || ', '
    || '                         SUM(NVL(edr_speed_by_hour_report_data.speed_count, 0)) " " '
    || '                    FROM edr_speed_by_hour_report_data '
    || '                   GROUP BY edr_speed_by_hour_report_data.bin_start_date_time '
    || '                ) results '
    || '          RIGHT OUTER JOIN edr_rpt_tmp_bin_periods '
    || '             ON results.start_date_time >= edr_rpt_tmp_bin_periods.bin_start_date_time '
    || '            AND results.start_date_time <  edr_rpt_tmp_bin_periods.bin_end_date_time '
    || '          GROUP BY edr_rpt_tmp_bin_periods.bin_start_date_time, '
    || '                edr_rpt_tmp_bin_periods.bin_end_date_time '
    || '          ORDER BY "Date/Time" ASC, '
    || '                "Rank" ASC '
    || ' ) results '
    || ' LEFT OUTER JOIN edr_rpt_tmp_grouping_table '
    || '   ON results."Date/Time" >= edr_rpt_tmp_grouping_table.interval_start_date_time '
    || '  AND results."Date/Time" <  edr_rpt_tmp_grouping_table.interval_end_date_time '
    || 'GROUP BY edr_rpt_tmp_grouping_table.interval_start_date_time, '
    || '      edr_rpt_tmp_grouping_table.interval_end_date_time, '
    || '      TO_CHAR(results."Date/Time", ''hh24:mi:ss.ff''), '
    || '      "Rank", '
    || '      "Hour", '
    || '      TO_CHAR(results."Date/Time", ''hh24''), '
    || '      "Row Type" '
    || 'ORDER BY edr_rpt_tmp_grouping_table.interval_start_date_time ';

In this I'm using two functions to generate sql strings 'my_speed_sum_text' and 'my_speed_select_text'.
When I debugg those functions it does generate the sql string correctly. I will post those 2 functions in a separate thread .
Could anyone help me how figure out that missing expression

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 26 2009

Added on May 29 2009

2 comments

1,053 views