Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

data lookup in Oracle - need suggestions

We have a problem with a view.  Call it the 'ItemView'.  It is a multi-table join in which there are frequently values 'missing'.  People get upset because item number X is not in the view, yet when we analyze the view, we find it is just because one or more of the view conditions are not met. So, I've been asked to create an exceptions report, in which I list the identiying key for all values not found in the view, along with a list of all unmet view conditions.  I don't have it all together yet, and I am hoping for some advice on bringing this together. The view is attached, and this is what I have so far --  

This is how I find  my 'missing' values:
SELECT COUNT(*) FROM  
(
  SELECT IMITM, IMLITM, IMUPMJ
  FROM CRPDTA.f4101   ---<<<<<<   this is the primary base table in the view
  MINUS
  SELECT Item_Number, second_item_number, date_updated
  FROM CRPDTA.ITEMVIEW  --<<<<<<<<   this is the view
);


This is my working table:
CREATE TABLE CRPDTA.ItemViewExceptions
(
  IMITM number(22) NOT NULL,
  IMLITM nchar(50) NOT NULL,
  IMUPMJ number(22) NULL,
  RUNDATE date DEFAULT sysdate, ---- <<< just a default for when the data is collected into the working table
  CONSTRAINT pk_ItemViewExceptions PRIMARY KEY (IMITM,IMLITM)  
);
COMMENT ON TABLE CRPDTA.ItemViewExceptions IS 'Working table for ItemView Exceptions report.';


These are my conditions:
ABAT1 = V         -- comes from CRPDTA.F0101..ABAT1 in the join
ABAC26 = 1      -- comes from CRPDTA.F0101..ABAC26 in the join
IVXRT = VN        -- comes from CRPDTA.F4104..IVXRT in the join
IIHZDC between 1 and 9 but not 1.4   -- comes from CRPDTA.F4908..IIHZDC in the join
IBMCU = 50      -- comes from CRPDTA.F4102.IBMCU in the join


It is JDE data, so I am dealing with the julian date (yuck!).  I believe I want to run the top query above to identify my exceptions, and write them into my working table.  Ideally, this is done for one day at a time -- either today or yesterday -- but I don't have the julian date figured out yet.

...and this is where I am stuck.  Not just the julian date, but more importantly, how do I then walk through the join with each IMTM,IMLITM pair, and identify which conditions are not being met?


I really need an assist on this.  Any Oracle Expert advice is hugely appreciated.
ItemView_07282016.sql
Avatar of flow01
flow01
Flag of Netherlands image

-- results per  IMITM, IMLITM
SELECT IMITM, IMLITM, COUNT(*) FROM  
(
  SELECT IMITM, IMLITM, IMUPMJ
  FROM CRPDTA.f4101   ---<<<<<<   this is the primary base table in the view
  MINUS
  SELECT Item_Number, second_item_number, date_updated
  FROM CRPDTA.ITEMVIEW  --<<<<<<<<   this is the view
);
GROUP BY  IMITM, IMLITM
ORDER BY  IMITM, IMLITM

-- julian date , something like
select to_char( sysdate, 'j' )  from dual
Creating a "working table" to hold these exceptions (either just for a day, or permanently) may or may not be a good idea.  I like the suggestion from flow01 to just find these dynamically.  But, this assumes that only one person (or a scheduled job?) will need to do this each day, and anyone who needs the data will have access to the results of this one run.  If you have a different situation, where multiple people need access to these results, and not all at the same time, it may make sense (from a performance perspective) to create a "working table" to hold these exceptions for the current day.  I don't know if it adds value in your organization to keep these records long-term, or not.

Now, identifying why a particular record is missing from the view, is a somewhat-complex task, because the view definition is not so simple.  (I have to admit that I'm not an expert with the ANSI-standard join syntax that this view uses.  I'm much more familiar with traditional Oracle join syntax.)  I think you mainly need to check this condition: "FROM  PRODDTA.F4101 MAINTBL INNER JOIN (..." because all of the other joins appear to be outer joins, and they will not prevent a row from being included.

From a performance perspective, this view is very ugly with conditions like these:
TRIM(F4102.IBMCU) = '50'
      AND TRIM(F0101.ABAC26) = '1'
ON TRIM(vnm.ABAN8) = TRIM(SECONDTBL.VENDORAN8)  
      ON TRIM(ssd.DRKY) = TRIM(SECONDTBL.IMSRP4)
      ON TRIM(sd.DRKY) = TRIM(SECONDTBL.IMSRP1)
Every time you use "TRIM" (or any other SQL operator, including: upper, lower, to_char, to_date, etc.) on a database column name, you prevent Oracle from using an index on that column, unless you have a function-based index on that column that exactly matches that query condition.  But, you weren't asking for performance help on this view.  You were just asking for help identifying why some records were excluded from the view.
Avatar of dbaSQL

ASKER

Thank you both very much.  I've only just now seen the posts, will be back to review asap.  One change I wanted to input is that the day does not matter anymore.  I just want to identify the pairs that do not exist in the view (which I can do already w/the minus statement), and then I need to figure out how to identify the view conditions which are not being met.  That is the most critical piece.
Avatar of dbaSQL

ASKER

>>I think you mainly need to check this condition: "FROM  PRODDTA.F4101 MAINTBL INNER JOIN (..." because all of the other joins appear to be outer joins, and they will not prevent a row from being included.

Do you have any suggestion for how to perform the lookup, once I have the exceptions to review?
check if select count(*) from PRODDTA.F4101 MAINTBL WHERE MAINTBL.IMITM =  yourimitmvalue AND MAINTBL.IMLITM  = 'yourimtlm_value'
delivers a count other then 0 to insure that where gets the correct result
then
start with changing
            WHERE MAINTBL.MAINTBL.IMSTKT in ('S','A','K','O','U')
to
            WHERE MAINTBL.IMITM =  yourimitmvalue AND MAINTBL.IMLITM  = 'yourimtlm_value'
                AND MAINTBL.IMSTKT in ('S','A','K','O','U')
then start commenting out existing where conditions that are not needed for joining
 (for examle)
            WHERE MAINTBL.IMITM =  yourimitmvalue AND MAINTBL.IMLITM  = 'yourimtlm_value'
--                AND MAINTBL.IMSTKT in ('S','A','K','O','U')
or
                        WHERE
                              TRIM(F4102.IBMCU) = '50'
--                              AND TRIM(F0101.ABAC26) = '1'
when the output changes from no rows to 1 or more rows you are on track
-- when no succes  continue to skip joins completely (for example vendor), you will have to comment out selected colums from those join-tabels to
Avatar of dbaSQL

