Question

Quick Reports SQL - Multiple Left Join Issue

  • 22 August 2023
  • 6 replies
  • 76 views
T
Rank
Badge +1

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

6 replies

ashen_malaka_ranasinghe
Rank
Userlevel 3
Badge +10

Hi @tcboutte,

What is the error that you are getting?

Find me on LinkedIn as Ashen Malaka: https://www.linkedin.com/in/ashen-malaka-ranasinghe/
T
Rank
Badge +1

Unfortunately, nothing more than this from the logs: “SQL_EXPRESSION - (Server Application Error): Sql Expression is wrong”.

 

It is first time I have tried to do a JOIN on a column created from the prior JOIN command. That shouldn’t be an issue in IFS, right?

K
Rank
Userlevel 4
Badge +9

@tcboutte I looked at your join and I think this is incorrect

 

LEFT JOIN
    SUB_PROJECT1 ON GEN_LED_VOUCHER_ROW_QRY.Sub_Project_ID = SUB_PROJECT1.Sub_Project_ID

 

There is no GEN_LED_VOUCHER_ROW_QRY.Sub_Project_ID column in the GEN_LED_VOUCHER_ROW_QRY view? 

 

I can only find a PROJECT_ID? , could that be your error?

 

 
Bjornar Brekka
Rank
Badge +2

Maybe try to wrap your code inside a Select like this:

 

Select * from(

Youre sql

 

)

 

or rewrite your SQL without using WITH statement

T
Rank
Badge +1

@kvbe - I am bringing in SUB_Project_ID from the ‘ACTIVITY’ table with this line in my select statement: ACTIVITY.Sub_Project_ID AS "Sub Project ID". It is the one I am trying to JOIN on.

 

@Bjornar Brekka I’ll try this!

T
Rank
Badge +1

I appreciate everyone’s eyes on this. I wish I could explain it, but without changing anything, that query works fine today… Maybe I was running into a server-side bug or load problem that is no gone. Thanks all!

Reply


Terms & ConditionsCookie settings