<div>
Let me guess: &nbsp;You want this dynamic from the rules from CPS_CLAIM_MAPPING and not just a hard-coded select?
</div>


<div>
Yes, I would need dynamic script as the mappings table might change in the future.<br />
<br />
Thanks.
</div>


<div>
Many thanks for the response.<br />
<br />
The query executes fine but the results are wrong.<br />
All the data mentioned in the spreadsheet are correct but the results shows like violated.<br />
<br />
If you see the following results, all the V5_ columns are populated with V8_ column value adding 800000000. &nbsp;So I expect the results everything is correct and no violation.<br />
<br />
V8_ID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V5_ID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_CLAIMID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V5_CLAIMID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_POLICYID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V5_POLICYID<br />
10000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;800010000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12345 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;800012345 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;57000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;800057000<br />
20000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;800020000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;90800 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;800090800 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;60000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;800060000<br />
30000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;800030000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;67000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;800067000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;77000 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;800077000<br />
<br />
<br />
But I am getting the result as,<br />
<br />
column V5_ID rowid AAA9ulAABAAD+2HAAD violated rule 100<br />
column V5_CLAIMID rowid AAA9ulAABAAD+2HAAD violated rule 101<br />
column V5_POLICYID rowid AAA9ulAABAAD+2HAAD violated rule 102
</div>


<div>
I do not understand what results you want.<br />
<br />
I thought you would want to know what rows and columns violated the rules.<br />
<br />
Yes, I saw all the rows provided in the spreadsheet were good so I added a 'bad' one so my code showed some output.<br />
<br />
Based on the data in my test case, are you saying you only want to return 'good' rows and don't want to know there is bad data in the table?
</div>


<div>
Apologies for not clear with my query previously. The requirements were slightly changed and please see below.<br />
<br />
For all the tables in a schema, I am trying to identify if V8_ column is populated, <br />
then its corresponding V5_ column also populated for each date (VALID_FROM date column) by checking their count. <br />
<br />
I don't need to worry about what value is populated in V5_ columns and only need to ensure that V5_ column is populated with some value.<br />
<br />
Table structure:<br />
<br />
CLAIM:<br />
VALID_FROM DATE<br />
V8_CLAIMID NUMBER<br />
V5_CLAIMID NUMBER<br />
V8_STATEID NUMBER<br />
V5_STATEID NUMBER<br />
V8_POLICYID NUMBER<br />
V5_POLICYID NUMBER<br />
<br />
The mappings is defined for all the tables like - CPS_CLAIM_MAPPING table:<br />
<br />
TARGET_TABLE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_COLUMN_LIST &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V5_COLUMN_LIST<br />
CLAIM &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_CLAIMID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; V5_CLAIMID<br />
CLAIM &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_STATEID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; V5_STATEID<br />
CLAIM &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_POLICYID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; V5_POLICYID<br />
LOAN &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_LOANID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; V5_LOANID<br />
LOAN &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_CUSTD &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; V5_CUSTID<br />
<br />
The expected result is as follows:<br />
<br />
CLAIM:<br />
<br />
VALID_FROM &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_CLAIMID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V5_CLAIMID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_STATEID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V5_STATEID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_POLICYID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V5_POLICYID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
01/04/2016 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;102 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102<br />
02/04/2016 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;80 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 80 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80<br />
03/04/2016 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;23 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;19 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 19 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 23 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;19<br />
<br />
LOAN:<br />
<br />
VALID_FROM &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_LOANID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V5_LOANID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V8_CUSTID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V5_CUSTID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
01/04/2016 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;15 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
02/04/2016 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;25 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 25 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;25 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
03/04/2016 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;35 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 35 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;30<br />
<br />
<br />
Also, both the V8 &amp;&nbsp;V5 columns needs to be verified against data dictionary (all_tab_cols) before producing the result, as there are some columns created incorrectly which are not exist in actual table. If either of column is not exist, then it can be excluded from the result.
</div>


<div>
I have accepted your solution and many thanks for speedy response.
</div>


<div>
The question is still open.
</div>


<div>
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.<br />
<br />
I have recommended this question be closed as follows:<br />
<br />
Split: <br />
-- slightwv (<a href="https:#a41531887" rel="ugc">https:#a41531887</a>)<br />
-- slightwv (<a href="https:#a41534885" rel="ugc">https:#a41534885</a>)<br />
<br />
<br />
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.<br />
<br />
sjwales<br />
Experts-Exchange Cleanup Volunteer
</div>


<div>
<div class="content wysiwyg-content">
Hi<br />
<br />
I want to compare columns on a same table (CPS_CLAIM) and the column mappings are configured in the mapping table (CPS_CLAIM_MAPPING).<br />
<br />
Most of the V5_ columns are populated using the rule defined in the above mapping table (E.g. V5_ID = V8_ID + 800000000) and few columns are not (E.g. V5_RESID, V5_DESC). Please see spreadsheet attached.<br />
<br />
Now I want to write a SQL/PLSQL script to verify all the mappings are correctly executed by comparing the V5_ column values against the V8_ column values (Subracting 800000000 from V5_ column value will give you the V8_ value).<br />
<br />
Note: I need to verify only the columns defined in the mapping table and no need to worry about other columns (E.g. V5_RESID, V5_DESC) and need to execute the script for all the tables in a schema.<br />
<br />
Please let me know if my requirement is not clear and any help would be much appreciated.<br />
<br />
Thanks.<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/04_w14/1089463/cps_claim.xls" target="_blank" class="file-inline" title="cps_claim.xls (20 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>cps_claim.xls</a>
</div>
</div>


cps_claim.xls

Hi

I want to compare columns on a same table (CPS_CLAIM) and the column mappings are configured in the mapping table (CPS_CLAIM_MAPPING).

Most of the V5_ columns are populated using the rule defined in the above mapping table (E.g. V5_ID = V8_ID + 800000000) and few columns are not (E.g. V5_RESID, V5_DESC). Please see spreadsheet attached.

Now I want to write a SQL/PLSQL script to verify all the mappings are correctly executed by comparing the V5_ column values against the V8_ column values (Subracting 800000000 from V5_ column value will give you the V8_ value).

Note: I need to verify only the columns defined in the mapping table and no need to worry about other columns (E.g. V5_RESID, V5_DESC) and need to execute the script for all the tables in a schema.

Please let me know if my requirement is not clear and any help would be much appreciated.

Thanks.

Compare columns on a same table

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.