how to calculate column value dynamically [message #663847] |
Wed, 21 June 2017 06:49 |
|
harishankar_kar
Messages: 22 Registered: July 2014 Location: India
|
Junior Member |
|
|
HI ,
I got some requirement like mentioned below
I have to write a procedure which will take a table name as input .
Now the table will have some key column on that basis i need to group by the data .
e.g
test table is having raw_id col1 col2 col3 col4 col5 flag
if i am getting perfect duplicate for the col1,col2,col3,col4,col5 then
i need to mark one flag as Y and other flags as N .
it means if i am having 3 rows with perfect duplicate then one raw_id will be Y and other 2 raw_id s will be N.
if i am having duplicates for col1 col2 col3 col4 but col5 value is different for 3 columns then i need to
mark one raw_id as Y and other raw_id as F.
I do have an extra config table which will have the tablename ,columns name ,column_name on which i need to check the different values.
config_table will look like below
table_name col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 uniq_col
test col1 col2 col3 col4 col5 col5
test table
raw_id col1 col2 col3 col4 col5 flag
1 A B C D 10 Y
2 A B C D 10 N
3 A B C D 100 F
4 A B C D 20 F
These things we need to handle dynamically .That is either dbms_sql or native dynamic sql ..
|
|
|
|
Re: how to calculate column value dynamically [message #663858 is a reply to message #663847] |
Wed, 21 June 2017 08:15 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Hi,
why shall the final result not be
raw_id col1 col2 col3 col4 col5 flag
1 A B C D 10 F
2 A B C D 10 F
3 A B C D 100 Y
4 A B C D 20 F
? Shall 'Y' be put to the row with lowest RAW_ID?
Anyway, you may find the use of analytic functions, e.g.
row_number() over (partition by col1, col2, col3, col4 order by raw_id)
first_value() over (partition by col1, col2, col3, col4 order by raw_id)
Just examine their output, you shall construct the required conditions easily after understanding them. They are described in SQL Language Reference book, which is available with other Oracle documentation books e.g. online on http://docs.oracle.com/en/database/database.html
I do not see here any use of dynamic SQL as table definition does not change; anyway, you should firstly make it computing correct results statically. Then fancy with dynamic SQL (if ever needed).
|
|
|
Re: how to calculate column value dynamically [message #663859 is a reply to message #663847] |
Wed, 21 June 2017 08:25 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your previous topics:
John Watson wrote on Sun, 06 July 2014 13:49Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
...
BlackSwan wrote on Sun, 06 July 2014 17:15Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
harishankar_kar wrote on Sun, 06 July 2014 22:11...
*BlackSwan made correct {code} tags. Please do so yourself in the future.
http://www.orafaq.com/forum/t/174502/102589/
So once more:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
|
|
|