KB236786: How to achieve the either-or optional prompts in a FFSQL report in Developer?

There might be such a requirement – two prompts are embedded in a FFSQL report, and when one of them is answered, the other one can be ignored. To achieve this, Prompt-dependent SQL setting needs to be used. Below is an example about how to enable this setting to achieve the either-or optional prompts. 

• In the FFSQL report editor, input the custom SQL which has two prompts embedded:
  

  
  Select CATEGORY_ID from LU_CATEGORY 
  Where CATEGORY_ID in ([Value1 (Number)], [Value2 (Number)]); 

  
   

• To make sure only one prompt needs to be answer, choose the text “[Value1 (Number)],” and right mouse click and choose Prompt-dependent SQL (NOTE that the comma also has to be chosen):

• Save the FFSQL;
• Run the report and it is found that only one out of the two prompts needs to be answer to make the report executed as expected.