Working with customer identifiers

In the Smile API and user interface, accounts and subscriptions are generally identified by a number called the USN, which is a text value. But in SQL, we use a generic numeric value called the UID (universal identifier) to refer to accounts and subscriptions.

The UID for accounts and related tables is provided in a column called “account”; the UID for subscriptions and related tables is in a column called “subscription”.

usn(uid) accepts a UID (account or subscription ID) and converts it into a USN.

For example, to list the USN and account balance of all users in Smile, you could use the following SQL:

SELECT usn(account), balance FROM account_balance WHERE balance > 0;

     usn    |   balance
------------+------------
 2142420815 |    1428.00
 2142420872 |     900.00
 2142420898 |     900.00
 2142420930 |     900.00
 2142420955 |     900.00

uid(usn) accepts a USN and converts it to an account or subscriber ID (uid).

To find the balance of an account given it’s USN, you would use the following query:

 balance
---------
 1428.00