cancel
Showing results for 
Search instead for 
Did you mean: 

Contracts with no transaction

vhzl123
Explorer
0 Kudos
149

Hello Everyone,

i would like to have a query that shows me those contracts that have no periodic transaction or models linked.

The following code is not working. It works if i put in the end [%0] and seach for a specific model, it shows me all those contracts that have a specific model but with T1."Code"= '' it doesnt work.

Can someone help?

Thank you!!

SELECT DISTINCT T0."ContractID", T0."CstmrCode", T0."CstmrName" FROM OCTR T0 INNER JOIN CTR2 T2 ON T0."ContractID" = T2."ContractID" INNER JOIN ORCP T1 ON T2."RcpEntry" = T1."AbsEntry" WHERE T1."Code" = ''

Accepted Solutions (1)

Accepted Solutions (1)

pieter_s
Member
0 Kudos

Hi,

Could you try out the below variation of your query?

SELECT DISTINCT T0."ContractID", T0."CstmrCode", T0."CstmrName"

FROM OCTR T0

LEFT JOIN CTR2 T2 ON T0."ContractID" = T2."ContractID"

LEFT JOIN ORCP T1 ON T2."RcpEntry" = T1."AbsEntry"

WHERE T1."Code" IS NULL

This query uses a LEFT JOIN to join the OCTR table with the CTR2 and ORCP tables. The LEFT JOIN ensures that all records from the OCTR table are included in the result set, even if there is no match in the CTR2 or ORCP tables. The WHERE clause filters the results to only include those contracts where there is no matching record in the ORCP table (i.e., where T1."Code" is NULL).

This should select those contracts that do not have any periodic transactions or models linked.

vhzl123
Explorer
0 Kudos

Thank you so much!! It worked.

Answers (0)