Forum Discussion

mheggie's avatar
mheggie
Frequent Visitor
6 months ago
Solved

Fabric Warehouse Incorrect Query Results

I'm hoping to get some guidance from the group on where to go with this.  I have 2 queries that run in sequence that read from my Fabric data warehouse.  The queries are different and should yield di...
data warehouse
  • deborshi_nag's avatar
    deborshi_nag
    6 months ago

    yeah, best to raise a ticket with product support - use the menu above Support > Product Support to log the new support case. Please make it clear in the ticket that removing the variable StartingDate resolves it; and the issue seems to be happening quite recently. 

     

    If my workaround helped please mark it as a solution, so that others in this situation can take advantage of this workaround! 

     

deborshi_nag's avatar
deborshi_nag
Super User
6 months ago

Hello mheggie 

 

It is a very strange behavious indeed! Can you try using a WITH clause instead of a IN clause and let me know if you see any difference in the behaviour? Here's one of your query with a WITH clause. 

 

-- Derive the starting date: first day of the current year minus 10 years
DECLARE @StartingDate DATE = DATEADD(YEAR, -10, DATETRUNC(year, GETDATE()));

WITH PayrollGL AS (
    SELECT DISTINCT F.ExpenseGLAccountKey
    FROM gold.FactPayrollHistory AS F
    JOIN gold.DimDate AS D
      ON F.PeriodEndDateKey = D.DateKey
    WHERE D.CalendarDate >= @StartingDate
)
SELECT a.*
FROM rpt.vw_DimGLAccount AS a
JOIN PayrollGL AS p
  ON a.[GL Account Key] = p.ExpenseGLAccountKey;

 

mheggie's avatar
mheggie
Frequent Visitor
6 months ago

Thanks for your suggestion. 

 

I tested with your query for both expense and liability and they both return what is expected.  I had already worked around this by replacing @StartingDate in the where clause with the expression I was using to calculate it and it works.

 

select * from rpt.vw_DimGLAccount
where [GL Account Key] in (SELECT F.ExpenseGLAccountKey
                    FROM gold.FactPayrollHistory F
                         JOIN gold.DimDate D ON F.PeriodEndDateKey = D.DateKey
                    WHERE D.CalendarDate >= DATEADD(YEAR, -10, DATETRUNC(year, GETDATE())));
 
There are definitely some workarounds for this which is why it feels like a bug to me. 
 
The process that uses this has been running  successfully in production since May 2025.  This issue just appeared in the last few weeks.
  • deborshi_nag's avatar
    deborshi_nag
    Super User
    6 months ago

    yeah, best to raise a ticket with product support - use the menu above Support > Product Support to log the new support case. Please make it clear in the ticket that removing the variable StartingDate resolves it; and the issue seems to be happening quite recently. 

     

    If my workaround helped please mark it as a solution, so that others in this situation can take advantage of this workaround!