Solved

Condition to file some string from query

Posted on 2012-04-02
14
378 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
How to increase the row limit in Jasper Server.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 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