Syntax to include process selection in where clause of complex selection

[mandy3]

Hello Team,
As we are trying to write a syntax to include process selection in where clause of complex selection in JOIN and user needs to select multiple values not one & run the Calculation.
Could you please help me on the right syntax in PAPM cloud environment.
Thanks in advance.
Mandy

[andrew_jones1]

HI Mandy.

You did not specify what data source(s) you expect to query in your Complex Selection WHERE clause. But assuming you just want to use a complex selection to restrict the data that exists or does not exist in another function or table, then you could use something like the following to check if data exist in another function (JO01) or does not exist in another table (ZMYADSO).

WHERE
(
	(
      R0001.RBUKRS, R0001.RFAREA, R0001.BLART, R0001.BELNR, R0001.KDAUF, R0001.KDPOS
    ) 
	IN 
	(
      SELECT
        DISTINCT RBUKRS, RFAREA, BLART, BELNR, KDAUF, KDPOS
      FROM
        Y100ENV0001JO01
      WHERE
        ZWWBRE_PA2 LIKE: P_WURC_H1
        AND FS_PER_CLIENT_ =: c_client
        AND FS_PER_PACKAGE_ID_ = IFNULL(: i_package_id, '')
        AND FS_PER_PROC_ID_ =: l_proc_id
        AND FS_PER_FID_ = 'JO01'
    )
	AND
	(
      R0001.RBUKRS, R0001.RFAREA, R0001.BLART, R0001.BELNR, R0001.KDAUF, R0001.KDPOS
    ) 
	NOT IN 
	(
      SELECT
        DISTINCT RBUKRS, RFAREA, BLART, BELNR, KDAUF, KDPOS
      FROM
		"/BIC/AZMYADSO2"
    )
  )

Note some of these Direct Select statements may only work in certain releases.