Home » SQL & PL/SQL » SQL & PL/SQL » how to calculate column value dynamically (oracle 11g 11.2.0.4.0 release )
how to calculate column value dynamically [message #663847] Wed, 21 June 2017 06:49 Go to next message
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 #663848 is a reply to message #663847] Wed, 21 June 2017 06:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: how to calculate column value dynamically [message #663858 is a reply to message #663847] Wed, 21 June 2017 08:15 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topics:

John Watson wrote on Sun, 06 July 2014 13:49
Welcome 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:15
Please 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.

Previous Topic: Sending List to Oracle Stored Procedure
Next Topic: PRMDB-0933-1
Goto Forum:
  


Current Time: Thu Mar 28 19:48:08 CDT 2024