CODEWITHSUNDEEP
sqlvieworacle11g
Incognito
Incognito

Reputation: 3094

Modifying the query for view

One of our views used to return a single row and a single value, which used to be either TRUE or FALSE. Due to certain data changes in the concerned tables, with the same criteria in WHERE clause as before, now we are getting two rows being returned from the system.

Let's assume the underlying table, test has two values as of now.

COLUMN
------
TRUE
FALSE

I can write a CASE statement to check the count of the records being returned from the current view, by encapsulating the entire code for view in an inner query, something like

SELECT CASE 
          WHEN count(a.val) > 1
             THEN 'TRUE'

          ELSE a.val
       END
FROM (
SELECT val FROM test) a;

What I am not able to do is, how to check for the records which are being returned by the inner query - I need to suppress the two records, and return only one record, as in if I am getting TRUE and FALSE as output, I should only return TRUE. In case, I am getting both records as TRUE or both as FALSe, I should return TRUE/FALSE respectively.

How can I get this done within the view?

Upvotes: 0

Views: 51

Answers (2)

Evgeni Dimitrov
Evgeni Dimitrov

Reputation: 22506

Cast the boolean to int and use max:

select max(cast(mybool as INT)) from MyView

Upvotes: 1

Rida BENHAMMANE
Rida BENHAMMANE

Reputation: 4129

Do as to return only the first row :

ELECT CASE 
          WHEN count(a.val) > 1
             THEN 'TRUE'

          ELSE a.val
       END
FROM (
SELECT val FROM test where rownum = 1) a;

Upvotes: 0

Related Questions