ASKER

Thank you, flow01, but I'm not sure what you mean by 'start with changing' and 'start commenting'.  Can you clarify?  

Or this piece --  can you provide an example, or more clarification?
>>>when the output changes from no rows to 1 or more rows you are on track
-- when no succes  continue to skip joins completely (for example vendor), you will have to comment out selected colums from those join-tabels to
He is talking about changing the query in the view definition by commenting out some of the conditions, so the missing records are not filtered out.  The only safe way to do this is either in a test system, or if you have to do this in production, make a copy of this view with a different view name, then start modifying the query for that new view, until the missing records are included.  This will be an multi-step, manual process, that you may have to repeat a number of times:
1. comment out a join condition in the view
2. rebuild the view that way
3. query the revised view to see if the missing records are included yet
4. if not, comment out another join conditions, and repeat
Avatar of dbaSQL

ASKER

My output should be someting like this:

IMITM / IMLITM           UnmetCondition                                                                     CurrentValue
AA  / BB                        MAINTBL.MAINTBL.IMSTKT in ('S','A','K','O','U')                      B
                                      IBMCU = 50                                                                                  49
AA / CC                         MAINTBL.MAINTBL.IMSTKT in ('S','A','K','O','U')                      X
                                      IBMCU = 50                                                                                   1
........
.....


What I must do is load the table of exceptions, and then walk through the IMITM/IMLITM pairs selecting the values that do not meet the join condtion, and then returning the above output with whatever values exist.   If it were SQL Server, I believe I could use something like this CASE statement:.


SELECT a.IMITM, a.IMLITM, UnmetCondition = CASE
  WHEN MAINTBL.IMSTKT NOT IN ('SA','A','K','O','U') THEN 'MAINTBL.MAINTBL.IMSTKT in ('S','A','K','O','U')
  WHEN CRPDTA.F0101.ABAT1 <> 'V' THEN 'ABAT1 = V'
  WHEN CRPDTA.FO1O1.ABAC26 <> 1 THEN 'ABAC26 = 1'
  WHEN CRODTA.F4104.IVXRT <> 'VN' THEN 'IVXRT = VN'
  WHEN CRPDTA.F4908.IIHZDC NOT BETWEEN 1 AND 9 THEN 'IIHZDC BETWEEN 1 AND 9'
  WHEN CRPDTA.F4102.IBMCU <> 50 THEN 'IBMCU = 50' END,
CurrentValue = CASE
      WHEN MAINTBL.IMSTKT NOT IN ('SA','A','K','O','U') THEN MAINTBL.IMSTKT,
      WHEN CRPDTA.F0101.ABAT1 <> 'V' THEN CRPDTA.F0101.ABAT1
        WHEN CRPDTA.FO1O1.ABAC26 <> 1 THEN CRPDTA.F0101.ABAC26
      WHEN CRPDTA.F4104.IVXRT <> 'VN' THEN CRPDTA.F4104.IVXRT
      WHEN CRPDTA.F4908.IIHZDC NOT BETWEEN 1 AND 9 THEN CRPDTA.F4908 END
FROM exceptionsTable a JOIN MAINTABLE b
  ON a.IMITM = b.IMITM
  AND a.IMLITM = b.IMLITM
 

I'm not sure if it's 100%, but I need assistance to do the same type construct with Oracle.   This is what I am trying to get assistance with.
Avatar of dbaSQL

ASKER

Anybody?  I don't believe the CASE is joined completely yet, given the attached view.  Any Oracle Expert advice on completing the CASE, and performing the CASE in Oracle, would be very much appreciated.
ItemView_07282016.sql
"What I must do is load the table of exceptions...".
This sounds like a SQL Server-style approach to a data problem.  In Oracle, this is usually *NOT* the most efficient way to solve a problem.

I would describe the problem this way:
"What I must do [first] is find the set of exceptions..."

This can be done best in Oracle either in a view, or in a sub-query or in a cursor loop in a procedure.  Then, getting the "CASE" logic to work as intended is still a challenge.  (I'm out of time for today, or at least for an hour or so.  I'll try to check back this evening or tomorrow morning, if no one else has helped you in the meantime.)
Avatar of dbaSQL

ASKER

>>This sounds like a SQL Server-style approach to a data problem.
That may be the case, but it works.  I have a view and a table -- I need to find all IMITM, IMLITM pairs from the table that don't exist in the view AND I need to then list reasons why they are not in the view.  

I have a nifty little minus statement that identifies all IMITM, IMLITM pairs.  So, I dump them into a working table and find out which view conditions are not being met.  If there is a more Oracle way to do it, I am all ears.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Can you create a really simple test case with sample data and expected results?

I have a pretty good idea what you are looking for but I'm having trouble coming up with a test case I'm comfortable with.

Here is what I dummied up for a test.  It changes your requirements a little bit.  I have a column for each rule violation instead of a list.  If you want a list, I can unpivot it.

Take a look at what I used and please change the tables, data and views to better match your desired results and we can go from there.

drop table tab1 purge;
create table tab1 (test_col varchar2(20), abat1 char(1), abac26 number);

insert into tab1 values('Do not return','A',1);
insert into tab1 values('Return','V',1);
insert into tab1 values('Do not return','V',2);
commit;

drop table tab2 purge;
create table tab2 (test_col varchar2(20), abat1 char(1), abac26 number);
insert into tab1 values('Return','X',1);
insert into tab1 values('Return','X',2);
commit;


create or replace view tab1_vw as select r1.test_col, t1.abat1, t1.abac26
from tab1 t1
	join tab2 t2 on t1.abat1=t2.abat1 and t1.abac26=t2.abac26 and
;

select test_col, abat1, abac26,
		case when abat1!='V' then 'abat1 is not V' end abat1_check,
		case when abac26!=1 then 'abac26 is not a one' end abac26_check
from tab1
where test_col='Return' and
(abat1,abac26) in
(
	select abat1, abac26 from tab1
	minus
	select abat1, abac26 from tab1_vw
)
/

Open in new window

Avatar of dbaSQL

ASKER

Am out of the house, but I will get back to this as soon as I can.  Thank you!!
"...it works.  I have a view and a table ..."
Sure, and if performance is no problem, that is likely an easy-to-code and easy-to-understand approach.  If you ever need maximum performance though from Oracle, that is not the most-efficient way to get the job done in Oracle.  Basically, in Oracle, don't ever create a "temp" table dynamically in a procedure, and don't use a "work" or "staging" table unless you have tried other approaches and this turns out to be the simplest approach to code for.  In some cases, a "global temporary table" can be useful in Oracle, but these are *NOT* created dynamically.  They are created just once, so the table and column definitions are permanent.  It is just the contents that are temporary (and visible only in the database session that inserts them, even if other sessions are using the same "global temporary table" at the same time).

