Minus operation not working in obiee 11g prompt

Question

Hi friends,

Im in obiee 11.1.1.7.150120. And in the prompt based upon the sql results im issuing the below query

SELECT "Fact1-payroll"."Employee Number" FROM "8 - HR-Payroll"
where "Fact1-payroll"."Period" = '@{v_from_period}{OCT-15}'
MINUS
SELECT "Fact1-payroll"."Employee Number" FROM "8 - HR-Payroll"
where "Fact1-payroll"."To Period" = '@{v_to_period}{NOV-15}'

But the prompt values are not showing up. Suppose in place of MINUS if i use UNION/UNION ALL/INTERSECT then the prompt values are appearing, but im not sure about the minus operation like why it is not working.

Kindly help with a fix for this problem.

Thanks in advance.

Regards,

Saro

Saro · Answer

Hi Jack,

Thanks for the reply.But i dont think there is an issue with the query like i said earlier that the query works fine with UNION/UNION ALL/INTERSECT. I do have problem only with the MINUS as it is not working in the sql prompt results.

Any other possible fix for this problem.

Regards,

Saro

jackohug · Answer

A good way to test out your query for SQL Results in a prompt is to make a New Analysis from Simple Logical SQL. The prompt dialogues don't give you any error messages – they just return no results, but the Analysis page will show you the error that's returned from the BI Server.