Procedure that compares one view then another.

[rsbutterfly16]

hi guys i am trying to create a procedure that check if data in a column (NumberID) form view [ID CR Alert] exist in two different views i created. If it does'nt exist in view [Existing IDs] then to look in second view [Existing ShipTos], this is what i have so far...

in first view i need to commpare:
dbo.[ID CR Alert]. NumberID = dbo.[Existing IDs].[BillToID] -if it doesn't exist in this view then go to second view, if it does then do nothing:

dbo.[ID CR Alert]. NumberID = dbo.[Existing IDs].[BillToID]


CREATE PROCEDURE [ID CR Alert] AS


SELECT DISTINCT
TOP 100 PERCENT dbo.[ID CR Alert].ReferenceID, dbo.[ID CR Alert].DistributorID,
dbo.[ID CR Alert]. NumberID FROM dbo.[ID CR Alert] CROSS JOIN
dbo.[Existing MAS IDs]
WHERE (NOT EXISTS
(SELECT [BillTo]
FROM dbo.[Existing IDs]
WHERE dbo.[Existing IDs].[BillToID] = dbo.[ID CR Alert]. NumberID))

ORDER BY dbo.[ID CR Alert].ReferenceID
GO


then i have an HTML temmplate that selects the NumberIDs that do aren't in any of these two views and sends an email.
help please

 

[bborissov]

How about:

SELECT DISTINCT
       CrAlert.ReferenceID,
       CrAlert.DistributorID,
       CrAlert. NumberID
FROM  dbo.[ID CR Alert]  CrAlert
-- What do that CROSS JOIN here???
CROSS JOIN dbo.[Existing MAS IDs]
LEFT JOIN (SELECT [BillTo] AS BillTo
                  FROM dbo.[Existing IDs]
           UNION
           SELECT [BillTo] AS BillTo
                  FROM dbo.[Existing ShipTos]) Tbl1
ON CrAlert.BillToID = Tbl1.BillTo
WHERE Tbl1.NumberID IS NULL
ORDER BY CrAlert.ReferenceID

Try it first and than add it in SP

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[rsbutterfly16]

sorry i did'nt put the second view it is supposed to check.. I changed some things so here is what i have so far.

Original view(it has all the data)= [ID CR Alert]

my first view is supposed to check= BillTo
second view= ShipTo

SELECT DISTINCT
TOP 100 PERCENT dbo.[ID CR Alert].ReferenceID,
dbo.[ID CR Alert].DistributorID,
dbo.[ID CR Alert].NumberID

FROM dbo.[ID CR Alert] INNER JOIN dbo.BillTo ON
dbo.[ID CR Alert].NumberID = dbo.BillTo.[BillToID]
WHERE (NOT EXISTS
(SELECT [BillToID]
FROM dbo.[BillTo]
WHERE dbo.[BillTo].[BillToID] = dbo.[ID CR Alert].NumberID))

IF (dbo.[ID CR Alert].NumberID = NULL) THEN

SELECT DISTINCT TOP 100 PERCENT dbo.[ID CR Alert].ReferenceID, dbo.[ID CR Alert].DistributorID,
dbo.[ID CR Alert].NumberID

FROM dbo.[ID CR Alert] INNER JOIN dbo.ShipTo ON dbo.[ID CR Alert].NumberID = dbo.ShipTo.[ShipToID]

WHERE (NOT EXISTS (SELECT [ShipToID]
FROM dbo.[ShipTo]
WHERE dbo.[ShipTo].[ShipToID] = dbo.[ID CR Alert].NumberID)))

END IF
ORDER BY dbo.[ID CR Alert].ReferenceID

I am trying to do this in a view, but i keep getting an error with the word Then . help!

 

[SQLSister]

YOu can't use if in a view. If is procedural code. Views do not allow multiple steps.

"NOTHING is more important in a database than integrity." ESquared

 

[rsbutterfly16]

ok then can you help me put it in a procedure?

 

[bborissov]

That code doesn't make any sense:

SELECT DISTINCT
       TOP 100 PERCENT dbo.[ID CR Alert].ReferenceID,
dbo.[ID CR Alert].DistributorID,
dbo.[ID CR Alert].NumberID

FROM   dbo.[ID CR Alert]
[COLOR=red]
INNER JOIN dbo.BillTo ON 
      dbo.[ID CR Alert].NumberID = dbo.BillTo.[BillToID]
[/color]
WHERE     ([COLOR=red]NOT EXISTS[/color]
                 (SELECT     [BillToID]
                  FROM       dbo.[BillTo]
                  WHERE dbo.[BillTo].[BillToID] = dbo.[ID CR Alert].NumberID))

You always will get EMPTY result set.
INNER JOIN will try to get all records from [ID CR Alert] which have matching records in [BillTo] BUT you filter these records in your WHERE clause.

Try this in QA or SSMS:

SELECT DISTINCT
       CrAlert.ReferenceID,
       CrAlert.DistributorID,
       CrAlert. NumberID
FROM  dbo.[ID CR Alert]  CrAlert
LEFT JOIN (SELECT [BillTo] AS BillTo
                  FROM dbo.[Existing IDs]
           UNION
           SELECT [ShipTo] AS BillTo
                  FROM dbo.[Existing ShipTos]) Tbl1
ON CrAlert.BillToID = Tbl1.BillTo
WHERE Tbl1.NumberID IS NULL
ORDER BY CrAlert.ReferenceID

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[rsbutterfly16]

where does Tbl1 come from?