Help - Search - Members - Calendar
Full Version: Excluding Records
Oracle DBA Forums > Oracle > Oracle Forum
ilundy77
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.
dvsoukup
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.
ilundy77
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.

burleson
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')
ilundy77
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!
burleson
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.