Solved

Condition to file some string from query

Posted on 2012-04-02
14
376 Views
Last Modified: 2012-04-09
Hi experts,

I'm using SQL Developer to write query.  In one of my query I get pretty much the result I want but notice that there are some extra rows that I would like to exclude, i.e.,

Program_ID
A0
A1
B
C
D
A2

For column 'Program_ID', I only want 'A0' amongst A0, A1, ... A9. I also want to have B, C, D and other values

Can you suggest what should I add in the WHERE statement to accomplish this task ?
0
Comment
Question by:Paul_ATL
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37796632
If you know what you want, an IN list:

where program_id in ('A0','A1','B','C','D')

add A2-A9.

If you mean something else, please clarify.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37796693
actually, I think you want...

where program_id in ('A0','B','C','D')

or,  if the goal is to pick up the first-A, which may or may not be A0 then try something like this...

select * from
(select t.*, row_number(partition by substr(program_id,1,1) order by to_number(substr(program_id,2))) rn from your_table t
where program_id in ('B','C','D')
or (program_id like 'A%')
) where program_id in ('B','C','D') or rn = 1
0
 

Author Comment

by:Paul_ATL
ID: 37796816
SDSTUBER,

I think your suggestion is close to what I'm looking for.  Also, I think I over simplify my question too much, let me elaborate for more clarity.

- In the result, Program_ID is not exactly string 'A0', 'A1', 'B', . . .
  But rather the 'A0', 'A1', . . . 'A9' is part of the string
  if the result is 'xxxxx - A0 yyyyy' then I want this included in the result
  if it has 'A1', . . . 'A9' then I don't want them

- The other part that I want, B, C, D, . . .
  The problem is that I do not have a finite list of what they are. I mean, it is a long list.
  As long as I does not contain 'A1', . . .'A9' then I want them.

I guess the easiest way is to say if 'A1', . . .'A9' is part of the string in Program_ID, then I do not want them.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37796847
I'm sorry that just confused me.  :)

could you post sample data and expected output?
0
 
LVL 31

Expert Comment

by:awking00
ID: 37796873
I have a feeling that your example may be a little simplified and perhaps you could provide some concrete examples of the data and the expected output, but if all you just have a beginning letter that determines the group and only want the first member of that group, the following might work:
select program_id from
(select program_id, row_number() over (partition by substr(program_id,1,1) order by program_id) rn
where rn = 1;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37796909
it's that basically what I posted a half-hour ago?
0
 

Author Comment

by:Paul_ATL
ID: 37797009
SDSTUBER,

Here is the sample data. Basically, I want to add a WHERE statement to exclude any result rows that PROGRAM_ID has 'A1' or 'A2', . . . 'A9'
Sample-Data.xlsx
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37797060
rather than an inclusion list, I think you're looking for an exclusion list


try these....

select * from (yourquery)
where not regexp_like(program_id,'A[1-5]')


or

select * from (yourquery)
where program_id not like '%A1%'
or program_id not like '%A2%'
or program_id not like '%A3%'
or program_id not like '%A4%'
or program_id not like '%A5%'


or

select * from (yourquery)
where program_id not like '%A%'
or (program_id like '%A0%')
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37797074
you may want to include other parameters to narrow down the A*  strings.

Otherwise other A characters found in the string might create false matches.  if A will only show up when followed by 0-5 then the above should work fine

One final note,  middle-string matching is among the least efficient searching you can do, so hopefully the main query driving this performs well because the searching on the results isn't something you'll be able to index easily
0
 

Author Comment

by:Paul_ATL
ID: 37797461
thanks SDSTUBER

running it now, will let you know later this eveining (it's a large query with up to 1.2M rows in result).
0
 
LVL 31

Expert Comment

by:awking00
ID: 37797616
>>it's that basically what I posted a half-hour ago?<<
Not exactly. As I said I think the example may be simplistic. What if there is also an E0, E1, ...?
I suspect the results would only be the E0 record.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37797635
the idea was the same,  number each group so you can find the earliest one.
The part that is different is only the A numbers matter,  B,C,D  do not.  That's why I only use the rn check for the A's.  If there did exist an E group, we have no rules from the question to determine if they should be included at all or filtered, or if filtered in what way.

but,  it doesn't really matter now, with more feedback since those posts it's fairly apparent that numbering is probably not the way to go here.
0
 

Author Closing Comment

by:Paul_ATL
ID: 37824986
need to make some tweaking but take the principle from this suggestion.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37825029
why the  B?

penalty grades shouldn't be given unless you requested additional information that was not provided.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now