For maximum performance in Oracle, you usually just query the base table(s) or views(s) directly, with no temporary or staging table needed.  And, you don't need to worry about read-consistency even if your query or procedure takes some minutes to run and other users are changing some records in the base table at the same time.  Oracle will give you a "read-consistent" view of the records, unless your query or procedure is very slow *AND* there are many other transactions affecting the same records during that time.  If that happens you may get a "snapshot too old" error in your query or procedure.  This usually means that a query or procedure is poorly-written (that is, not taking advantage of existing indexes) or that an index may need to be added to support the query or procedure.
Avatar of dbaSQL

ASKER

>>For maximum performance in Oracle, you usually just query the base table(s) or views(s) directly, with no temporary or staging table needed.  
Alright.  Can you suggest the right way to do that with the task at hand?  This has become horribly critical, and I am running into one syntax error after another, as I am trying to use the CASE statement.
>>Can you suggest the right way to do that with the task at hand?

I tried.  I just needed input from you to add to the sample test case I posted to make it more realistic of what you need.
Avatar of dbaSQL

ASKER

sorry, slightwv, i was still looking at your sample.  the tab1 and tab2 in your example -- can you tell me which tables in my actual design these are intended to mimic?  I need to be sure I'm reading it correctly, to edit it usefully for you.
>> can you tell me which tables in my actual design these are intended to mimic?  

None.  I was trying to create as simple of a test case as I could and still have the basics that meet your requirements.

You have a view that joins some tables.  You have a base table.  You do a minus query to get records missing.  Then based on the where clauses inside the view, why did the record fail to show up in the view.

To demonstrate that, you should only need two base tables for a join example and a view.

That is what I was trying to set up.
Avatar of dbaSQL

ASKER

ok.  let me see what i can do.
I think your query syntax needs to look more like this (with the name of the column after the "case" and the conditions for it):

SELECT a.IMITM, a.IMLITM, CASE when ... end UnmetCondition,
   CASE when ... end CurrentValue
 FROM exceptionsTable a JOIN MAINTABLE b
   ON a.IMITM = b.IMITM
   AND a.IMLITM = b.IMLITM
Avatar of dbaSQL

ASKER

>>To demonstrate that, you should only need two base tables for a join example and a view.
slightwv, this is the part I am unsure on.  as you can see from my attachment, the view is HUGE.  How can I substantially demonstrate the requirement with a three column view?
>>How can I substantially demonstrate the requirement with a three column view?

What is the technical difference between a view with a 10 table join and a view with a two table join?

The requirement really doesn't have to do with the complexity of the view itself.  It shouldn't make any difference if you have 50 where clauses that affect the results or two.

If I can demonstrate what you want using two where clause conditions, can't you expand that to the rest?

You minus a table against a view and get some results.  I don't see where the number of where conditions changes this.
Avatar of dbaSQL

ASKER

I hear you, slightwv, but the damn joins in the view are not on tables as much as they are on data collections, if that's the right label.  Makes it difficult to put a test together.  The coluns in your example -- abat1, abac26 -- these are not joinable columns.  They are referenced in where clauses of the joins.  

see here:

FROM CRPDTA.F4101 MAINTBL
      INNER JOIN (
                        SELECT DISTINCT
                        F4102.IBITM,
                        F4104.IVITM,
                        F4104.IVAN8,
                        F4104.IVXRT,
                        F4102.IBMCU,
                        F4102.IBLITM,
                        F4102.IBSTKT,
                        F4102.IBLNTY,
                        F4102.IBVEND,
                        F0101.ABALPH,
                        F4104.IVCITM,
                        F0101.abac26
                  FROM
                        CRPDTA.F4102 LEFT OUTER JOIN CRPDTA.F0101
                           ON F4102.IBVEND      = F0101.ABAN8 LEFT OUTER JOIN CRPDTA.F4104
                              ON F4104.IVITM       = F4102.IBITM
                              AND F4104.IVAN8 = F4102.IBVEND
                  WHERE
                        TRIM(F4102.IBMCU) = '50'
                        AND   TRIM(F0101.ABAC26) = '1'      ) VAN8

or this one:

LEFT JOIN
      (SELECT      ABALPH,
                  ABAN8
       FROM  CRPDTA.F0101
       WHERE  ABAT1='V'
       AND    ABAC26='1') vnm

so i'm trying to create and populate two small, usable test tables in the example with JOINable columns... and it's not very obvious or easy at all.  still on it.
Trust me,  I know it's not obvious.  ;)

I tried the best I could based on the information you provided to date.  I focused on the output of a MINUS query and the 'rules' behind it.

I was hoping it was close enough for you to take it and apply it to what you have.
Avatar of dbaSQL

ASKER

I feel like it is close.  Almost like it's right there.  but i just can't find it yet.  and everybody wants this yesterday.  I will have something as soon as I can, slightwv.  thank you very much for seeing where I'm at with this.
Avatar of dbaSQL

ASKER

I may have found it.  the right table relationship to help you help me with this.  but i have to freeking leave the house for a bit. I will be back on this tonight, and send an update as soon as i can.
Avatar of dbaSQL

ASKER

Much closer, slightwv.  The final select after the view is not quite the output I am looking for, but it could just be that my sample data is flawed.    I really, reeeeallly need to bring this to completion.  As you know, the real view is insane.  If  you could help me to make this example perform as expected, I am sure I can turn this around on the real objects.  Please review as soon as you can, and let me know what you think.



drop table CRPDTA.tab1 purge;
CREATE TABLE CRPDTA.tab1 (IMITM NUMBER,IMLITM NCHAR(50), IMSTKT NCHAR(2));
INSERT INTO CRPDTA.tab1 VALUES(86973,'NO80-4231462','O');
INSERT INTO CRPDTA.tab1 VALUES(86978,'N090-3663204','O');
INSERT INTO CRPDTA.tab1 VALUES(82916,'TYB4SELU4','S');
COMMIT;
SELECT * FROM CRPDTA.tab1;


drop table tab2 purge;
CREATE TABLE CRPDTA.tab2 (ABAN8 NUMBER, ABAT1 NCHAR(6),ABAC26 NCHAR(6));
INSERT INTO CRPDTA.tab2 VALUES(195956,'B','1');
INSERT INTO CRPDTA.tab2 VALUES(123316,'V','1');
INSERT INTO CRPDTA.tab2 VALUES(900,'BP','');
INSERT INTO CRPDTA.tab2 VALUES(7043,'E','');
COMMIT;
SELECT * FROM CRPDTA.tab2;

