0

I have a view created as below

CREATE OR REPLACE VIEW ITEM_USAGE_V(LAST_USAGE_DATE, Item_Name, CODE) AS
   SELECT MAX (use_date) AS last_usage_date,
          COUNT (DISTINCT itnmed) AS Item_Name,
                bcde as code
   FROM (SELECT use_date, itnmed,bcde
             FROM item_usage_info iui
            WHERE EXISTS
                     (SELECT 1
                        FROM item_all
                       WHERE itnmed = iui.itnmed AND status = 1
                      ))
    group by bcde;

when i run the below query

select *
from ITEM_USAGE_V
where bcde='341232125421' and itnmed='dipos_container'

the behavior should be as below

SELECT 
     MAX (use_date) AS last_usage_date, 
     COUNT (DISTINCT itnmed) AS Item_Name, 
     bcde as code FROM 
         (SELECT use_date, 
                 itnmed,bcde 
           FROM  item_usage_info iui 
           WHERE EXISTS 
                 (SELECT 1 
                  FROM item_all 
                  WHERE itnmed = iui.itnmed 
                  AND status = 1 
                  AND itnmed <> iui.itnmed))  group by bcde

when the run query

select * from ITEM_USAGE_V where bcde='341232125421' and itnmed='dipos_container'

the behavior should be as original view.

How can i achieve this

Veverke
  • 9,208
  • 4
  • 51
  • 95
Madhu Velayudhan
  • 59
  • 1
  • 2
  • 8
  • 2
    Your question doesn't make any sense to me - you say when you run one query you want one behaviour, and when you run the same query again you want a different behaviour - and that different behaviour includes an additional clause in the correlated join conditions which directly contradicts a previous join condition? `where itnmed = iui.itnmed ... and itnmed != iui.itnmed`... um, you can't have it both ways at the same time! N.B. for your exists query, you should alias the item_all table and all references to its columns – Boneist Jun 29 '16 at 14:21
  • Where do you run the query with `bcde='341232125421' and itnmed='dipos_container'` ? – Grayson Jun 29 '16 at 14:24
  • 2
    Your question isn't clear. Please describe in detail the behavior you want. You are trying to show it with SQL, but the SQL isn't correct so we have no idea what results you want. – Bampfer Jun 29 '16 at 14:34
  • 1
    Also note: the second "SELECT 1 FROM item_all ..." won't ever return anything because it has two WHERE clauses that are opposites: "WHERE itnmed = iui.itnmed AND itnmed <> iui.itnmed". – Bampfer Jun 29 '16 at 14:35
  • actually i have a parser where the operator is is hard coded to equal (=), so when i call the view the query built is bcde='341232125421' and itnmed='dipos_container' but i need the output to be bcde='341232125421' and itnmed<>'dipos_container' – Madhu Velayudhan Jun 29 '16 at 14:51
  • I really don't understand your requirement. Why don't simply create two separate views and use one or the other when required ? Or why don't use two separate queries ? Why do you need a "parser" ? It seems to me that you overcomplicate a very simple task instead of keeping it KISS --> see this: https://en.wikipedia.org/wiki/KISS_principle – krokodilko Jun 29 '16 at 19:25
  • It *is* possible to force a query to behave completely different than intended, by using DBMS_ADVANCED_REWRITE like in my answer [here](http://stackoverflow.com/questions/37888050/oracle-select-returns-rows-but-select-count1-return-0). However, for rewrite equivalency Oracle will consider your two queries to be identical. You need to have more than just whitespace and case difference between the queries for that to work. – Jon Heller Jun 29 '16 at 21:15

0 Answers0