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

You say you know that the column alias should be in double quotes, e.g. as “10:30” but none of your examples provides code which actually produces this as an end result.

e.g.
create view v1 as
select 1 as 10:30 from dual; --wrong

vs

create view v1 as
select 1 as '10:30 ' from dual; --wrong

vs

create view v1 as
select 1 as "10:30" from dual; --right

I.e. in your pro_convert_rows_to_columns proc, you need the concatenation of something like:

|| '"'||v_samp_time.sample_time|| '" '

(single quote then double quote then single quote)

Regards,
Dominic

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows

From: Quanwen Zhao<mailto:quanwenzhao_at_gmail.com> Sent: 18 November 2021 09:32
To: ORACLE-L<mailto:oracle-l_at_freelists.org> Subject: How to concat dynamic column alias using || on oracle PL/SQL?

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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fquanwenzhao.files.wordpress.com%2F2021%2F11%2Fpro_convert_rows_to_columns_1.png&data=04%7C01%7C%7Ce7b2f257070d4285dece08d9aa764ca8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637728247406148912%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=qLr7IN0X1SHPxbSjgfw%2Fil4rJg%2BTxGu5FsAeldLXJ0Q%3D&reserved=0>

https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_2.png<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fquanwenzhao.files.wordpress.com%2F2021%2F11%2Fpro_convert_rows_to_columns_2.png&data=04%7C01%7C%7Ce7b2f257070d4285dece08d9aa764ca8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637728247406158866%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=hAlVUMkaiqZiWbjk8iRxMQXcmZJz8R52QCtSqQifFyo%3D&reserved=0>

https://quanwenzhao.files.wordpress.com/2021/11/test_demo.png<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fquanwenzhao.files.wordpress.com%2F2021%2F11%2Ftest_demo.png&data=04%7C01%7C%7Ce7b2f257070d4285dece08d9aa764ca8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637728247406168817%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=Z3bGPggy14eyW2oKyQhMYMArCi5rVhscDojODvRc8vE%3D&reserved=0>

https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_3.png<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fquanwenzhao.files.wordpress.com%2F2021%2F11%2Fpro_convert_rows_to_columns_3.png&data=04%7C01%7C%7Ce7b2f257070d4285dece08d9aa764ca8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637728247406168817%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=ALAOK4U1XfsuZA1IcroglmPGvBXIaDYsoUZoH3iqsn4%3D&reserved=0>

https://quanwenzhao.files.wordpress.com/2021/11/pro_convert_rows_to_columns_4.png<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fquanwenzhao.files.wordpress.com%2F2021%2F11%2Fpro_convert_rows_to_columns_4.png&data=04%7C01%7C%7Ce7b2f257070d4285dece08d9aa764ca8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637728247406178777%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=feKdTR55p5KqczEDhdowlOCrh2dU54WC6ITHm7NaKzo%3D&reserved=0>

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

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