Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Excluding Records, Want to exclude records that contain specific text
ilundy77
post Jan 30 2012, 11:56 AM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 30-January 12
Member No.: 46,676



hi,

I'm very new to sql. I'm using rapid sql to query an oracle db. I need to exclude records that contain the following text:
  • reissue
  • scholastic
  • media tie-in
  • movie tie-in


Currently, I'm using the following query, which returns records that include the text I'd like to exclude (reissue, scholastic, media tie-in, movie tie-in).

Select
ISBN,
BOOK_TITLE,
PUBLISHER_DESCRIPTION,
AUTHOR_BYLINE,
PRODUCT,
COVER_STATUS,
ASSET_TYPE

from ts_admin.editorial
where season in ('2011 September',
'2011 October',
'2011 November',
'2011 December',
'2012 January',
'2012 February',
'2012 March',
'2011 Fall/ 2012 Winter')
and asset_type = 'Cover Image'

Can anyone help me edit this query so that it excludes records containing the text that I've indicated? Thanks in advance for the help. I greatly appreciate it.
Go to the top of the page
 
+Quote Post
dvsoukup
post Jan 30 2012, 04:57 PM
Post #2


Newbie
*

Group: Members
Posts: 7
Joined: 13-January 12
Member No.: 46,588



So that text that you want to exclude....is it in a specific field? Can't tell where it would be merely by looking at your query there wink.gif

Several options here. You could use UNIONS and remove each record w/ that text from each of the queries in the different UNIONS.
ie.
select * from ts_admin.editorial where <field> <> 'reissue'
union
select * from ts_admin.editorial where <field> <> 'scholastic'
union
select * from ts_admin.editorial where <field> <> 'media tie-in'
union
select * from ts_admin.editorial where <field> <> 'movie tie-in'



Or you could probably do something with a nested sub-query like so:

where <field> not in (select <field> from ts_admin.editorial where <field2(field containing your text...)> = 'reissue'
or <field2> = 'scholastic'
or <field2> = 'media tie-in'
or <field2> = 'movie tie-in')


Easiest option would probably be to just incorporate the exclusions right into your query:
Select
ISBN,
BOOK_TITLE,
PUBLISHER_DESCRIPTION,
AUTHOR_BYLINE,
PRODUCT,
COVER_STATUS,
ASSET_TYPE

from ts_admin.editorial
where season in ('2011 September',
'2011 October',
'2011 November',
'2011 December',
'2012 January',
'2012 February',
'2012 March',
'2011 Fall/ 2012 Winter')
and asset_type = 'Cover Image'
and ([field1] <> 'reissue' OR [field1] <> 'scholastic' OR [field1] <> 'media tie-in' OR [field1] <> 'movie tie-in')


Now if those strings you are wanting to exclude are part of a larger string that you would need to parse out, you have a bit more complicated problem on your hand...though still do-able wink.gif

Several ways to do it, but whichever you choose, choose the fastest one. Can run explain plans against the different queries to figure out which one is most optimal.
Go to the top of the page
 
+Quote Post
ilundy77
post Jan 31 2012, 12:45 PM
Post #3


Newbie
*

Group: Members
Posts: 4
Joined: 30-January 12
Member No.: 46,676



Thank you very much for your help, and for bearing with my newbie mistakes. And yes, the text would be in either the MISC or TITLE field. Sorry for the confusion.

QUOTE (dvsoukup @ Jan 30 2012, 04:57 PM) *
So that text that you want to exclude....is it in a specific field? Can't tell where it would be merely by looking at your query there wink.gif

Several options here. You could use UNIONS and remove each record w/ that text from each of the queries in the different UNIONS.
ie.
select * from ts_admin.editorial where <field> <> 'reissue'
union
select * from ts_admin.editorial where <field> <> 'scholastic'
union
select * from ts_admin.editorial where <field> <> 'media tie-in'
union
select * from ts_admin.editorial where <field> <> 'movie tie-in'



Or you could probably do something with a nested sub-query like so:

where <field> not in (select <field> from ts_admin.editorial where <field2(field containing your text...)> = 'reissue'
or <field2> = 'scholastic'
or <field2> = 'media tie-in'
or <field2> = 'movie tie-in')


Easiest option would probably be to just incorporate the exclusions right into your query:
Select
ISBN,
BOOK_TITLE,
PUBLISHER_DESCRIPTION,
AUTHOR_BYLINE,
PRODUCT,
COVER_STATUS,
ASSET_TYPE

from ts_admin.editorial
where season in ('2011 September',
'2011 October',
'2011 November',
'2011 December',
'2012 January',
'2012 February',
'2012 March',
'2011 Fall/ 2012 Winter')
and asset_type = 'Cover Image'
and ([field1] <> 'reissue' OR [field1] <> 'scholastic' OR [field1] <> 'media tie-in' OR [field1] <> 'movie tie-in')


Now if those strings you are wanting to exclude are part of a larger string that you would need to parse out, you have a bit more complicated problem on your hand...though still do-able wink.gif

Several ways to do it, but whichever you choose, choose the fastest one. Can run explain plans against the different queries to figure out which one is most optimal.

Go to the top of the page
 
+Quote Post
burleson
post Jan 31 2012, 02:55 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,100
Joined: 26-January 04
Member No.: 13



Hi

>> I need to exclude records that contain the following text:

Or, you mightlike to filter our unwanted rows)

CODE
select
  tezt_column
where
text_column not in ('media tie-in,'scholastic','ETC')


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
ilundy77
post Jan 31 2012, 03:16 PM
Post #5


Newbie
*

Group: Members
Posts: 4
Joined: 30-January 12
Member No.: 46,676



QUOTE (burleson @ Jan 31 2012, 02:55 PM) *
Hi

>> I need to exclude records that contain the following text:

Or, you mightlike to filter our unwanted rows)

CODE
select
  tezt_column
where
text_column not in ('media tie-in,'scholastic','ETC')



Thanks very much!
Go to the top of the page
 
+Quote Post
burleson
post Jan 31 2012, 03:43 PM
Post #6


Advanced Member
***

Group: Members
Posts: 11,100
Joined: 26-January 04
Member No.: 13



Sorry!

If the column is embedded inside other fields, use "like" or regexp_like:

CODE
select
text_column
where
text_column not like ('%media tie-in%')
or
text_column not like ('%movie tie-in%')
or
text_column not like ('%scholastic%')
or
text_column not like ('&reissue&')


Please read:

http://www.dba-oracle.com/t_sql_exclude_ro...ded_strings.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th April 2014 - 07:12 PM