WHERE OR NOT WHERE THAT IS THE QUESTION 1

[LaurenM]

Hey all, I need some help with figuring out the logic of the where stmt. Here's the situation: I have a web page front end, where the user inputs a criteria. If they want all pieces of type 3421, then they input 3421, and all pieces of type 3421 are displayed. Thats the easy part. However heres the hard part, if the user wants to see all pieces in the database, then they enter 0000 or some other input (it has to be a number, and four digits long). The trouble I'm having is how to do that in the where clause of the sql. Any help in figuring out the logic and/or some pointers to a web page that explains this would earn my undying gratitude and appreciation.

Lauren.

 

[SJSFoxPro]

Do you use a view / stored procedure / report behind the scenes that you can add logic to?

 

[LaurenM]

No. Just a straight up sql query.

 

[SantaMufasa]

Lauren,

You can use a DECODE function to achieve what you want. Here is a proof-of-concept using the Oracle Education Summit Sporting Goods "s_emp" table. (In this example, I save the code to a script named "tt_332.sql", and I execute the script since it has an embedded ACCEPT statement.):

accept id_in prompt "Enter id to display: "
select id, last_name from s_emp
where decode('&id_in','0000',null,null,'9999999999','&id_in') is null
   or '&id_in' = id
/

SQL> @tt_332
Enter id to display: 1

        ID LAST_NAME
---------- ---------------
         1 Velasquez
SQL> @tt_332
Enter id to display: 25

        ID LAST_NAME
---------- ---------------
        25 Schwartz
SQL> @tt_332
Enter id to display: 888
SQL> @tt_332
Enter id to display: 0000

        ID LAST_NAME
---------- ---------------
         1 Velasquez
         2 Ngao
         3 Nagayama
         4 Quick-To-See
         5 Ropeburn
         6 Urguhart
         7 Menchu
         8 Biri
         9 Catchpole
        10 Havel
        11 Magee
        12 Giljum
        13 Sedeghi
        14 Nguyen
        15 Dumas
        16 Maduro
        17 Smith
        18 Nozaki
        19 Patel
        20 Newman
        21 Markarian
        22 Chang
        23 Patel
        24 Dancs
        25 Schwartz
SQL> @tt_332
Enter id to display: 
SQL>

Notice that when you enter "0000", it displays all rows; an existing id displays that row; a non-existing id displays nothing; a NULL entry also displays nothing.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LaurenM]

Yes!! That is exactly what I am looking for.

 

[Beilstwh]

You can also do something like

where .....
and id_in = '0000' or :id_in = mytable_id)
....

Bill
Oracle DBA/Developer
New York State, USA