Home » SQL & PL/SQL » SQL & PL/SQL » SQL QUERY USING DECODE FUNCTION
SQL QUERY USING DECODE FUNCTION [message #188712] Mon, 21 August 2006 05:10 Go to next message
jai_162
Messages: 21
Registered: July 2006
Junior Member
By using the Decode function ,Can you code the below query?

if (Metro Cities ---> like 'Bangalore','Chennai','Mumbai',Kolkotta,'Delhi)

Non Metro Cities--> like 'Mysore','Hyderabad','Cochin','Mangalore' etc

Regards
Jayaprakash K

Report message to a moderator

Re: SQL QUERY USING DECODE FUNCTION [message #188729 is a reply to message #188712] Mon, 21 August 2006 06:03 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
In Decode function, you can not use oracle "like" future directly. but however you can achieve the same with little modification.
for example,
Quote:

if (Metro Cities ---> like 'Bangalore','Chennai','Mumbai',Kolkotta,'Delhi')


DECODE(Metro Cities, 'Bangalore', '[your result]','Chennai','[your result]','Mumbai', '[your result]','Kolkotta', '[your result]','Delhi', '[your result]')


Quote:

Non Metro Cities--> like 'Mysore','Hyderabad','Cochin','Mangalore' etc


Same as above.

Thanks,
Thangam

Report message to a moderator

Re: SQL QUERY USING DECODE FUNCTION [message #188731 is a reply to message #188712] Mon, 21 August 2006 06:09 Go to previous messageGo to next message
Tom Dunne
Messages: 13
Registered: August 2006
Location: Dublin, Ireland
Junior Member
I don't think it's clear what you are trying to do.

The decode works as follows:

Decode( value_to_search_for
       value1, Result_if_search_item_is_value1,
       value2, Result_if_search_item_is_value2,
       ...
       , default_value)


An example is here on the Oracle documentation.

Report message to a moderator

Re: SQL QUERY USING DECODE FUNCTION [message #188738 is a reply to message #188731] Mon, 21 August 2006 06:48 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Well, let me clarify....

i have coded the decode function as you mentioned only.
For example
DECODE(Metro Cities, 'Bangalore', '[your result]','Chennai','[your result]','Mumbai', '[your result]','Kolkotta', '[your result]','Delhi', '[your result]')

where
Metro Cities is value_to_search_for,
'Bangalore' is value1,
Result_if_search_item_is_value1 is [your result],
'Chennai' is value2,
Result_if_search_item_is_value2 is [your result], etc.


Actually the question is(whatever i have understood, please correct me if i'm wrong)

if (Metro Cities --> like 'Bangalore','Chennai','Mumbai',Kolkotta,'Delhi) then
Metro_result
elsif (Non Metro Cities--> like 'Mysore','Hyderabad','Cochin','Mangalore') then
Non_Metro_result
end if;

Thanks,
Thangam

Report message to a moderator

Re: SQL QUERY USING DECODE FUNCTION [message #189074 is a reply to message #188731] Wed, 23 August 2006 02:13 Go to previous messageGo to next message
jai_162
Messages: 21
Registered: July 2006
Junior Member
But this solution is very long.i.e need to enter the City name then Output each time.This looks bit long solution.Is there is any other way to find whether it is Metro or Non-Metro depending on the input.?

Report message to a moderator

Re: SQL QUERY USING DECODE FUNCTION [message #189098 is a reply to message #189074] Wed, 23 August 2006 03:41 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
i believe that with the help of decode we could not accomplish as you expected in simpler way. why dont you try with case when statement because it is more flexible i.e we can use direct like or in etc

Thanks,
Thangam

Report message to a moderator

Re: SQL QUERY USING DECODE FUNCTION [message #189108 is a reply to message #189098] Wed, 23 August 2006 04:34 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Can you describe your table? How can Oracle know whether a city is a metro city?

MHE

Report message to a moderator

Re: SQL QUERY USING DECODE FUNCTION [message #189127 is a reply to message #189108] Wed, 23 August 2006 05:08 Go to previous messageGo to next message
jai_162
Messages: 21
Registered: July 2006
Junior Member
COLUMN NAME IS : CITY

Table name : DEPT

SELECT DECODE(&city,'BANGALORE','METRO','MANGALORE','NON-METRO',
......) FROM DEPT;

Report message to a moderator

Re: SQL QUERY USING DECODE FUNCTION [message #189131 is a reply to message #189127] Wed, 23 August 2006 05:28 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I repeat my question: How can Oracle know whether a city is a metro city?

MHE

Report message to a moderator

Re: SQL QUERY USING DECODE FUNCTION [message #189133 is a reply to message #189131] Wed, 23 August 2006 05:32 Go to previous messageGo to next message
jai_162
Messages: 21
Registered: July 2006
Junior Member
By knowing the City Name.
Like City-->Bangalore,Chennai,Delhi,Calcutta are Metro Cities.
Otherwise it is Non-Metros.

Report message to a moderator

Re: SQL QUERY USING DECODE FUNCTION [message #189136 is a reply to message #189133] Wed, 23 August 2006 05:39 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Ok, then you could do something like this.
Script:
CREATE TABLE cities(cname VARCHAR2(20))
/

 INSERT INTO cities VALUES ('Bangalore');
 INSERT INTO cities VALUES ('Chennai'  );
 INSERT INTO cities VALUES ('Mumbai'   );
 INSERT INTO cities VALUES ('Kolkotta' );
 INSERT INTO cities VALUES ('Delhi'    );
 INSERT INTO cities VALUES ('Mysore'   );
 INSERT INTO cities VALUES ('Hyderabad');
 INSERT INTO cities VALUES ('Cochin'   );
 INSERT INTO cities VALUES ('Mangalore');
  
SELECT c.cname
      , nvl(v1.ctype, 'NON-METRO') type
FROM    cities c
   ,    ( SELECT 'METRO' ctype
               , the_value cname
          FROM   TABLE(string_to_tab('Bangalore,Chennai,Mumbai,Kolkotta,Delhi',','))
        ) v1
WHERE   c.cname = v1.cname(+)
/
 
DROP TABLE cities
/
Run:
SQL> @orafaq

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


CNAME                TYPE
-------------------- ---------
Bangalore            METRO
Chennai              METRO
Cochin               NON-METRO
Delhi                METRO
Hyderabad            NON-METRO
Kolkotta             METRO
Mangalore            NON-METRO
Mumbai               METRO
Mysore               NON-METRO

9 rows selected.


Table dropped.
string_to_tab is a user defined function. It is also known as str2tbl. Several aliases exist on the board. Search for the code here.

MHE

Report message to a moderator

Previous Topic: using forall with rowtype in collection
Next Topic: ORA - 03121 : no interface driver connected - function not performed
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Dec 04 18:59:26 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.