Re: How to concat dynamic column alias using || on oracle PL/SQL?

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_at_gmail.com> 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
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l