• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle select in view question (String function ? )

 
Jeremy Wilson
Ranch Hand
Posts: 166
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34870
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeremy,
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic