Solved

Condition to file some string from query

Posted on 2012-04-02
14
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 77

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 74

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

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

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

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 74

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
 
LVL 74

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 74

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 32

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 74

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 74

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 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