File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes Oracle select in view question (String function ? ) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle select in view question (String function ? )" Watch "Oracle select in view question (String function ? )" New topic

Oracle select in view question (String function ? )

Jeremy Wilson
Ranch Hand

Joined: Feb 18, 2003
Posts: 166
The sql view below does not do what I want it to and not sure which string function to use. The goal is to get the two columns with instr to only count the rows that the INSTR actually finds the string. Basically I am looking for a substring function that returns a boolean value and only count those when true. How I go about this in an oracle view.

Jeremy Wilson
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

You need a subquery for that. The instr function is the correct one to use. However, it returns a number (position of string.) A function returning a boolean wouldn't help you either though. Then count would return 0 (if no data), 1 (if all match or all don't match) or 2 (if there is some mix.)

What you really want to count is:
select count(*) from auditRecord where instr(ACTION,'LOG IN SUCCESS',1,1) >= 0

Similarly for the failed case. Then you can merge the subqueries into an extra level on the main one.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: Oracle select in view question (String function ? )
It's not a secret anymore!