OleDb Select Command Returning an Empty Column

Question

I have two saved queries in my database called Free_Cash_Flow and Quarterly_Growth_Rates. They are shown here:

Free_Cash_Flow:

Quarterly_Growth_Rates:

When I use this code:

SELECT * _
FROM Free_Cash_Flow AS C _
INNER JOIN Quarterly_Growth_Rates AS G _
ON (C.Ticker = G.Ticker) AND ((IIF(C.Period = 4, C.Year + 1, C.Year)) = G.Year) AND ((IIF(C.Period = 4, 1, C.Period + 1)) = G.Qtr)

This is returned:

The column Free_Cash_Flow is blank. But as you can see in the Free_Cash_Flow table, there is data in that column. Why is is not being pulled into the new table? I run this query with other tables of similar format and everything works great. Any suggestions?

Additional Info

I'd like to add that the Free_Cash_Flow column is populated using an equation in the SQL statement of its respective saved query. I think this may have something to do with my problem. I'm really stuck here and I desperately need to figure this out.

Here is the code containing the SQL statement used for the Free_Cash_Flow saved query:

Variables:

Dim Calculation = “Free_Cash_Flow”

Dim Formula = “(SELECT (SUM(su.Net_Cash_Flow_Operating) - SUM(su.Capital_Expenditures)) _
FROM (SELECT Ticker, [Year], Period, Net_Cash_Flow_Operating, Capital_Expenditures _
FROM Cash_Flow_Statements UNION ALL SELECT Ticker, [Year] + 1, Period - 4, Net_Cash_Flow_Operating, Capital_Expenditures _
FROM Cash_Flow_Statements) su _
WHERE su.Ticker = c.Ticker AND su.[Year] = c.[Year] AND (su.Period Between c.Period - 3 And c.Period))”

Dim Where_Statement = "WHERE i.Period < 5"

SQL Statement:

"CREATE PROC " & Calculation & " AS SELECT i.Ticker, i.[Year], i.Period, " & Formula & " AS " & Calculation & " _
FROM (Income_Statements AS i _
INNER JOIN Balance_Sheets AS b _
ON (i.Ticker = b.Ticker) AND (i.[Year] = b.[Year]) AND (i.Period = b.Period)) _
INNER JOIN Cash_Flow_Statements AS c ON (b.Ticker = c.Ticker) AND (b.[Year] = c.[Year]) AND (b.Period = c.Period) " & Where_Statement & ""

Answer 1

This may have nothing to do with your JOIN. I wonder if Free_Cash_Flow is of a type that OLEDB doesn't understand. It looks like it's in Open Office as a currency value, right? Do you know for a fact that OLEDB can see that value at all? Try a simpler query like

SELECT C.Free_Cash_Flow FROM Free_Cash_Flow AS C

and see if that turns up anything at all.

Answer 2

Dont specifically know why, but using "*" is typically not preferred, but you might have better / proper answer if you explicitly query the named columns, such as

select 
      C.Ticker,
      C.Year,
      C.Period,
      C.Free_Cash_Flow,
      G.Year as GrowthYear,
      G.Period as GrowthPeriod 
   from ....

It MIGHT be getting confused because the table name is the same as the column and ignoring it. By qualifying the columns with your "C" and "G" aliases respectively, might do what you are looking for.

Answer 3

Have you tried changing the last part to a WHERE and see what that returns?

WHERE ((IIF(C.Period = 4, C.Year + 1, C.Year)) = G.Year) AND
      ((IIF(C.Period = 4, 1, C.Period + 1)) = G.Qtr)

Answer 4

You need to specify which column you need when you rename like C & G below:

SELECT C.*, G.* _
FROM Free_Cash_Flow AS C _
INNER JOIN Quarterly_Growth_Rates AS G _
ON (C.Ticker = G.Ticker) AND ((IIF(C.Period = 4, C.Year + 1, C.Year)) = G.Year) AND ((IIF(C.Period = 4, 1, C.Period + 1)) = G.Qtr)

Answer 5

Theoretically, there is no reason why it should not work, save for the fact that the column name is identical to the table name. Perhaps your database engine is confused by this, try renaming.

Answer 6

Try replacing the table name like below and check whether Quarterly_Growth_Rates column is coming if that column is also not coming then it may be the name identical issue.

SELECT * _
FROM Quarterly_Growth_Rates AS G _ INNER JOIN Free_Cash_Flow AS C _ON (G.Ticker = C.Ticker) AND (G.Year = (IIF(C.Period = 4, C.Year + 1, C.Year))) AND (G.Qtr = (IIF(C.Period = 4, 1, C.Period + 1)))