Query the database

To pull your data from the database, run a query in a SQL worksheet. You can edit a sample query of an existing GTD Cloud report or write your own query.

Transactional data in GTD Cloud is stored in the Lines by Jurisdiction (LBJ) table, referred to as lbj_share_view_with_policy in Snowflake. The fields in this table are explained in the LBJ data dictionary.

There are 363 fields in the Lines by Jurisdiction table, not all of which are relevant to your organization. Read the field descriptions in the data dictionary to make sure that you are pulling the data you need.

Edit sample queries of Sovos reports

To customize an existing GTD Cloud report, edit the sample query to change the filters, fields, or layout.

Download a ZIP file containing queries for the following GTD Cloud reports:

  • Simplified Transaction Detail Report
  • Transaction Detail Report by Line Item
  • Transaction Detail Report by Line Item and Taxing Jurisdiction

To query the database using a sample query, follow these steps:

  1. Go to Projects on the main menu and select Worksheets. On the main screen, click the down arrow, then select SQL Worksheet.
  2. Make sure that your user role is either SYSADMIN or ACCOUNTADMIN and that the correct warehouse is selected.
  3. Copy the text of the sample query and paste it into the SQL worksheet.
  4. Follow the instructions included in the sample query to set the variables and execute the query.

Write your own queries

If modifying one of the GTD Cloud reports does not give you the data you need, you can create a custom query. Snowflake offers extensive SQL support to manipulate data beyond what is possible in GTD Cloud reports.

The next section includes some short example queries that show how you might pull, filter, and order data. You can also check the Reference section of the Snowflake documentation for supported SQL commands and functions.

When you have written your query, follow the steps in the Edit sample queries of Sovos reports section to query the database, using your custom query instead of the sample.

Examples

Pull a list of 100 document numbers 
select distinct(transaction_document_number)
from lbj_share_view_with_policy
group by transaction_document_number
limit 100;
Find transactions with a specific document number 
select from lbj_share_view_with_policy
where transaction_document_number = '0689815962';
Query the line item audit ID, taxing jurisdiction, document tax amount, line item tax amount, taxing jurisdiction-level tax amount, gross amount, and tax rate for a specific document, state, and invoice date range 
select line_item_audit_id, tj_type,  document_tax_amount,
line_item_tax_amount, line_tj_result_tax_amount,
gross_amount, tax_rate from lbj_share_view_with_policy 
where transaction_document_date
BETWEEN TO_DATE('2020-06-01', 'YYYY-MM-DD')
AND TO_DATE('2020-06-30', 'YYYY-MM-DD')
and ship_to_tjc_state = 'ILLINOIS'
and document_audit_id = 'e6f6054e-5c7e-465a-b5c5-4ac840a78078';
limit 5;
Find cities with transactions in a specific state 
select distinct(ship_to_tjc_city), count(1)
from lbj_share_view_with_policy 
where ship_to_tjc_state = 'ILLINOIS'
group by ship_to_tjc_city
order by count(1) desc;

Download results

After you have successfully queried the database, the query results will appear in the Results section. To download the data, click the Download results icon, then select a format.

The results will be downloaded to your computer. The file will have the same name as the SQL worksheet.