--drop table tab3 purge;
CREATE TABLE CRPDTA.tab3 (IBITM NUMBER,IBLITM NCHAR(50),IBMCU NCHAR(24),IBVEND NUMBER);
INSERT INTO CRPDTA.tab3 VALUES(86973,'NO80-4231462','1400',195956);
INSERT INTO CRPDTA.tab3 VALUES(86978,'N090-3663204','50',7043);
INSERT INTO CRPDTA.tab3 VALUES(82916,'TYB4SELU4','50',900);
COMMIT;
SELECT * FROM CRPDTA.tab3;


CREATE OR REPLACE VIEW CRPDTA.testing_vw AS
SELECT t1.IMITM,t1.IMLITM,t1.IMSTKT,t3.IBMCU,t3.IBVEND,t2.ABAN8,t2.ABAT1,t2.ABAC26
FROM CRPDTA.tab1 t1 JOIN CRPDTA.tab3 t3
  ON t1.IMITM = t3.IBITM
  AND t1.IMLITM = t3.IBLITM LEFT OUTER JOIN CRPDTA.tab2 t2
    ON t3.IBVEND = t2.ABAN8;
SELECT * FROM CRPDTA.testing_vw;


SELECT ABAN8, ABAT1, ABAC26,
            CASE WHEN ABAT1!='V' THEN 'abat1 is not V' END ABAT1_CHECK,
            CASE WHEN ABAC26!=1 THEN 'abac26 is not a one' END ABAC26_CHECK
FROM CRPDTA.tab2
WHERE (ABAT1,ABAC26) IN (
    SELECT ABAT1,ABAC26
    FROM CRPDTA.tab2
    minus
    SELECT ABAT1,ABAC26
    FROM CRPDTA.testing_vw
  )
Can you provide the expected results from that sample data?
Avatar of dbaSQL

ASKER

Well, I tried and tried with that last set of data, but I only returned one record from the findal output.  So, this is a redo.  More matching data, more unmatching data.  I also added more conditions to the WHERE clause in the LEFT OUTER on the view.

drop table CRPDTA.tab1 purge;
CREATE TABLE CRPDTA.tab1 (IMITM NUMBER,IMLITM NCHAR(50), IMSTKT NCHAR(2));
INSERT INTO CRPDTA.tab1 VALUES(86973,'NO80-4231462','O');
INSERT INTO CRPDTA.tab1 VALUES(86978,'N090-3663204','O');
INSERT INTO CRPDTA.tab1 VALUES(82916,'TYB4SELU4','S');
INSERT INTO CRPDTA.tab1 VALUES(82916,'TYB4SELU4','R');
INSERT INTO CRPDTA.tab1 VALUES(86978,'N090-3663204','L');
COMMIT;
SELECT * FROM CRPDTA.tab1;


drop table CRPDTA.tab2 purge;
CREATE TABLE CRPDTA.tab2 (ABAN8 NUMBER, ABAT1 NCHAR(6),ABAC26 NCHAR(6));
INSERT INTO CRPDTA.tab2 VALUES(195956,'V','1');
INSERT INTO CRPDTA.tab2 VALUES(123316,'V','1');
INSERT INTO CRPDTA.tab2 VALUES(900,'V','1');
INSERT INTO CRPDTA.tab2 VALUES(900,'V',' ');
INSERT INTO CRPDTA.tab2 VALUES(123316,'V','2');
INSERT INTO CRPDTA.tab2 VALUES(123316,'X','3');
COMMIT;
SELECT * FROM CRPDTA.tab2;

drop table CRPDTA.tab3 purge;
CREATE TABLE CRPDTA.tab3 (IBITM NUMBER,IBLITM NCHAR(50),IBMCU NCHAR(24),IBVEND NUMBER);
INSERT INTO CRPDTA.tab3 VALUES(86973,'NO80-4231462','50',195956);
INSERT INTO CRPDTA.tab3 VALUES(86978,'N090-3663204','50',123316);
INSERT INTO CRPDTA.tab3 VALUES(82916,'TYB4SELU4','50',900);
INSERT INTO CRPDTA.tab3 VALUES(82916,'TYB4SELU4','1',900);
INSERT INTO CRPDTA.tab3 VALUES(82916,'TYB4SELU4','11',900);
COMMIT;
SELECT * FROM CRPDTA.tab3;

DROP VIEW CRPDTA.testing_vw;
CREATE OR REPLACE VIEW CRPDTA.testing_vw AS
SELECT t1.IMITM,t1.IMLITM,t1.IMSTKT,t3.IBMCU,t3.IBVEND,t2.ABAN8,t2.ABAT1,t2.ABAC26
FROM CRPDTA.tab1 t1 JOIN CRPDTA.tab3 t3
  ON t1.IMITM = t3.IBITM
  AND t1.IMLITM = t3.IBLITM LEFT OUTER JOIN CRPDTA.tab2 t2
    ON t3.IBVEND = t2.ABAN8
WHERE TRIM(t3.IBMCU) = '50'
AND TRIM(t2.ABAC26) = '1'
AND t1.IMSTKT IN('S','A','K','O','U');
SELECT * FROM CRPDTA.testing_vw;


This is my attempted query to output the desired rows, but it fails with 'FROM keyword not found where expected'  Error at line 62, column 100.  

SELECT t1.IMITM,t1.IMLITM,CASE WHEN t2.ABAT1 <> 'V' THEN 'abat1 is not V'
                               WHEN t2.ABAC26 <> '1' THEN 'abac26 is not 1'
                               WHEN t1.IMSTKT NOT IN('S','A','K','O','U') THEN 'invalid imstkt' END = "Unmet Condition",
  CASE WHEN t2.ABAT1 <> 'V' THEN t2.ABAT1
       WHEN t2.ABAC26 <> '1' THEN t2.ABAC26
       WHEN t1.IMSTKT NOT IN('S','A','K','O','U') THEN t1.IMSTKT END = "Current Value"
FROM CPRDTA.tab1 t1 JOIN CRPDTA.tab3 t3
  ON t1.IMITM = t3.IBITM
  AND t1.IMLITM = t3.IBLITM LEFT OUTER JOIN CRPDTA.tab2 t2
    ON t3.IBVEND = t2.ABAN8
WHERE (t2.ABAT1,t2.ABAC26) IN (
    SELECT ABAT1,ABAC26
    FROM CRPDTA.tab2
    minus
    SELECT ABAT1,ABAC26
    FROM CRPDTA.testing_vw
  )

