Re: Dynamic select query

Ranganath,

You could use either

SELECT * FROM TAB1 WHERE INSTR(SHIP_NAME, SHIP_ID) > 0 or
SELECT * FROM TAB1 O FROM WHERE SHIP_NAME LIKE '%'||(SELECT SHIP_ID FROM TAB1 WHERE ROWID = O.ROWID)||'%' I did a test in my system; the INSTR approach was far better than the LIKE one. n the latter the response time was twice as much and consistent reads were 100 times as much.

HTH. Arup
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 17, 2002 5:38 AM

> Hi all,
>
> I have a table by name shipment with two columns say ship_id and
> ship_name and data as follows:
>
> SHIP_ID SHIP_NAME
> ------------- -------------------
> ABC 1ABCLMN
> XYZ 23XYZ1112
> ABB 123LM
>
> I want to select all the columns in shipment table which has ship_id
> as string in the ship_name column. For example, ship_id 'ABC' is present
in
> '1ABCLMN'. So that record should be selected. The ship_id string may
start
> from any position and end at any position in ship_name column. So I
cannot
> use the substr function in this case. How do I write the select clause
for
> this requirement?
>
> Any help in this regard is very much appreciated.
>
> Thanks and Regards,
>
> Ranganath
> WARNING: The information in this message is confidential and may be
legally
> privileged. It is intended solely for the addressee. Access to this
message
> by anyone else is unauthorised. If you are not the intended recipient,
any
> disclosure, copying, or distribution of the message, or any action or
> omission taken by you in reliance on it, is prohibited and may be
unlawful.
> Please immediately contact the sender if you have received this message in
> error. Thank you.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Krishnaswamy, Ranganath
> INET: Ranganath.Krishnaswamy_at_blr.hpsglobal.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Arup Nanda
  INET: arupnanda_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).