DB2 error on TSQL using SELECT AS alias and an inner join

Question

I am writing a T-SQL program over a DB2 database on a LINUX box (DB2/LINUXX8664) using a linked server. I think the DB2 is Version 9.5.3 but not certain. I am receiving an error that I feel is likely a DB2 issue as the syntax checks out okay in T-SQL. This is the code:

IF(SELECT(OBJECT_ID('TEMPDB..#TempFile))) IS NOT NULL DROP TABLE #TempFile

SELECT        *

INTO  #TempFile
FROM  OPENQUERY(LinkedServer, '

SELECT  F.LOAN_NUMBER,

   (SELECT 
    SUM(EXP_CHILD_CARE_AMOUNT) + SUM(EXP_FOOD_AMOUNT) + 
    SUM(EXP_LIFE_INSURANCE_AMOUNT) + SUM(EXP_TRANSPORTATION_AMOUNT) + SUM(EXP_TUITION_AMOUNT)+
    SUM(EXP_USER_1_AMOUNT) + SUM(EXP_USER_2_AMOUNT) + SUM(EXP_USER_3_AMOUNT) + 
    SUM(EXP_UTILITIES_AMOUNT)
    FROM FINANCIAL F)
       AS ExpenseTotal,

    (SELECT
     SUM(MORTGAGOR_NET_PAY_AMOUNT) + SUM(MORTGAGOR_OTHER_INCOME_AMOUNT) AS IncomeTotal 
     FROM FINANCIAL F   
      INNER JOIN BDE.LOAN_V a ON F.LOAN_NUMBER = A.LOAN_NUMBER)
     WHERE A.FIRST_PRINCIPAL_BALANCE> 0
        GROUP BY F.LOAN_NUMBER 
        ORDER BY F.LOAN_NUMBER,   


FETCH ONLY WITH UR ')

Here is the error:

OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "A" was found following "BER) WHERE". Expected tokens may include: "FROM". SQLSTATE=42601 ". Msg 7350, Level 16, State 2, Line 4 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "LINKEDSERVER".

Answer 1

= A.LOAN_NUMBER) WHERE A.FIRST_PRINCIPAL_BALANCE> 0 - that bracket looks out of place.

Answer 2

The first thing I notice is that you have a naked query, which is not uncommon in Microsoft SQL Server. A simplified version of your linked query looks like this:

SELECT (subquery), (subquery) WHERE ...conditions...

In DB2, you must have a FROM clause in any query. Microsoft and some other SQL vendors permit a SELECT with no FROM clause, but this isn't standard SQL. In this case, DB2 conforms to the standard.

---

Second thing I notice:

IF(SELECT(OBJECT_ID('TEMPDB..#TempFile))) IS NOT NULL DROP TABLE #TempFile

Do you need to close that quoted string?