Structured Query Language (SQL)

This page contains a selection of SQL code that I have found useful. It contains some basic syntax as well as some advanced SQL/Oracle functions that can be very useful in the Campaign Management space. I have used different flavours of SQL so you may find a combination of both SQL server and Oracle examples below.


Create A Table

Create Table Schema_Name.Table_Name  (Field_Name   Number(15));


Alter Table And Add A Primary Key To A Field

Alter Table Schema_Name.Table_Name
Add Primary Key (Field_Name);

Note: Incidentally this will allow you to link to the table in an access table and be able to delete/append/update records directly in the linked table


Alter Table And Add A Primary Key Across 2 Fields

Alter Table  Schema_Name.Table_Name
Add Primary Key (Field_Name1, Field_Name2)


Merge Statement

In the example below we are counting the number of ‘active’ accounts and updating this count to a field called count_accts.

Merge Into Schema_Name.Table_Name Z
Using (Select A.Field_Name1, Count(B.Field_Name1) As Totaccts From Schema_Name.Table_Name A, Schema_Name.Table_Name2 B
Where A.Field_Name1 = B.Field_Name1
and B.Field_Name2 = ‘Active’
Group By Field_Name1) Subquery
On (Z.Field_Name1 = Subquery.Field_Name1)
When Matched Then
Update Set Z.Count_Accts = Subquery.Totaccts;


Rank() Statement

ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>) 
RANK() OVER ([<partition_by_clause>] <order_by_clause>) 
DENSE_RANK() OVER ([<partition_by_clause>] <order_by_clause>)

The following code will select just one account per customer, in this case the highest acct_id

Create Table Schema_Name.Tablename As (Select Cust_ID, Acct_ID,Post_ID From
(Select A.Cust_ID, A.Acct_ID,A.Post_ID, Row_Number() Over (Order By A.Acct_ID Desc) Acct_Rank
From Schema_Name.Table_Name A, Schema_Name.Table_Name2 B
Where A.Cust_ID = B.Cust_ID)
Where Acct_Rank = 1);

The following code is a bit more involved. It compares 2 tables:

Customers_To_Rank

  • Cust_ID – key for customer id
  • Acct_ID – key for each account the customer holds
  • Priority – score of importance for this particular lead
  • Propensity_Val – likelihood a customer will respond to this particular lead
  • Contact_Name – End-user ultimately responsible for actioning the lead

against – Contacts_And_Caps

  • Contact_ID – ID for the relationship manager
  • Contact_Name – Name of relationship manager
  • Lead_Cap – maximum amount of leads for that relationship manager

The code finds the maximum available number of leads that each relationship manager can deal with, then it ranks leads by a combination of priority and propensity and caps them by the relationship manager cap. The table is updated with the leads that need to be actioned (Select_Cust flag updated to true).

Update Customers_To_Rank a
Set a.Select_Cust = 1
Where Acct_ID in (
Select Acct_ID FROM (
Select A.Acct_ID, A.Cust_ID, A.Lead, A.Priority, A.Contact_ID,
Row_Number () Over (Partition By Contact_Name Order By Priority Asc, Propensity_Val Desc) Contact_Rank
From Customers_To_Rank A) SubQuery,
(Select Contact_ID, Max(Lead_Cap) As Lead_Cap
From Contacts_And_Caps Group By Contact_ID) Cap
Where SubQuery.Contact_Rank <= Cap.Lead_Cap
And SubQuery.Contact_ID = Cap.Contact_ID);

For a nice explanation on the differences between the different types of ranks you could view the following blog – Link (Opens in a new Tab)


Case Statements

Could be used to create a table of all customers and update a flag for every product that they have.

I use case statements when I create the data for the Data Quality Reports Tool (Click here to view in a new Tab).

Notice the (+) this means that we are doing an Outer-Join to the table. If the customer wasn’t in the table we assign an ‘n’ flag otherwise we give them a ‘y’… it is like an if statement.

This logic could also be used to create a single table of all communications history.

Merge Into Schema_Name.Table_Name J
Using (Select J.Customer_Id,
Case When Bs341.A1 Is Null Then ‘N’ Else ‘Y’ End As “Homeins”,
Case When Bs342.A1 Is Null Then ‘N’ Else ‘Y’ End As “Carins”,
Case When Bs58.A1 Is Null Then ‘N’ Else ‘Y’ End As “Mortgage”
From Schema_Name.Table_Name J,
Schema_Name.Bs341,
Schema_Name.Bs342,
Schema_Name.Bs58
Where Bs341.A1(+) = J.Customer_Id
And Bs342.A1(+) = J.Customer_Id
And Bs58.A1(+) = J.Customer_Id ) SubQuery
On (J.Customer_Id = SubQuery.Customer_Id)
When Matched Then
Update Set J.Homeins = SubQuery.Homeins,
J.Carins = SubQuery.Carins,
J.Mortgage = SubQuery.Mortgage;


Calculate Statistics

In order for the database to function efficiently it needs to know high level information about tables, their relative size, etc. In order to gather statistics you would run the following.

Exec Dbms_Stats.Gather_Table_Stats(‘Schema_Name’, ‘Table_Name’, Cascade => True) ;

Alternatively, ask your database administrator (dba) if they are doing this for you already.


DROP Command

If you are using a table as a one-off don’t forget to drop it afterwards and then use the purge command to free-up the space that your table was taking.

When you drop a table, it has only been renamed. The table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace.

It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by purging it.

Drop Table Schema_Name.Table_Name Purge;


Purge Your Recycle Bin To Free-Up The Space Of The Tables

Purge Recyclebin


Deduping your table in SQL (Oracle)

Rather Than Selecting Deduped Records From A Table, You Can Remove The Dupes From The Table Outright.

Try The Sql Below For A Quick Efficient Way.

Delete From <Tablename>
Where Rowid In ( Select Rowid
From ( Select A.*,
Row_Number() Over ( Partition By <Dedupe_Field> Order By <Order_Field> ) Rn
From <Tablename> A)
Where Rn <> 1);

Replace <Tablename> With The Table That You Want Deduped
Replace <Dedupe_Field> With The Field That Has Duplicates
Replace <Order_Field> With The Field That Would Help You Prioritise The Best Field To Dedupe By (If It Doesn’t Matter, Just Put Any Field)


Deduping your table in SQL (SQL Server)

WITH CTE  AS(
Select <Dedupe_Field>,
RN = Row_Number() Over ( Partition By <Dedupe_Field> Order By <Order_Field>)
From <Tablename>
)
DELETE FROM CTE  WHERE RN > 1

Source: Tim Schmelter (Stackoverflow)


Indexes

Create Index <Index Name> On <Table Name> (<Column Name>)

Note: An index has to have a unique name, and that is a unique name to the schema, not simply the table.

An index often takes up a lot of space so what do you do when you want to get rid of that pesky index?

Drop Index <Index Name>

The composite index is also a useful creature. For example you want to condition on field_name1 as well as field_name2, you can.

Create Index Index_Name On Schema_Name.Table_Name (Field_Name1, Field_Name2)

This will make for quicker queries than an index on each field individually.

Quick TipOne final word on the index: they make select queries run more quickly, but they make update queries slower. If you have an index on a table you plan to update or append: drop the index, do your updating and then recreate the index.

 


Finding which SQL views have been created against a table or Schema

The Following Query Will Show Results For All Views Against Tables In The Schema_Name Schema

Select *
From   User_Dependencies
Where Type = ‘View’
And   Referenced_Owner = ‘Schema_Name’

The Following Query Will Show Results For All Views Against The Table_Name Table

Select *
From   User_Dependencies
Where Type = ‘View’
And   Referenced_Name = ‘Table_Name’


