Archive for the ‘Utility/Tool’ Category

Utility Tool – Column To Query

 

Hi All,

Please have a look at new utility – ColumnToSQL.

It is used to convert data from MS Excel columns to below formats:

  • SQL Query. For example – select * from S_org_ext where row_id IN (‘a’,’b’,’c’,’d’……)
  • Siebel Query. For example – In Last Name column, you may query as “= ‘a’ Or =’b’ OR =’c’ OR …….”

 

Origin Of The Tool

In data migration phase, while loading data for various entities we had fallout records (i.e. Records that failed to load successfully). We needed to re-extract these records. These count of fallout records was generally in hunderds and it used to take huge amount of time to put those Records Ids in the below shown format. Thus I designed ColumnToQuery macro tool.

 

Presentation and User Guide

For detailed insight and user guide, please download Powerpoint presentation here >> ColumnToQuery – Presentation

 

Tool Setup

Please download the setup of ColumnToQuery tool here >> ColumnToQuery – Setup

 

Utility Tool – File Enumerator

 

File Enumerator enlists all the files under a selected directory and its sub-directories in Microsoft Excel sheet. It will include all the standard file details such as file name, author, size, created date, document type, comments etc.

 

Origin Of The Tool

During data migration, it was required to extract all the Opportunity Attachment files from Siebel 7.8 to Siebel 8.1 platform. We extracted all attachment files from Siebel 7.8 file system through Client Side Scripting and stored them on Windows Server.

Now we wanted to confirm successful extraction by comparing the attachment records in tables against the attachment files on the Windows server. So I cam up with File Enumerator tool. Using it, we generated the list of attachment files in excel sheet. Also we took an extract of database table in excel sheet. Finally, we compared the two sheets using vlookup function.

Apart from use at workplace, I have also used this tool to create a consolidated list of –

  • All my music files (MP3)
  • All my video files (movies)

 

Presentation and User Guide

Please download the document here >> FileEnumerator_Presentation

 

Tool Setup

Please download the setup of File Enumerator >> File Enumerator – Setup

Appreciate your feedback:-)

 

Oracle SQL – Date Difference In Days

 

Scenario

It was required to take a full extract of Service Requests from database. But there was a calculated field “Days Open” as follows:

IIf ([Status] = LookupValue(“SR_STATUS”, “Open”), Timestamp() – [Created],[Updated] – [Created])

Now the target was to design an Oracle SQL for calculating number of days a Service Request was open.

 

Solution

select s.row_id
, s.SR_STAT_ID
, To_char(s.created, ‘MM/DD/YYYY HH24:MI:SS’) “Created”
, To_char(s.last_upd, ‘MM/DD/YYYY HH24:MI:SS’) “Last_Updated”
, To_char(sysdate, ‘MM/DD/YYYY HH24:MI:SS’) “Today”
, Case
      When s.SR_STAT_ID = ‘Open’ Then to_char (sysdate – s.created, ‘999999D99’)
      Else to_char (s.last_upd – s.created, ‘999999D99’)
   End “Days Open”
from SIEBEL.S_SRV_REQ s;

Download SQL – Date Difference

Please note that I have utilized To_Char function with an explicit format specification.

 

Oracle SQL – Replace Newline Character

 

Requirement

I had a requirement to take extract of all Contacts from Siebel Database (Oracle Database in this case). I designed the SQL including all necessary columns and took the extract in CSV format.

There were two challenges the I faced:

  • Linebreaks in the phone fields like Work Phone#, Fax Phone#, Home Phone#.
  • Linebreaks in the comments fields.

Due to this reasons, a single contact record was getting scattered to more than two lines in text file. This led to inconsistency.

 

Solution

Replace Function was used to resolve it. The syntax is:

replace( string1, string_to_replace, [ replacement_string ] )

where, string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced by it in string1.

Following SQL was used:

select con.row_id
, Con.FAX_PH_NUM
, REPLACE(Con.FAX_PH_NUM, CHR(10), ‘ ‘) as “Updated_Fax”
, Con.COMMENTS
, REPLACE(Con.COMMENTS, CHR(13) || CHR(10), ‘ ‘) as “Updated_Comments”
from siebel.S_contact con;

 

Concept

Line Feed (LF) character (\n) has 10 as ASCII code, whereas Carriage Return (CR) character (\r) has 13 as ASCII code.

In Unix system, newline character is LF. But in Microsoft Windows system, CR+LF represents the newline character.

 

Utility Tool – SQL Bin v3.3

 

Let me present to you SQL Bin v3.3!!

It is an Excel-based utility to store all SQL Statements at one place for quick and easy reference.

The major features of this tool are:

  • Version control
  • User-friendly interface

 

Origin Of The Tool

I work on weekly dashboards. For this, I have to take out several extracts from the database and in result, I have to work with a number of SQL statements. Previously, I had to keep all these SQL in text/notepad files in unorganized fashion. Also it was time consuming to search for any specific SQL.

At that point, I came up with the idea of SQL Bin tool to organize all my SQL statements in organized and time saving fashion.

 

Presentation/Slide Show

Please download presentation on SQL Bin – SQLBin_Presentation

 

Tool Setup

Please download the setup of SQL Bin – SQL Bin v3.3 – Setup

Appreciate your feedback upon usage.