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)
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;
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:
- 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)
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,
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;
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.
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
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(
RN = Row_Number() Over ( Partition By <Dedupe_Field> Order By <Order_Field>)
DELETE FROM CTE WHERE RN > 1
Source: Tim Schmelter (Stackoverflow)
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.
One 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
Where Type = ‘View’
And Referenced_Owner = ‘Schema_Name’
The Following Query Will Show Results For All Views Against The Table_Name Table
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.
- Create staging tables in advance (you can’t use DDL SQL in proc sql – so no “create table”, etc.)
- Test and run SQL in a session in Toad, SQL+ or SSMS
- In order to change your SQL to proc sql prefix the code with “begin ” and finish it with “commit; end;”
- 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:
Delete From Schema_Name.Table_Name;
Insert Into Schema_Name.Table_Name (Field_Name) Values (‘00000000000000985662091’);
Insert Into Schema_Name.Table_Name (Field_Name) Values (‘00000000000000965362702’);
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
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
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%’
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.