Plex 2E

How can I define the SET on an Entity so that the resulting SQL will include NULL values

External application created rows in a table with Null values - so we need to be able to include them in the select 

ie 

need to be able SQL to build as

  WHERE GTCAckId >= 0   and FundViewSeq = 0 or FundViewSeq is null

current process generates a missing tag

"FROM GTCAckResponse T1 "
"WHERE "
"( ( T1.GTCAckId >= ? ) ) AND "
"( ) "
"ORDER BY T1.GTCAckId "

what is the database, what are the fields definitions, what language, what DBMS, can you post a small local to show the issue..etc 

Using SQL Server 2012 - 

Field Definition Example in SQL was a numeric (9) field that was set to NULL when inserted to the Table by an external Process 

In the Model I have added the FLD Value NULL triple along with a FLD NULL NULL Triple 

I then added a State to the Field with the NULL Value  and added the Entity SET SelectNull to the Entity

Created a view that Selects the SET SelectNull  but then when the source is Generated for WIN C and for C#

I can obviously override the SQL and do an Exec SQL  - but wondered why Using a Select Set on the View doesn't seem to generate what I expect or what I am doing wrong

"SELECT T1.GTCAckId, T1.GTCStatus, T1.GTCGTID, T1.GTCErrorMessage, "
"T1.GTCDealType, T1.GTCCashAccountID, T1.GTCSettlementDate, "
"T1.DateCreated, T1.GTCFundID, T1.GTCClientID, T1.GTCCounterpartyID, "
"T1.GTCQuantity, T1.GTCInvestmentID, T1.GTCTradeID, T1.GTCTransactionID, "
"T1.GTCPrice, T1.GTCActionCode, T1.GTCStrategyID, T1.GTCTradeDate, "
"T1.GTCTransactionIndicator, T1.FundViewSeq "
"FROM GTCAckResponse T1 "
"WHERE "
"( ( T1.GTCAckId > ? ) ) AND "
"( ( T1.FundViewSeq < 1 ) ) "
"ORDER BY T1.GTCAckId "
;

the NULL Selection is not Generated

sorry I can get this to generate something right

#BEGIN VIEW LIBRARY_TEST_SET

CREATE VIEW LIBRARY_TEST_SET AS

SELECT

    T1.TESTKEY,

    T1.TESTHAS

FROM LIBRARY_TEST_PHYSICAL_TAT T1

WHERE

  ( ( NOT T1.TESTHAS IS NULL  ) )

#END

I think you need to rethink the SET part

Yes - you are right - It seems to works if the Select set only contains the EQ Val statements - if you add more than 1 they are 'AND'ed together -   if you add a maybe eq val - that's when they are not generated -  - I was hoping to get the selections 'OR'ed on the condition but definitely not quite the same

I will continue to investigate

#BEGIN VIEW LIBRARY_TEST_SET
CREATE VIEW LIBRARY_TEST_SET AS
SELECT
T1.TESTKEY,
T1.TESTHAS
FROM LIBRARY_TEST_PHYSICAL_TAT T1
WHERE
( ( T1.TESTHAS IS NULL ) OR ( T1.TESTHAS = 1 ) )
#END

#BEGIN VIEW LIBRARY_TEST_SET
CREATE VIEW LIBRARY_TEST_SET AS
SELECT
T1.TESTKEY,
T1.TESTHAS
FROM LIBRARY_TEST_PHYSICAL_TAT T1
WHERE
( ( T1.TESTHAS IS NULL ) AND ( T1.TESTHAS >= 1 ) OR ( T1.TESTHAS = 1 ) )
#END

i am pretty sure I could sit down and understand your logic and reproduce it but first I guess I think you need to the word "NULL" into your generated source first...

these are the questions that get me out of plex retirement.so thanks

I can get the NULL generated now by using the EQ value  on the set

 -

"WHERE "
"( ( T1.GTCAckId > ? ) )  AND "
"( ( T1.FundViewSeq IS NULL  ) AND ( T1.FundViewSeq = 0 ) ) "
"ORDER BY T1.GTCAckId "

When I switch to a Maybe EQ -

"( ( T1.GTCAckId > ? ) )  AND "
"( ( T1.FundViewSeq IS NULL  ) OR  ( T1.FundViewSeq = 0 ) ) "
"ORDER BY T1.GTCAckId "

however the order of the triples seem to make a difference  if I have the Maybe as the first triple  - I get

"( ( T1.GTCAckId > ? ) )  AND "
"( ( T1.FundViewSeq = 0 ) AND ( T1.FundViewSeq IS NULL  ) ) "
"ORDER BY T1.GTCAckId "

If I make them BOTH Maybe EQ statements I get  no NULL statement

"WHERE "
"( ( T1.GTCAckId > ? ) )  AND "
"( ( T1.FundViewSeq = 0 ) ) "
"ORDER BY T1.GTCAckId "

But thanks - seems I can do what I want with the correct order of triples