Now, with tab1 as my primary, if I run this statement, it tells me I should be outputting results:

SELECT * FROM CRPDTA.tab1;
SELECT * FROM CRPDTA.testing_vw;

This is the output:
IMITM                  IMLITM                                             IMSTKT
---------------------- -------------------------------------------------- ------
86973                  NO80-4231462                                       O      
86978                  N090-3663204                                       O      
82916                  TYB4SELU4                                          S      
82916                  TYB4SELU4                                          R      
86978                  N090-3663204                                       L      

IMITM                  IMLITM                                             IMSTKT IBMCU                    IBVEND                 ABAN8                  ABAT1  ABAC26
---------------------- -------------------------------------------------- ------ ------------------------ ---------------------- ---------------------- ------ ------
86973                  NO80-4231462                                       O      50                       195956                 195956                 V      1      
86978                  N090-3663204                                       O      50                       123316                 123316                 V      1      
82916                  TYB4SELU4                                          S      50                       900                    900                    V      1      

With the above data, this is the output I would expect:

IMITM / IMLITM                       UnmetCondition                              CurrentValue
82916 / TYB4SELU4                   invallid IMSTKT                                    R
86978 / N090-3663204             invalid IMSTKT                                     L



Problems:    A., The select fails with the FROM error.  I can't find the flaw
                      B., This does not account for the unmet conditions in tab2 -- I need all IMITM/IMLITM values returned in the output, with any unmet join conditions.  This isn't all of them, by any means  -- if you looked at the view -- but it is all of them in this example.


CASE WHEN t2.ABAT1 <> 'V' THEN 'abat1 is not V'
                               WHEN t2.ABAC26 <> '1' THEN 'abac26 is not 1'
                               WHEN t1.IMSTKT NOT IN('S','A','K','O','U') THEN 'invalid imstkt' END = "Unmet Condition",
Avatar of dbaSQL

ASKER

I've tried this, to reference the other unmet join conditions, but it still fails with the FROM clause ot being found where expected.  I'm still working it.


SELECT t1.IMITM,t1.IMLITM,CASE WHEN TRIM(t2.ABAT1) <> 'V' THEN 'abat1 is not V'
                               WHEN TRIM(t2.ABAC26) <> '1' THEN 'abac26 is not 1'
                               WHEN TRIM(t3.IBMCU) <> '50' THEN 'ibmcu is not 50'
                               WHEN TRIM(t1.IMSTKT) NOT IN('S','A','K','O','U') THEN 'invalid imstkt' END = "Unmet Condition",
  CASE WHEN TRIM(t2.ABAT1) <> 'V' THEN t2.ABAT1
       WHEN TRIM(t2.ABAC26) <> '1' THEN t2.ABAC26
       WHEN TRIM(t3.IBMCU) <> '50' THEN t3.IBMCU
       WHEN t1.IMSTKT NOT IN('S','A','K','O','U') THEN t1.IMSTKT END = "Current Value"
FROM CPRDTA.tab1 t1 JOIN CRPDTA.tab3 t3
  ON t1.IMITM = t3.IBITM
  AND t1.IMLITM = t3.IBLITM LEFT OUTER JOIN CRPDTA.tab2 t2
    ON t3.IBVEND = t2.ABAN8
WHERE (t2.ABAT1,t2.ABAC26) IN (
    SELECT ABAT1,ABAC26
    FROM CRPDTA.tab2
    minus
    SELECT ABAT1,ABAC26
    FROM CRPDTA.testing_vw
  )
Avatar of dbaSQL

ASKER

ok.  got the FROM error.  still working it, though.  back shortly.  i hope
Avatar of dbaSQL

ASKER

OK.  This logic compiles and returns the data in the attached screenshot.  i'm still reviewing to see if it is as expected for the test data I used.

SELECT t1.IMITM,t1.IMLITM,CASE WHEN TRIM(t2.ABAT1) <> 'V' THEN 'abat1 is not V'
                               WHEN TRIM(t2.ABAC26) <> '1' THEN 'abac26 is not 1'
                               WHEN TRIM(t3.IBMCU) <> '50' THEN 'ibmcu is not 50'
                               WHEN TRIM(t1.IMSTKT) NOT IN('S','A','K','O','U') THEN 'invalid imstkt' END "Unmet Condition",
  CASE WHEN TRIM(t2.ABAT1) <> 'V' THEN t2.ABAT1
       WHEN TRIM(t2.ABAC26) <> '1' THEN t2.ABAC26
       WHEN TRIM(t3.IBMCU) <> '50' THEN t3.IBMCU
       WHEN t1.IMSTKT NOT IN('S','A','K','O','U') THEN t1.IMSTKT END "Current Value"
FROM CRPDTA.tab1 t1 JOIN CRPDTA.tab3 t3
  ON t1.IMITM = t3.IBITM
  AND t1.IMLITM = t3.IBLITM LEFT OUTER JOIN CRPDTA.tab2 t2
    ON t3.IBVEND = t2.ABAN8
WHERE (t2.ABAT1,t2.ABAC26) IN (
    SELECT ABAT1,ABAC26
    FROM CRPDTA.tab2
    minus
    SELECT ABAT1,ABAC26
    FROM CRPDTA.testing_vw
  )
results.png
Avatar of dbaSQL

ASKER

Ok.  If I add the GROUP BY, I believe my results are correct.  Not 100% yet on the one that is NULL in the output.

SELECT t1.IMITM,t1.IMLITM,CASE WHEN TRIM(t2.ABAT1) <> 'V' THEN 'abat1 is not V'
                               WHEN TRIM(t2.ABAC26) <> '1' THEN 'abac26 is not 1'
                               WHEN TRIM(t3.IBMCU) <> '50' THEN 'ibmcu is not 50'
                               WHEN TRIM(t1.IMSTKT) NOT IN('S','A','K','O','U') THEN 'invalid imstkt' END "Unmet Condition",
  CASE WHEN TRIM(t2.ABAT1) <> 'V' THEN t2.ABAT1
       WHEN TRIM(t2.ABAC26) <> '1' THEN t2.ABAC26
       WHEN TRIM(t3.IBMCU) <> '50' THEN t3.IBMCU
       WHEN t1.IMSTKT NOT IN('S','A','K','O','U') THEN t1.IMSTKT END "Current Value"
FROM CRPDTA.tab1 t1 JOIN CRPDTA.tab3 t3
  ON t1.IMITM = t3.IBITM
  AND t1.IMLITM = t3.IBLITM LEFT OUTER JOIN CRPDTA.tab2 t2
    ON t3.IBVEND = t2.ABAN8
