?
Solved

How to write an SQL  in toad

Posted on 2009-04-22
7
Medium Priority
?
552 Views
Last Modified: 2013-11-15
I have developed a Webi Report which has five prompts all of which have an option to enter "ALL" to get ALL the values).
My Question is how do i write a sql in toad to test the data when the prompt entered is ALL
0
Comment
Question by:bodeveloper17
  • 4
  • 3
7 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 24204599
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')

Open in new window

0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 24204629
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

0
 

Author Comment

by:bodeveloper17
ID: 24206097
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
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 24208216
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
0
 

Author Comment

by:bodeveloper17
ID: 24208312
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
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 1500 total points
ID: 24208849
Assuming your prompts are for the fields listed above, the WHERE clause in Toad would look like what I've attached below.  This type of code essentially lets you have 5 "optional" parameters.  The parameters are optional in that you can choose to select an explicit value for each one of them or for only a single one of them.   For the purpose of simplification, I've changed IN to =.  This is because SQL input parameters don't allow multiple values.  If you want to test multiple input values, you'll need to hardcode them for the sake of testing.  I've also changed OR to AND between each of the statements.   Unless there's absolutely no possibility that you'll have overlapping records when comparing data from these different tables and fields, you don't want to use OR, or else you won't get the results you want.

Also - I'd really recommend you copy and past the SQL that WEBI generates directly into Toad to see what it does.

~Kurt



(B . BATCH_NBR = '&PROMPT1' OR '&PROMPT1'='ALL')
AND (C.AS_RPTD_BATCH_NBR = '&PROMPT2' OR '&PROMPT21'='ALL')
AND (LSS.DRUG_BATCH_LOT_NUMBER_LSS = '&PROMPT3' OR '&PROMPT3'='ALL')
AND (LSS.DEVICE_LOT_NUMBER_LSS = '&PROMPT4' OR '&PROMPT4'='ALL')
AND (LSS.QC_LOT_NUMBER_LSS = '&PROMPT5' OR '&PROMPT5'='ALL')

Open in new window

0
 

Author Closing Comment

by:bodeveloper17
ID: 31573265
Thanks a lot, it did worked although i have tweak it here and there.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question