-
Create Column Names dynamically
Hello,
I have the following task that I am having problems with.
1. THIS IS DONE - Check for distinct MODEL_NAME value in TABLE 1 and see what MODEL_NAME value is missing in TABLE 2 (could be multiple).
Here is SQL - THIS WORKS
Code:
SELECT DISTINCT a.device_type
FROM logrelay.ra_device_type_hist@PRLDW a
WHERE a.device_type is not null
MINUS
SELECT B.DEVICE_TYPE
FROM rkiss.device_models b
2. THIS IS DONE - Stored Prodecure that inserts new values into TABLE 2 with the following data, (id, dte, device_type) - this works as well.
3, HERE IS MY PROBLEM.
I have TABLE 3 where DEVICE_TYPE from TABLE 2 are column names. Currently I add column names manually, but would like to automate this process.
I would like to create a process that would check any new DEVICE_TYPE values in TABLE 2 that does not exist as column names in TABLE 3. If new DEVICE_TYPE value exists in TABLE 2, create new column name (as value of the new DEVICE_TYPE in TABLE 2) in TABLE 3.
I do not how to code this.
Any ideas?
Thanks
Roman
-
Try this:
Code:
Begin
For X In (Select Device_Type
From Rkiss.Device_Models
Minus
Select Column_Name
From All_Tab_Columne
Where Owner='RKISS'
And Table_Name='TABLE3')
Loop
Execute Immediate 'Alter Table Table3 Add('
||X.Device_Type||' Varchar2(10))';
End Loop;
End;
/
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
I will give it a try.
Thanks
Roman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|