Left Join with is null check

Question

Hello 
 I want to write the following SQL Query as 4 GL Link instruction: 
 
 As seen in above screenshot this returns a record. With the following Link statement I try to make this SQL in 4GL. 
 
 In 4 GL I make a left outer join and check with [F:YCFPJOB]ZDOCUPDTICK = AVOID.AINT if the column value is null. Is this not the correct way to test for is null? I don't get the record with the 4GL statement from the screenshot but in SQL I get a record. I saw an old post which suggests using AVOID for such cases: https://www.sagecity.com/us/sage_x3/f/general-discussion/101401/4gl-how-to-outer-join 
 Some additional information: ZDOCUPDTICK is an integer column and YCPFJOB an abbreviation from a custom table with ZCFPJOB as a key. [ZETUP]AEXPFRM contains "[POH]ZCLFUPDTICK < [POH]UPDTICK & [POH]PRNFLG = 2 & [POH]ZPLNNUM <> ''" from a setup field. 
 Thank you for your help. 
 Best regards

Bruno Gonzalez · Accepted Answer

If you want to have NULL and you manage that out of 4GL, you can simply do a FOR .. SQL loop that will execute the query you give it in parameter. Extract from X3 Online help: 
 # Example 1: Request to select a maximum value in a table
 Local Char REQUEST(255)
 Local Integer MAX_VAL
 REQUEST="select max(ACCNUM_0) From GACCENTRYD"
 For (Integer NUM) From "3" Sql REQUEST As [XXX]
 MAX_VAL=[F:XXX]NUM
 Next 
 From "3"= Oracle From "5" or From "S" = SQL Server You can also use a clob field instead of a char variable to store the query.

Sage X3

Left Join with is null check

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING