Skip to Main Content

How to create dynamic SQL query in Oracle

user12251389Jan 21 2022 — edited Jan 21 2022

I have table VW_ID which is having ID column and which is having 1416 rows. I need to create view VW_ID_CUSTOMIZED or may be function such that it will first fetch first 300 rows from VW_ID table and create one row with the below format:

    {"ID": "<here pass actual ID value from VW_ID>","ID": "Is"},
    {"ID": "<here pass actual ID value from VW_ID>","ID": "Is"},
    {"ID": "<here pass actual ID value from VW_ID>","ID": "Is"}

It will then fetch another 300 rows from VW_ID table and create another row with the above format. So basically at the end i will be having only 5 rows in view VW_ID_CUSTOMIZED or may be function. In the above part <here pass actual ID value from VW_ID> i want to replace with ID value from VW_ID and rest it will be hardcoded as it is.
Is it possible to write SQL query and generate View out of it or can i create may be function ?
I tried simple below query which i need to modify with the above logic.

select LISTAGG(ID, ', ') from VW_ID where rownum < 300;

Please find the DB_FIDDLE for DDL and DML script for the table : https://www.db-fiddle.com/f/29RMhJmsJ9CSkaXJULX1ie/0
Below is few part of my DDL and DML:
CREATE Table VW_ID (ID varchar2(50));
INSERT INTO VW_ID (ID) VALUES ('CA0679011084');
INSERT INTO VW_ID (ID) VALUES ('GRR000000010');
INSERT INTO VW_ID (ID) VALUES ('DE000A0JCY11');
INSERT INTO VW_ID (ID) VALUES ('DE000A111ZD6');
INSERT INTO VW_ID (ID) VALUES ('DE0005557508');
INSERT INTO VW_ID (ID) VALUES ('DE0005933931');
INSERT INTO VW_ID (ID) VALUES ('DE000A2LQ3M9');
INSERT INTO VW_ID (ID) VALUES ('GB00BH4HKS39');
INSERT INTO VW_ID (ID) VALUES ('IE00BYZK4552');
INSERT INTO VW_ID (ID) VALUES ('LU0659580079');
INSERT INTO VW_ID (ID) VALUES ('US3696041033');
INSERT INTO VW_ID (ID) VALUES ('IE00B567SW70');
INSERT INTO VW_ID (ID) VALUES ('DE000A1ML7J1');
INSERT INTO VW_ID (ID) VALUES ('ATSTARTUP300');
INSERT INTO VW_ID (ID) VALUES ('CH0267291224');
INSERT INTO VW_ID (ID) VALUES ('DE000A1TNM50');
INSERT INTO VW_ID (ID) VALUES ('CH0019597530');
INSERT INTO VW_ID (ID) VALUES ('LU1376267727');
INSERT INTO VW_ID (ID) VALUES ('DE000CBK1001');
INSERT INTO VW_ID (ID) VALUES ('DE0005491666');
INSERT INTO VW_ID (ID) VALUES ('DE000A2TR919');
INSERT INTO VW_ID (ID) VALUES ('DE0006289382');
INSERT INTO VW_ID (ID) VALUES ('IE00B3VWMM18');
INSERT INTO VW_ID (ID) VALUES ('CA2006977045');
INSERT INTO VW_ID (ID) VALUES ('LU0129463922');
INSERT INTO VW_ID (ID) VALUES ('AT0000776307');
INSERT INTO VW_ID (ID) VALUES ('DE0006231004');
INSERT INTO VW_ID (ID) VALUES ('US72919P2020');
INSERT INTO VW_ID (ID) VALUES ('DE000TLX1005');
INSERT INTO VW_ID (ID) VALUES ('US55087P1049');
INSERT INTO VW_ID (ID) VALUES ('DE0007446007');
INSERT INTO VW_ID (ID) VALUES ('DE0005297204');
INSERT INTO VW_ID (ID) VALUES ('DE000A0D8Q31');
INSERT INTO VW_ID (ID) VALUES ('DE000A0Z2ZZ5');

This post has been answered by BluShadow on Jan 21 2022
Jump to Answer
Comments
Post Details
Added on Jan 21 2022
6 comments
319 views