How to write an SQL in toad

I've attached a sample SQL snippet below.  As you can see, in the WHERE clause there is a parenthetical statement that either forces a field to be explicitly equal to a prompt value or the prompt = "All".  Since &PROMPT isn't a database field, it essentially forces the entire statement to be ignored when the user selects 'All', thereby returning all possible records since the field isn't being filtered.

~Kurt

SELECT
  A.FIELDA,
  A.FIELDB,
  A.FIELDC
FROM
  TABLE A
WHERE
  (A.FIELDA = &PROMPT OR &PROMPT = 'ALL')

Select allOpen in new window

Actually, there's an error in my code - if the &PROMPT is a string datatype, then the last line should be:

(A.FIELDA = '&PROMPT' OR '&PROMPT' = 'ALL')

~Kurt

It did not worked for me.
Below is the query, i used along with four other prompts. Looks like it is give results based on other four prompts.
the number of records does not matches with Webi Report.
(B . BATCH_NBR ='&PROMPT' OR '&PROMPT'='ALL')
OR

C . AS_RPTD_BATCH_NBR IN ( '40135' )

OR

LSS . DRUG_BATCH_LOT_NUMBER_LSS IN ( '9RL13M' ) OR

LSS . DEVICE_LOT_NUMBER_LSS IN ( 'A1252' ) OR

LSS . QC_LOT_NUMBER_LSS IN ( '9RL13M' )

)
Thanks

Have you use Show SQL Query in WEBI to see the SQL that WEBI is generating?  Please show your entire SQL Statement and what you expect to be accomplished.  In the case of your code above, it's a little confusing having so many OR keywords, but for different database fields.  Is there a possibility that there will be overlapping values.  Also, are those explicity values what would otherwise be entered via parameter?

~Kurt

Ok i have five prompts, non of which can be left blank. User has to enter the parameter value as in DB.He can enter multiple values or he can enter "ALL" for any of the pararmeter.
Let me know if you have further Q?.
Thanks

Thanks a lot, it did worked although i have tweak it here and there.