WHERE (t2.ABAT1,t2.ABAC26) IN (
    SELECT ABAT1,ABAC26
    FROM CRPDTA.tab2
    minus
    SELECT ABAT1,ABAC26
    FROM CRPDTA.testing_vw
  )
GROUP BY t1.imitm, t1.imlitm,
  CASE WHEN TRIM(t2.ABAT1) <> 'V' THEN 'abat1 is not V'
                               WHEN TRIM(t2.ABAC26) <> '1' THEN 'abac26 is not 1'
                               WHEN TRIM(t3.IBMCU) <> '50' THEN 'ibmcu is not 50'
                               WHEN TRIM(t1.IMSTKT) NOT IN('S','A','K','O','U') THEN 'invalid imstkt' END,
  CASE WHEN TRIM(t2.ABAT1) <> 'V' THEN t2.ABAT1
       WHEN TRIM(t2.ABAC26) <> '1' THEN t2.ABAC26
       WHEN TRIM(t3.IBMCU) <> '50' THEN t3.IBMCU
       WHEN t1.IMSTKT NOT IN('S','A','K','O','U') THEN t1.IMSTKT END
group-by-results.png
Avatar of dbaSQL

ASKER

If you look in the original view, you can see that these are the real tables that my test tables are based on:

tab1 = F4101 - IM
tab2 = F0101 - AB
tab3 = F4102 - IB

Unless I've  missed one, these are the only other tables that are used in the view, in addition to the above three:

F4104 - IV
F4908 - II
F0005 - DR

slightwv, would I need to account for every table in the original view, just as i have in my testing sample?  Do you even feel  my testing sample is worthwhile?  I will stop posting and wait to hear back from you.
>>would I need to account for every table in the original view

Possibly?  Not sure I completely understand everything you've posted after the initial sample data.

>>Do you even feel  my testing sample is worthwhile?  

I can't answer that.  I can take it, try to figure it all out and try to produce your expected results.

>>With the above data, this is the output I would expect:

I can get your expected results just going to the base tables.

I don't need the minus query.  I'm not sure what the actual benefit is.

If you know all the core requirements for each base table, just write the test cases for each table individually.  You can likely UNION them all together for a single result set.

SELECT imitm || ' / ' || imlitm, case when imstkt not IN('S','A','K','O','U') then 'invalid IMSTKT' end unmetcondition, imstkt current_value
FROM tab1
where imstkt not IN('S','A','K','O','U');

Open in new window


You can add additional case statements and additional clauses in the WHERE to perform all the tab1 data checks at the same time.
Avatar of dbaSQL

ASKER

>>I can get your expected results just going to the base tables.    
FABULOUS!  
>>I don't need the minus query.  I'm not sure what the actual benefit is.
the  minus is because I thought it was necessary, but temp/working tables are not the best, especially if they can be avoided.

I need to finish this today, slightwv.  i know you hear that a lot, but it is very true.  vacation is tomorrow, and I do not want to spend it doing this.  can you help?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL

ASKER

Yes.  You have given me very usable examples.  The problem is that I must get this done today, and I don't have the concept nearly as solid as you do. Regardless, you have given me excellent insight. I will work through it and let you know.  Thank you, slightwv.
Try the example below.  It is untested because I don't have anything to test against.

If you only have one condition to test against then you don't need the CASE statement in the select.

You just need to keep going through all your WHERE clauses in the view and keep adding to the tests.

You are checking the negatives of what the where clause in the view is checking.

If the where clause uses an '=' condition, you check the '!=' condition.  If it uses 'IN', you use 'NOT IN'.

SELECT imitm || ' / ' || imlitm, 'invalid IMSTKT' unmetcondition, imstkt current_value FROM MAINTBL where imstkt not IN('S','A','K','O','U')
union all
SELECT IIITM || '/' || IIHZDC, 'invalid IIHZDC', IIHZDC FROM F4908 WHERE IIHZDC NOT BETWEEN '1' AND '9' AND IIHZDC ='1.4'
/

Open in new window

Avatar of dbaSQL

ASKER

Hi slightwv.  Unfortunately, I still don't have it. Tthe sample you've given me does a direct select against the MAINTBiL with the IMSTKT NOT IN list.  The problem is that this table is only the 'MAINTBL' in the view -- where it is then inner joined to the subquery with numerous other joins and conditions6.  So I am not sure how this example will work.

The same thing goes for the second select in the UNION ALL -- it's not just an explicit reference to F4908 where IIHZDC BETWEEN '1' AND '9' AND IIHZDC <> '1.4'.  It is that data also joined to VAN9 (which is yet another subquery), where VAN9.IMITM = F4908.IIITM = VAN9.IMITM.  

I don't know how to construct the unioned query with the additional criteria that is part of the view.  I really hope that makes sense.
OK, then write the query to join F4908 to VAN9 and do the check.

I've provided the basic concept.  I cannot write copy/paste code that magically works because I don't have your tables and data nor do I fully understand all the join criteria/requirements.
Avatar of dbaSQL

ASKER

I am not looking to copy/paste the magic code, slightwv.  I am looking to understand your suggestions so that I can make this happen.  I will let  you know how things come together.
I was going to try to guess but I think I now see the issue you were trying to explain.

Things are so heavily nested that going against the base tables is difficult.

I think where I'm confused is the original MINUS query you had.  It was a base table versus the view.

I'm still thinking it should be possible to break it down into a few smaller queries to uncover the missing values.
Avatar of dbaSQL

ASKER

>>Things are so heavily nested that going against the base tables is difficult.
big time.  

I am very close.  I will let you know as soon as I have it.
Avatar of dbaSQL

ASKER

I said I was close -- I have approached it much like your suggestion, with the unioned selects -- I have four of the different join connditions functioning well, as you can see in the attached, but I am having a terrible time with the remaining conditions.  For example, the code posted below all by itself fails with the attached screenshot.  I'm not sure if you can help me slightwv, but I am seriously hopeful.  if I could finish this, I need only wrap a create view onto it, and I can be done.  Again, the attached functions well, but it does not have all of the condition checks i n there.  I am trying to add the one below, and failing miserably.

Do you see the problem?