Transferring Proc SQL to the Advanced tab in IBM (Unica) Campaign

If you want to transfer a piece of SQL to Unica do the following.

  1. Create staging tables in advance (you can’t use DDL SQL in proc sql – so no “create table”, etc.)
  2. Test and run SQL in a session in Toad, SQL+ or SSMS
  3. In order to change your SQL to proc sql prefix the code with “begin ” and finish it with “commit; end;”
  4. Copy and paste your full SQL code into Microsoft Word and do the following find and replace.

=> Note: Keep Doing Each Of The Following Steps Until You Don’t Get Any More Replacements

4.1 Replace <Tab> With <Space> — Do This By Copying A Tab That You Type Yourself
4.2 Replace ^P With <Space> — The ^ Comes From Shift + 6
4.3 Replace <Space><Space> With <Space>

Some Sample Code That Will Work Is:

Begin
Delete From Schema_Name.Table_Name;
Commit;
Insert Into Schema_Name.Table_Name (Field_Name) Values (‘00000000000000985662091’);
Commit;
Insert Into Schema_Name.Table_Name (Field_Name) Values (‘00000000000000965362702’);
Commit;
End;

After the find/replace steps above you should end up with SQL that doesn’t have any carriage returns.

Copy and paste this into the advanced box in Unica.

E.G. Your final code should look like this:

Begin Delete From Schema_Name.Table_Name; Commit; Insert Into Schema_Name.Table_Name (Fieldname) Values (‘00000000000000985662091’); Commit; Insert Into Schema_Name.Table_Name (Fieldname) Values (‘00000000000000965362702’); Commit; End;


How to remove Carriage Returns or Line Feeds from a table value

Select Replace (Replace (Field_Name, Chr (13), ”), Chr (10), ”) As Fixed_Field From…


 Find Large Tables

Select  Table_Name, Tablespace_Name, Num_Rows
From All_Tables
Where Owner = ‘Owner’ And Num_Rows > 1000000
Order By Num_Rows Desc;


 Find tables related to a certain campaign and when they were last analysed

Select Table_Name, Tablespace_Name, Last_Analyzed
From All_Tables
Where Table_Name Like ‘%Searchstring%’ And Owner = ‘Owner’


 Available, used and free tablespace

Select A.Tablespace_Name, A.Bytes Total, B.Bytes Used, C.Bytes Free,
(B.Bytes * 100) / A.Bytes “% Used”, (C.Bytes * 100) / A.Bytes “% Free”
From Sys.Sm$Ts_Avail A, Sys.Sm$Ts_Used B, Sys.Sm$Ts_Free C
Where A.Tablespace_Name = B.Tablespace_Name
And A.Tablespace_Name = C.Tablespace_Name;


Finding Useful Tables/Views In A Large Database

  • All_Tables – list of all tables in the current database that are accessible to the current user
  • All_Views – list of all views in the current database that are accessible to the current user
  • All_Tab_Columns – list of all columns in the current database that are accessible to the current user

Select Owner, View_Name From All_Views Where View_Name Like ‘Searchstring%’

All_Tab_Cols: As the name suggests this is a list of all tables/views and all columns within those tables/views.

You will find that this is particularly useful if you know which Field_Name you are looking for.

Select Owner, Table_Name From All_Tab_Cols Where Column_Name Like ‘%Group%’ And Table_Name Like ‘%Cust%’
Intersect
Select Owner, Table_Name From All_Tab_Cols Where Column_Name Like ‘%Id%’ And Table_Name Like ‘%Cust%’

This query helps me find tables/views that have field_names including ‘Group’ and ‘ID’, and limit just to tables/views containing the word ‘CUST’

I have used intersect because each column name is on a new row… So if you are looking for multiple fields in a single table you need to specify the criteria in 2 separate queries and then join the results.


To view the page about Unix Code and Scripts click here.