Does anyone know why the following SQL doesn't work in IFS? I only seems to work without the last LEFT JOIN and the associated column that I want to bring in at the bottom of my SELECT statement. I’ve verified the column names exist, but unfortunately the errors in IFS point me nowhere. Thanks!
WITH FilteredAccount AS (
SELECT *
FROM ACCOUNT
WHERE Company = '4400'
)
SELECT
GEN_LED_VOUCHER_ROW_QRY.Company,
GEN_LED_VOUCHER_ROW_QRY.Voucher_Type,
GEN_LED_VOUCHER_ROW_QRY.Voucher_No,
GEN_LED_VOUCHER_ROW_QRY.Accounting_Year,
GEN_LED_VOUCHER_ROW_QRY.Accounting_Period,
GEN_LED_VOUCHER_ROW_QRY.Voucher_Date,
GEN_LED_VOUCHER_ROW_QRY.Year_Period,
GEN_LED_VOUCHER_ROW_QRY.Account,
GEN_LED_VOUCHER_ROW_QRY.Account_Desc,
GEN_LED_VOUCHER_ROW_QRY.Code_B,
GEN_LED_VOUCHER_ROW_QRY.Code_B_Desc,
GEN_LED_VOUCHER_ROW_QRY.Code_C,
GEN_LED_VOUCHER_ROW_QRY.Code_C_Desc,
GEN_LED_VOUCHER_ROW_QRY.Code_D,
GEN_LED_VOUCHER_ROW_QRY.Code_D_Desc,
GEN_LED_VOUCHER_ROW_QRY.Code_E,
GEN_LED_VOUCHER_ROW_QRY.Code_E_Desc,
GEN_LED_VOUCHER_ROW_QRY.Code_F,
GEN_LED_VOUCHER_ROW_QRY.Code_F_Desc,
GEN_LED_VOUCHER_ROW_QRY.Code_G,
GEN_LED_VOUCHER_ROW_QRY.Code_G_Desc,
GEN_LED_VOUCHER_ROW_QRY.Project_Activity_Id,
GEN_LED_VOUCHER_ROW_QRY.Code_H,
GEN_LED_VOUCHER_ROW_QRY.Code_H_Desc,
GEN_LED_VOUCHER_ROW_QRY.Trans_Code,
GEN_LED_VOUCHER_ROW_QRY.Text,
GEN_LED_VOUCHER_ROW_QRY.Amount,
GEN_LED_VOUCHER_ROW_QRY.Correction,
GEN_LED_VOUCHER_ROW_QRY.Header_Correction,
GEN_LED_VOUCHER_ROW_QRY.Quantity,
GEN_LED_VOUCHER_ROW_QRY.Process_Code,
GEN_LED_VOUCHER_ROW_QRY.Optional_Code,
GEN_LED_VOUCHER_ROW_QRY.Party_Type_Id,
GEN_LED_VOUCHER_ROW_QRY.Party_Name,
GEN_LED_VOUCHER_ROW_QRY.Reference_Serie,
GEN_LED_VOUCHER_ROW_QRY.Reference_Number,
GEN_LED_VOUCHER_ROW_QRY.Corrected,
GEN_LED_VOUCHER_ROW_QRY.Multi_Company_Id,
GEN_LED_VOUCHER_ROW_QRY.Is_Multi_Company_Voucher,
GEN_LED_VOUCHER_ROW_QRY.Function_Group,
GEN_LED_VOUCHER_ROW_QRY.Reference_Row_No,
GEN_LED_VOUCHER_ROW_QRY.Period_Allocation,
GEN_LED_VOUCHER_ROW_QRY.Mpccom_Accounting_Id,
GEN_LED_VOUCHER_ROW_QRY.Accounting_Year_Reference,
GEN_LED_VOUCHER_ROW_QRY.Entry_Date,
GEN_LED_VOUCHER_ROW_QRY.Userid,
GEN_LED_VOUCHER_ROW_QRY.Voucher_Type_Reference,
GEN_LED_VOUCHER_ROW_QRY.Voucher_No_Reference,
GEN_LED_VOUCHER_ROW_QRY.Creator_Desc,
GEN_LED_VOUCHER_ROW_QRY.Exclude_Periodical_Cap,
GEN_LED_VOUCHER_ROW_QRY.Entered_By_User_Group,
GEN_LED_VOUCHER_ROW_QRY.Approved_By_Userid,
GEN_LED_VOUCHER_ROW_QRY.Approved_By_User_Group,
GEN_LED_VOUCHER_ROW_QRY.Jou_No,
GEN_LED_VOUCHER_ROW_QRY.Multi_Company_Acc_Year,
GEN_LED_VOUCHER_ROW_QRY.Multi_Company_Voucher_Type,
GEN_LED_VOUCHER_ROW_QRY.Multi_Company_Voucher_No,
FilteredAccount.ACCNT_TYPE,
FilteredAccount.ACCNT_GROUP,
ACTIVITY.Sub_Project_ID AS "Sub Project ID",
SUB_PROJECT1.Description AS "Sub Project Description"
FROM
GEN_LED_VOUCHER_ROW_QRY
LEFT JOIN
FilteredAccount ON GEN_LED_VOUCHER_ROW_QRY.ACCOUNT = FilteredAccount.ACCOUNT
LEFT JOIN
ACTIVITY ON GEN_LED_VOUCHER_ROW_QRY.Project_Activity_Id = ACTIVITY.Activity_Seq
LEFT JOIN
SUB_PROJECT1 ON GEN_LED_VOUCHER_ROW_QRY.Sub_Project_ID = SUB_PROJECT1.Sub_Project_ID
