Oracle select in view question (String function ? )

[Jeremy Wilson]

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.

CREATE OR REPLACE VIEW LOGONREPORT
AS
SELECT USERNAME, 
  COUNT(*) ATTEMPTS, 
  COUNT(INSTR(ACTION,'LOG IN SUCCESS',1,1)) SUCCESS,
  COUNT(INSTR(ACTION,'LOG IN FAILED',1,1)) FAILED
FROM AUDITRECORD
GROUP BY USERNAME;

[Jeanne Boyarsky]

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.