T-SQL View issue

2327 views sql
-3

I'm working with a Business Objects developer to create a new report. He wants me to create a view that he can query. My problem is that I've successfully created a Query, and have verified that it works, but when I use it / modify it ever so slightly to create a view, it doesn't return the correct results. So my question is two-fold: 1. What am I missing? Why doesn't my view return the same results as my query? 2. Is anyone familiar with Business Objects, and can my query be configured in Business Objects? Our Business Objects developer claims it can't be done.

Here is my SQL query that works. Note the following: - It is basically a Union where I eliminate records from the second part of the Union if a matching record already exists in the first part (based on my key). - There is ONE input parameter, LOT, which is used three times. - Both parts of the Union are querying Views. That shouldn't matter.

SELECT [Lot]
      ,[Qty]
      ,[MOP]
      ,[MOP Revision]
      ,[G_MOP]
      ,[G_MOP Revision]
      ,[Scrap Code]
      ,[MOP Threshold]
      ,[Txn Date]
      ,[Qty Built]
      ,[Catalog]
      ,[Comments]
  FROM [MES_QAS].[MES].[V_BOBJ_SCRAP_MOP_HISTORY]
  where lot = 'TF1_80400_QA7'

  UNION

  (SELECT [Lot]
      ,[Qty]
      ,[MOP]
      ,[MOP Revision]
      ,[G_MOP]
      ,[G_MOP Revision]
      ,[Scrap Code]
      ,[MOP Threshold]
      ,[Txn Date]
      ,[Qty Built]
      ,[Catalog]
      ,[Comments]
  FROM [MES_QAS].[MES].[V_BOBJ_NO_SCRAP_ALL_MOP_HISTORY]
  where lot = 'TF1_80400_QA7'
  AND [LOT]+IsNull([MOP],[G_MOP]) NOT IN (SELECT [Lot] + IsNull([MOP],[G_MOP]) FROM [MES].[V_BOBJ_SCRAP_MOP_HISTORY] where lot = 'TF1_80400_QA7')
  )

  ORDER BY Lot, mop, g_mop

Next, I created a view as follows (I stripped out the LOT as a parameter):

CREATE VIEW [MES].[V_BOBJ_SCRAP_ALL_MOP_HISTORY] (
    "Lot"
   ,"Qty"
   ,"MOP"
   ,"MOP Revision"
   ,"G_MOP"
   ,"G_MOP Revision"
   ,"Scrap Code"
   ,"MOP Threshold"
   ,"Txn Date"
   ,"Qty Built"
   ,"Catalog"
   ,"Comments"
   ) 
AS

(
    (
    SELECT [Lot]
          ,[Qty]
          ,[MOP]
          ,[MOP Revision]
          ,[G_MOP]
          ,[G_MOP Revision]
          ,[Scrap Code]
          ,[MOP Threshold]
          ,[Txn Date]
          ,[Qty Built]
          ,[Catalog]
          ,[Comments]
      FROM [MES].[V_BOBJ_SCRAP_MOP_HISTORY] H1
      )
      UNION
      (
      SELECT [Lot]
          ,[Qty]
          ,[MOP]
          ,[MOP Revision]
          ,[G_MOP]
          ,[G_MOP Revision]
          ,[Scrap Code]
          ,[MOP Threshold]
          ,[Txn Date]
          ,[Qty Built]
          ,[Catalog]
          ,[Comments]
      FROM [MES].[V_BOBJ_NO_SCRAP_ALL_MOP_HISTORY] A
      WHERE [LOT]+IsNull([MOP],[G_MOP]) NOT IN (SELECT [Lot] + IsNull([MOP],[G_MOP]) FROM [MES].[V_BOBJ_SCRAP_MOP_HISTORY] H2)
      )
  )
;

So here's the problem: when I run the following query, I don't get the correct results back - I only get results from the top portion of the Union, not the bottom:

SELECT * FROM [MES].[V_BOBJ_SCRAP_ALL_MOP_HISTORY]
where lot = 'TF1_80400_QA7'

Again, my two questions are: 1. What am I missing - why doesn't my view work? Is there a way to fix it? 2. Can my first query be modeled in Business Objects?

Brian Palmer, Lakewood, Colorado

answered question

2 Answers

9

I think what you need to build is a table function, actually - there are some subtle differences in how you filter the view versus the first hard-coded SQL

i.e.

where lot = 'TF1_80400_QA7'
AND [LOT]+IsNull([MOP],[G_MOP]) NOT IN (SELECT [Lot] + IsNull([MOP],[G_MOP]) FROM 
[MES].[V_BOBJ_SCRAP_MOP_HISTORY] where lot = 'TF1_80400_QA7')

posted this
13

The problem is in your subselect in the view, because it's not filtering by lot.

posted this

Have an answer?

JD

Please login first before posting an answer.