Excel vLookup vs SQL Join

This will sound rather obvious to Excel wizards. We had a problem with a very complex report that temporarily needed some extra details added. Rather than create a copy of the report we added some extra data to it via Excel. We extracted the extra data via SQL and then joined the two outputs via Excel using vLookup. An example of how to do this is here. The index match lookup method is recomended for very large spreadsheets.


An alternative would have been to pull the data back into the database using Cgywin via a temporary table. This would allow us to do a join via SQL in the usual fashion.

grep 'AP Invoices' ./ap_bal.csv > ap_edited.csv

Create Table Analysis (Transaction_Type Varchar2(130), Transaction_Number Varchar2(20)…………. );

sed 's+^+Insert Into Analysis (Transaction_Type, Transaction_Number) Values (+' ap2.csv > ap3.txt sed 's/$/,)/' ap3.txt > ap4.txt

Clean up and load with SQL Developer

Oracle EBS Development and Extensibility Handbook

If you are starting to work on Oracle EBS applications this book is the best place to start. I wish I had got hold of a copy sooner.

By the same author is Oracle Fusion Applications Development and Extensibility Handbook (Oracle Press).

Searching and reviewing the SVN repository

I wanted a way to search a subversion repository for change references. I'm using cygwin to run it from Windows. Useful if you are not sure where a change has been made and do not want to create and search a local replica.



To view deletions and modifications of files try the following:

svn log https://vmsscdtconfmgt/SSC_Releases -v --limit 300 | grep 'D /' > svn_del_add.txt

svn log https://vmsscdtconfmgt/SSC_Releases -v --limit 300 | grep 'M /' >> svn_del_add.txt