---- IIHZDC BETWEEN '1' AND '9' AND	IIHZDC !='1.4'    --F4908..IIHZDC
				SELECT
								MAINTBL.IMLITM					AS IMLITM,
								MAINTBL.IMITM					AS IMITM,
            		'IIHZDC 1 to 9 but not 1.4'	AS UnmetCondition,
                HAZD.IIHZDC AS CurrentValue,
                MAINTBL.IMSTKT
				FROM PRODDTA.F4101 MAINTBL INNER JOIN (
            SELECT DISTINCT
								F4102.IBITM,
								F4104.IVITM,
								F4104.IVAN8,
								F4104.IVXRT,
								F4102.IBMCU,
								F4102.IBLITM,
								F4102.IBSTKT,
								F4102.IBLNTY,
								F4102.IBVEND,
								F0101.ABALPH,
								F4104.IVCITM,
								F0101.abac26
						FROM PRODDTA.F4102 LEFT OUTER JOIN PRODDTA.F0101 
              ON F4102.IBVEND      = F0101.ABAN8 LEFT OUTER JOIN PRODDTA.F4104 
                ON F4104.IVITM       = F4102.IBITM
                AND F4104.IVAN8 = F4102.IBVEND
            WHERE TRIM(F4102.IBMCU) = '50'
						AND   TRIM(F0101.ABAC26) = '1'
					) VAN8 
            ON VAN8.IBITM = MAINTBL.IMITM
						AND VAN8.IVXRT = 'VN' LEFT OUTER JOIN 
                  (
                        SELECT DISTINCT
													F4102.IBITM,
													F4104.IVITM,
													F4104.IVAN8,
													F4104.IVXRT,
													F4102.IBMCU,
													F4102.IBLITM,
													F4102.IBSTKT,
													F4102.IBLNTY,
													F4102.IBVEND,
													F0101.ABALPH,
													F4104.IVCITM,
													F0101.abac26
												FROM PRODDTA.F4102 LEFT OUTER JOIN PRODDTA.F0101 
                          ON F4102.IBVEND      = F0101.ABAN8 LEFT OUTER JOIN PRODDTA.F4104 
                            ON F4104.IVITM       = F4102.IBITM
														AND F4104.IVAN8 = F4102.IBVEND
                        WHERE TRIM(F4102.IBMCU) = '50'
												AND   TRIM(F0101.ABAC26) = '1' ) VAN9 
                          ON  VAN9.IBITM = MAINTBL.IMITM
                          AND VAN9.IVAN8 = VAN8.IVAN8
                          AND VAN9.IVXRT = 'UP')
                WHERE IMSTKT IN ('S','A','K','O','U')
                ) SECONDTBL
        LEFT OUTER JOIN
        (
          SELECT IIITM,
                IIHZDC,
                '1' AS SET_TO_1
      	 FROM   PRODDTA.F4908
        WHERE  IIHZDC NOT BETWEEN '1' AND '9'
        AND	IIHZDC != '1.4') HAZD ON HAZD.IIITM = SECONDTBL.IMITM

Open in new window

syntax-error.png
ItemViewExceptions.sql
Avatar of dbaSQL

ASKER

Though I'm sure you know this, each of the checks that I am doing are coming from the view definition, slightwv.    Maybe you can see what I'm doing wrong in the one that is failing.
Avatar of dbaSQL

ASKER

Maybe not quite as well as I thought.  There are numerous dupes in the output.  I will put an outer around the whole thing and group it.  Hopefully that's not in Oracle's book of don't do's.
Avatar of dbaSQL

ASKER

Please see the attached, slightwv.  I thought that it was correct, but now I am not so sure.  The count that I listed in the very first post returned about 2700 IMITM/IMLITM values from the main table that did not also exist in the view.  The attached query, however, is returning more than 3600 where the unmet condition is 'Not in branch 50'.  I have been scrolling through the results in the sql developer window, I don't see any other values from the other condition checks in the output.  I have to question whether the logic is correct, because that is the first one in my sequence of checks.  

Basically, I'm returning data, but as of yet I don't see any records for conditions other than the first one, where not in branch 50.  

I still haven't gotten my other conditions into the query, but I need to confirm this query is correct before adding any other components to the collection.
working.sql
too-many-first-condition.png
Avatar of dbaSQL

ASKER

slightwv, can you at least tell me why HAZD.IIHZDC is an invalid identifier in this piece?  The HAZD.IIHZDC is being selected in the 4th line of code, and HAZD alias is defined in the very last line.  I am absolutely pulling my hair out on this.  so close, yet so freeking far

				SELECT
								MAINTBL.IMLITM					AS IMLITM,
								MAINTBL.IMITM					AS IMITM,
								'IIHZDC NOT BETWEEN 1-9 OR <> 1.4' AS UnmetCondition,
                HAZD.IIHZDC AS CurrentValue
				FROM PRODDTA.F4101 MAINTBL
					INNER JOIN (
							SELECT DISTINCT
								F4102.IBITM,
								F4104.IVITM,
								F4104.IVAN8,
								F4104.IVXRT,
								F4102.IBMCU,
								F4102.IBLITM,
								F4102.IBSTKT,
								F4102.IBLNTY,
								F4102.IBVEND,
								F0101.ABALPH,
								F4104.IVCITM,
								F0101.ABAC26,
                F0101.ABAT1
							FROM PRODDTA.F4102 LEFT OUTER JOIN PRODDTA.F0101 
                ON F4102.IBVEND = F0101.ABAN8 LEFT OUTER JOIN PRODDTA.F4104 
                  ON F4104.IVITM = F4102.IBITM
                  AND F4104.IVAN8 = F4102.IBVEND
              WHERE TRIM(F4102.IBMCU) = '50'
							AND TRIM(F0101.ABAC26) = '1' ) VAN8 
               ON VAN8.IBITM = MAINTBL.IMITM
						   AND VAN8.IVXRT = 'VN' LEFT OUTER JOIN (
											SELECT DISTINCT
													F4102.IBITM,
													F4104.IVITM,
													F4104.IVAN8,
													F4104.IVXRT,
													F4102.IBMCU,
													F4102.IBLITM,
													F4102.IBSTKT,
													F4102.IBLNTY,
													F4102.IBVEND,
													F0101.ABALPH,
													F4104.IVCITM,
													F0101.ABAC26
											FROM PRODDTA.F4102 LEFT OUTER JOIN PRODDTA.F0101 
                        ON F4102.IBVEND = F0101.ABAN8 LEFT OUTER JOIN PRODDTA.F4104 
                          ON F4104.IVITM = F4102.IBITM
													AND F4104.IVAN8 = F4102.IBVEND
                      WHERE TRIM(F4102.IBMCU) = '50'
											AND TRIM(F0101.ABAC26) = '1' ) VAN9 
                        ON VAN9.IBITM = MAINTBL.IMITM
                        AND VAN9.IVAN8 = VAN8.IVAN8
                        AND VAN9.IVXRT = 'UP'
            WHERE MAINTBL.IMSTKT IN ('S','A','K','O','U');
) SECONDTBL
LEFT OUTER JOIN
	(SELECT IIITM,
	 	    IIHZDC,
			'1' AS SET_TO_1
	 FROM   PRODDTA.F4908
	 WHERE  IIHZDC NOT BETWEEN '1' AND '9' 
   AND	IIHZDC !='1.4') HAZD ON HAZD.IIITM = SECONDTBL.IMITM

