Home » SQL & PL/SQL » SQL & PL/SQL » add decode in a where clause
add decode in a where clause [message #275201] Thu, 18 October 2007 15:11 Go to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

ANY SUGGESTIONS IS APPRECIATED.

I AM TRYING TO EXLUDE A VALUE FROM A TABLE IF 'ALL' IS SELECTED FROM THE WHERE CLAUSE


SELECT bvsu.date_of_use,
bvsu.entry_point
FROM
VOC_SITE_USE bvsu
WHERE
bvsu.date_of_use BETWEEN p_date_from AND p_date_to
AND bvsu.entry_point LIKE :p_entry_point
--values entered could be %, west, east , north, south

IF the value :p_entry_point = % (meaning ALL)
I want to exclude one of the VALUES from the TABLE (West for example) FROM the ALL search, how DO I WRITE this IN my SELECT STATEMENT WHERE CLAUSE , can DECODE be used , IF so how?
Re: add decode in a where clause [message #275203 is a reply to message #275201] Thu, 18 October 2007 15:27 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Where does this code run from because it is invalid at a SQL prompt. You don't use : syntax at a SQL prompt. you also do not have a slash or semicolon to execute this query.

Please tell us the context of your query, your Oracle version number, please use CODE tags to format your query to make it legible and please do not use ALL CAPS when asking a question.

And unless you are comparing the column bvsu.date_of_use to two other columns in the same table, you need to use TO_DATE on the date from and date two criteria.

[Updated on: Thu, 18 October 2007 15:28]

Report message to a moderator

Re: add decode in a where clause [message #275204 is a reply to message #275201] Thu, 18 October 2007 15:29 Go to previous messageGo to next message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This will eliminate department '10' if '%' is entered as a parameter. Is this what you are looking for?
SELECT * FROM DEPT
WHERE deptno LIKE :p_deptno
  AND deptno <> DECODE(:p_deptno, '%', 10, -1);
However, hardcoded values (10, -1) don't look very promising. Besides, which value do you want to eliminate? "West, for example" is OK "for example", but what would you choose in real life?
Re: add decode in a where clause [message #275208 is a reply to message #275203] Thu, 18 October 2007 16:01 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

its in a cursor in a procedure in PL/SQL Oracle forms 9i, looking to insert the results from the cursor into a table.
Re: add decode in a where clause [message #275210 is a reply to message #275204] Thu, 18 October 2007 16:05 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

This is exactly what I needed,thanks! But can you explain to me the contents of the Decode (I am unfamiliar with how this function actually works)what does the -1 mean?
Re: add decode in a where clause [message #275211 is a reply to message #275210] Thu, 18 October 2007 16:15 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

However, hardcoded values (10, -1) don't look very promising. Besides, which value do you want to eliminate? "West, for example" is OK "for example", but what would you choose in real life?

The values from the p_entry_point come from a static lov, and 'West' for example is the only value from the list that should not be included in the query. Did I make any sense?
Re: add decode in a where clause [message #275254 is a reply to message #275211] Fri, 19 October 2007 01:03 Go to previous message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
WHERE deptno LIKE :p_deptno
  AND deptno <> DECODE(:p_deptno, '%', 10, -1);

If there was only the first line (WHERE deptno LIKE :p_deptno), you'd always get what you put into the parameter. If it was '10', you'd get only department 10. If it was '%', you'd get all departments.

The second line (AND deptno <> DECODE(:p_deptno, '%', 10, -1)) restricts the output. Read it this way:
if p_deptno = '%'           --> and deptno <> 10 (omit department 10)
if p_deptno = anything else --> and deptno <> -1 (omit department -1. But, there's no department -1 in 
                                                  the DEPT table, so it will, actually, return all records)

Read more about DECODE function here.
Previous Topic: Help needed
Next Topic: PL/SQL Tuning (merged 2 threads)
Goto Forum:
  


Current Time: Mon Dec 05 02:35:00 CST 2016

Total time taken to generate the page: 0.11515 seconds