---
title: "SQL Queries for Lucanet.Financial Warehouse"
source_url: https://support.lucanet.com/en/documentation/consolidation-financial-planning/imports-journals/analyze-database/sql-queries-fwh
language: en
last_updated: 2026-10-01
---
# SQL Queries for Lucanet.Financial Warehouse

## Overview

With the following SQL queries, you can check the values in Lucanet.Financial Warehouse. Run the queries with the **Analyze database** function (see [Analyzing Databases](https://support.lucanet.com/en/documentation/consolidation-financial-planning/imports-journals/analyze-database.md)).

The SQL queries for Lucanet.Financial Warehouse help in the following situations:

- Incorrect values are displayed in Lucanet, but the correct values are contained in Lucanet.Financial Warehouse. In this case, the import into the reporting entities must be checked.
- The expected values are not in Lucanet.Financial Warehouse. In this case, a check must be performed to determine whether the import into Lucanet.Financial Warehouse and the script used for the import were configured correctly.

## Notes on Using SQL Queries

The following notes apply when you use the SQL queries for Lucanet.Financial Warehouse:

- The elements listed under [Placeholders in the Queries](#placeholders-in-the-queries) are placeholders and must be replaced accordingly.
- Individual lines can be commented out using `//`, which means the lines are no longer taken into account in the SQL query.
- Texts, and therefore also dimension values, are put in single quotation marks (`'`). Figures, such as the fiscal year and periods, are not.
- You can combine various queries with `UNION` or `UNION ALL` if the number of result columns, their names, and their data types are identical. With `UNION`, data records that occur multiple times are not returned, which only affects duplicates. With `UNION ALL`, all data records are returned.
- For the SQL statements, the following aliases can be used for the tables, if necessary, to make the queries clearer: `B` for `Bundle`, `OB` for `OpeningBalance`, `FP` for `FinancialPosting`, and `FPL` for `FinancialPostingLine`.

The following operators can be used in the SQL queries for Lucanet.Financial Warehouse:

| Operator | Description |
|---|---|
| `=` | Equal to |
| `!=` | Not equal to |
| `<>` | Not equal to |
| `>` | Greater than |
| `<` | Less than |
| `>=` | Greater than or equal to |
| `<=` | Less than or equal to |
| `BETWEEN x AND y` | Is between value x and y, each included |
| `LIKE %%` | Placeholder for character strings |
| `IN ()` | Matches any value in the specified list |

Examples of the `LIKE` and `IN` operators:

- `"Dim_GLAccount" LIKE '%1000'` means accounts that end with 1000.
- `"Dim_GLAccount" LIKE '1000%'` means all accounts that start with 1000.
- `"Dim_GLAccount" IN ('1000', '2000', '3000')` means the accounts 1000, 2000, and 3000.

Conditions can be linked with `AND`, `OR`, or `NOT`, for example `NOT IN (x, y, z)`, and can be encapsulated using brackets.

## Placeholders in the Queries

The SQL queries for Lucanet.Financial Warehouse contain various placeholders that must be replaced before sending the SQL query:

| Placeholder | Description |
|---|---|
| ReportingEntityCode | Name of the reporting entity |
| AdjustmentLevelCode | Name of the adjustment level |
| DataLevelCode | Name of the data level |
| FiscalYear | Fiscal year |
| Period | Posting period |
| Account, and more | Dimension value |

## Available Dimension Codes

The following default dimensions are available in Lucanet.Financial Warehouse, if configured:

| Dimension code | Description |
|---|---|
| GLAccount | Ledger account |
| GroupAccount | Group account |
| SubLedgerAccount | Sub-account of ledger account, for example debtor, creditor, or asset |
| CostCenter | Cost center |
| FunctionArea | Functional area |
| LegalEntityPartner | Partner |
| TransactionType | Transaction type for schedules |
| BusinessUnit | Business unit |
| CostElement | Costs and/or revenue type from cost accounting |
| CostUnit | Cost unit |
| Order | Order |
| OrganizationUnit | Organization unit |
| ProfitCenter | Profit center |
| Project | Project |
| StatisticAccount | Statistical account |

> **Note:** Dimension codes are specified for querying the master data from the `ElementAttribute` table without `Dim_`, for example `GLAccount` instead of `Dim_GLAccount`. For the `ElementCode` of subledger accounts (`SubLedgerAccount`), a prefix is usually used that specifies the account more specifically: `C_` for creditors, `D_` for debtors, and `A_` for assets.

## Example Queries

The following sections contain example queries for Lucanet.Financial Warehouse.

### Determining the BundleCode

Determines the BundleCode for a specific reporting entity and fiscal year. The last executed import comes first.

```sql
SELECT *
FROM "Bundle"
WHERE
     "LegalEntityCode" = 'ReportingEntityCode'
 AND "FiscalYear"      = FiscalYear
ORDER BY
   "CreationDateTime"
```

### Determining All Postings

Determines all postings for a specific reporting entity, fiscal year, and period. It is possible to restrict the result to various dimensions.

```sql
SELECT "FPL".*
FROM "FinancialPostingLine" "FPL"
INNER JOIN "Bundle" "B"
  ON "FPL"."BundleCode" = "B"."BundleCode"
INNER JOIN "FinancialPosting" "FP"
  ON "FPL"."BundleCode"          = "FP"."BundleCode"
 AND "FPL"."FinancialPostingCode" = "FP"."FinancialPostingCode"
WHERE
     "B"."LegalEntityCode"            = 'ReportingEntityCode'
 AND "B"."FiscalYear"                 = FiscalYear
 AND "FPL"."Period"                   = Period
 AND "FPL"."Dim_GLAccount"            = 'Account'
 AND "FPL"."Dim_SubLedgerAccount"     = 'SubLedger'
 AND "FPL"."Dim_LegalEntityPartner"   = 'PartnerCode'
 AND "FPL"."Dim_CostCenter"           = 'CostCenter'
 AND "FPL"."Dim_TransactionType"      = 'TransactionType'
```

### Determining Opening Balance Sheet Values

Determines the opening balance sheet values for a specific reporting entity and fiscal year. It is possible to restrict the result to various dimensions.

```sql
SELECT "OB".*
FROM "OpeningBalance" "OB"
INNER JOIN "Bundle" "B"
  ON "OB"."BundleCode" = "B"."BundleCode"
WHERE
     "B"."LegalEntityCode"           = 'ReportingEntityCode'
 AND "B"."FiscalYear"                = FiscalYear
 AND "OB"."Dim_GLAccount"            = 'Account'
 AND "OB"."Dim_SubLedgerAccount"     = 'SubLedger'
 AND "OB"."Dim_LegalEntityPartner"   = 'PartnerCode'
 AND "OB"."Dim_CostCenter"           = 'CostCenter'
 AND "OB"."Dim_TransactionType"      = 'TransactionType'
```

### Determining the Balance

Determines the balance for a specific reporting entity and fiscal year. It is possible to restrict the result to the period and to various dimensions.

```sql
SELECT 0 AS "Period", SUM("Debit"-"Credit") AS "Value"
FROM "OpeningBalance" "OB"
INNER JOIN "Bundle" "B"
  ON "OB"."BundleCode" = "B"."BundleCode"
WHERE
     "B"."LegalEntityCode"           = 'ReportingEntityCode'
 AND "B"."FiscalYear"                = FiscalYear
 AND "OB"."Dim_GLAccount"            = 'Account'
 AND "OB"."Dim_SubLedgerAccount"     = 'SubLedger'
 AND "OB"."Dim_LegalEntityPartner"   = 'PartnerCode'
 AND "OB"."Dim_CostCenter"           = 'CostCenter'
 AND "OB"."Dim_TransactionType"      = 'TransactionType'
UNION ALL
SELECT "FPL"."Period", SUM("Debit"-"Credit") AS "Value"
FROM "FinancialPostingLine" "FPL"
INNER JOIN "Bundle" "B"
  ON "FPL"."BundleCode" = "B"."BundleCode"
INNER JOIN "FinancialPosting" "FP"
  ON "FPL"."BundleCode"          = "FP"."BundleCode"
 AND "FPL"."FinancialPostingCode" = "FP"."FinancialPostingCode"
WHERE
     "B"."LegalEntityCode"           = 'ReportingEntityCode'
 AND "B"."FiscalYear"                = FiscalYear
 AND "FPL"."Period"                 <= Period
 AND "FPL"."Dim_GLAccount"           = 'Account'
 AND "FPL"."Dim_SubLedgerAccount"    = 'SubLedger'
 AND "FPL"."Dim_LegalEntityPartner"  = 'PartnerCode'
 AND "FPL"."Dim_CostCenter"          = 'CostCenter'
 AND "FPL"."Dim_TransactionType"     = 'TransactionType'
GROUP BY "FPL"."Period"
```

### Determining Element Master Data

Determines the master data for a specific reporting entity, fiscal year, and element. It is possible to restrict the result to various dimensions.

```sql
SELECT *
FROM "ElementAttribute"
WHERE
     "LegalEntityCode" = 'ReportingEntityCode'
 AND "FiscalYear"      = FiscalYear
 AND "DimensionCode"   = 'DimensionCode'
 AND "ElementCode"     = 'ElementCode'
```