Open in new window

Sorry for not getting back to this.  I was swamped yesterday.

>>least tell me why HAZD.IIHZDC is an invalid identifier in this piece?

What you posted is incomplete because I don't see the starting paran for:  ) SECONDTBL

I'm assuming this is part of another inline view.  If it is, that is the cause of the error.  You define HAZD at the same level as SECONDTBL.  You cannot reference that inside the inline view that defines SECONDTBL.

Simplified, you are trying this:

select 'hello' from (
     select secondtbl.myalias from dual
) firsttbl join
(
select 'world' myalias from dual
) secondtbl
on ...
Avatar of dbaSQL

ASKER

>>What you posted is incomplete because I don't see the starting paran for:  ) SECONDTBL
What I've posted is the whole piece of code.  If I am missing something, maybe that's why it's failing.

The view that I've attached 'works' but it is incomplete.  I've got 5 condition checks in there, but there are two remaining which I am failing  miserably to get into the construct.  One of them is the one with HAZD that is failing with invalid identifier that I last posted.  The other is where ABAT1 = V.  They are both in the bottom of the view I previously posted, and here they are again - the 9th and 10th lines for the 1st one, the 18th and 19th lines for the second on:

		) SECONDTBL LEFT OUTER JOIN (
				SELECT 
					IIITM,
	 				IIHZDC,
					'1' AS SET_TO_1
				FROM
					PRODDTA.F4908
				WHERE
					IIHZDC BETWEEN '1' AND '9'
					AND	IIHZDC !='1.4') HAZD 
			ON HAZD.IIITM = SECONDTBL.IMITM LEFT JOIN (
				SELECT	
					ABALPH,
					ABAN8
				FROM   
					PRODDTA.F0101
				WHERE  
					ABAT1='V'
					AND ABAC26='1') vnm
					  ON TRIM(vnm.ABAN8) = TRIM(SECONDTBL.VENDORAN8) LEFT JOIN PRODCTL.F0005 ssd 
						ON TRIM(ssd.DRKY) = TRIM(SECONDTBL.IMSRP4)
						AND ssd.DRSY = '41'
						AND ssd.DRRT = 'S4' LEFT JOIN PRODCTL.F0005 sd  
						  ON TRIM(sd.DRKY) = TRIM(SECONDTBL.IMSRP1)
						  AND sd.DRSY = '41'
						  AND sd.DRRT = 'S1' LEFT JOIN PRODCTL.F0005 gd  
							ON TRIM(gd.DRKY) = (TRIM(SECONDTBL.IMSRP1)||TRIM(SECONDTBL.IMSRP2))
							AND gd.DRSY = '55'
							AND gd.DRRT = 'SX' LEFT JOIN PRODCTL.F0005 cd  
							  ON TRIM(cd.DRKY) = TRIM(SECONDTBL.IMSRTX)
							  AND cd.DRSY = '55'
							  AND cd.DRRT = 'SX';

Open in new window



Rather than creating a procedure to identify which imitm/imlitm pairs in the maintbl do not exist in the view, i just wanted to create a view of them, so they can be visible at all times, without running some ugly code.  That is the goal of the attached, but again, I am missing two conditions, and every darned attempt to write them in fails with invalid identifier and/or the sql command not being ended properly.
vw_ItemViewExceptions.sql
The code in the block is incomplete.

If the code in the .sql attachment works, then I'm not sure what you want me to look at.  I don't know how to put those two together the way you are trying.

The error above, I explained.  It is a scope issue.  You are trying to reference a value that isn't visible in the scope you are trying it in.

If you can post the complete code that is generating an error, I can try to help with the syntax.  I'm not sure I can help with the logic.
Maybe it's time to review the business problem you are trying to solve here, and consider a different approach or solution.  I understand the basic problem is: identifying items in a base table that aren't in a complex view that includes the base table.

Your idea of using a different view to identify these items, plus include the reason they aren't in the original view is a nice idea, that in theory, is possible in Oracle and would have the advantage of being queryable on demand.  In practice however, this new view becomes complex to write, because the original view is rather complex and you need to include most (or all?) of that complexity plus the reverse of most of those conditions to determine why a particular item isn't included in the original view.

Instead of developing one extremely-complex view that can support queries on demand, and include the reason for why an item is missing, would it be acceptable to run a procedure or a report once per day (or at some other reasonable interval) and put the results into a reporting table or a file that can be viewed by users on-demand?  This may be an option that is easier to write and may be simpler to understand, troubleshoot and/or modify if needed.
Avatar of dbaSQL

ASKER

>>If the code in the .sql attachment works, then I'm not sure what you want me to look at.  I don't know how to put those two together the way you are trying.
I should have voiced myself differently.  The previously attached view does run, but it is incomplete.  There are two conditions that I still need to check.  That is what I would like your help with, slightwv.  I've attached two scripts -- one for each of the remaining condition checks.  

The AVAT1 not = V script runs, but it outputs records where ABAT1 = V, which is exaclty what we're checking the opposite of.  I only want to see values where AVAT1 <> V.  See the attached screenshot.

The hazd.IIHZDC does not run.  It will not compile, and tells me that HAZD.IIHZDC is an invalid identifier.  



also, I just want to explain what you probably already see -- I am approaching each statement like you initially suggested -- where AVAT1 = V, I'm looking where AVAT1 <> V.  Where IIHZDC BETWEEN 1 AND 9, I'm looking for values where IIHZDC NOT BETWEEN 1 and 9.  As per your suggestion, I am trying to write test cases for each join condition, and UNION ALL them together for the single result set.  The previously attached vw_ItemViewExceptions is that single result set, minus two conditions.  Those are the ones I am struggline with, and that I have attached to this post.


Finally, the customer wants a view.  They do not want a report once daily, they want a view.
AVAT1-not---V.sql
hazd.IIHZDC-not-between-1-and-9.sql
not-supposed-to-return-Vs.png
Avatar of dbaSQL

ASKER

>>I would write my tests against the base tables and UNION them all together.
That is what I have done, but unfortunately, the unioned statement still is not usable.  I don't want to leave this open indefinitely.  I appreciate your input.  Thank you.