Hi Quanwen,
First of all, Q-quoting is for literals, not for object names:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html
Secondly, you code on pro_convert_rows_to_columns_3.png has syntax errors:
linex #14-19 should be like this:
v_sql := v_sql || ', '
|| 'MAX(DECODE(sample_time, '''
|| v_samp_time.sample_time
|| ''', value)) as '
|| '"'
|| v_samp_time.sample_time
|| '"';
ie '"' - " (double quotes) between '...' (single quote).
But it would be even easier if you add " in your cursor for generating
sample times:
cursor cur_samp_time is
select sample_time, *'"'||sample_time||'"' as sample_alias*
from ....
v_sql := v_sql || ', '
|| 'MAX(DECODE(sample_time, '''
|| v_samp_time.sample_time
|| ''', value)) as '
|| v_samp_time.*sample_alias*
PS. it would be better if you use gist.github.com or pastebin to post your
code/output/errors instead of screenshots.
On Thu, Nov 18, 2021 at 12:32 PM Quanwen Zhao <quanwenzhao@xxxxxxxxx> wrote:
Hello all 😊,
Recently I encountered a very weird issue when writing a PL/SQL procedure,
here are some screenshots from the following links:
*https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_1.png*
<https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_1.png>
*https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_2.png*
<https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_2.png>
*https://quanwenzhao.files.wordpress.com/2021/11/test_demo.png*
<https://quanwenzhao.files.wordpress.com/2021/11/test_demo.png>
*https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_3.png*
<https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_3.png>
*https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_4.png*
<https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_4.png>
Yes, I know a column alias must use a double quotes for special character
because my case is the character about TIME (just including hour and
minute) like this, "10:30".
Could you help me troubleshooting it? Thanks beforehand! 🙏
Best Regards
Quanwen Zhao
