Add attribute to view based on if another attribute is in a list-CodePudding

Lets say I have a view with a CommissionNumber and a CreationDate:

Select CommissionNumber, CreationDate
FROM dbo.MyOrders

The commissionNumber is just a varchar of size 12.

There's another table (let's name that dbo.CanceledOrders) with CommissionNumbers of orders that were recently canceled:

CREATE TABLE [dbo].[CanceledOrders] (
    [PK_ID] [int] IDENTITY(1,1) NOT NULL,
    [FK_VehicleFile] [int] NOT NULL,
    [CommissionNumber] [nvarchar](15) NULL,
    [CancelingUser] [nvarchar](50) NULL,
    [CancelationDate] [date] NULL,
    [ValidatingUser] [nvarchar](50) NULL,
    [ValidationDate] [date] NULL,
    [ReasonForCancelation] [nvarchar](500) NULL);

I would like to add to the above view an attribute that is true or false based on whether the commissionNumber of the view is also listed in that second table, which indicates, that this order was canceled. That is supposed to look something like that:

 Select CommissionNumber, CreationDate, IsCancelled as (case....
    FROM dbo.MyOrders

I can't get the syntax right. Can anybody help me?

CodePudding user response：

You can use an outer join and then check if the primary key on the joined table has a value or not which indicates if a join was found. You could get duplicate records if the same CommissionNumber value can exist on multiple records in the CanceledOrders table.

Select o.CommissionNumber, o.CreationDate, IIF(c.PK_ID IS NULL, 0, 1) AS IsCancelled
FROM dbo.MyOrders o LEFT OUTER JOIN dbo.CanceledOrders c ON o.CommissionNumber = c.CommissionNumber

CodePudding user response：

A simple Exists query should do the trick:

SELECT
    CommissionNumber, 
    CreationDate,
    CASE
        WHEN Exists
        (
            SELECT 1 
            FROM dbo.CanceledOrders As C 
            WHERE C.CommissionNumber = O.CommissionNumber
        ) THEN CAST(1 As bit)
        ELSE CAST(0 As bit)
    END As IsCancelled
FROM
    dbo.MyOrders As O

EXISTS (Transact-SQL)

CodePudding user response：

I would use Outer Apply like this:

CREATE View [dbo].[Test]
As
Select O.CommissionNumber, O.CreationDate, Case When C.CommissionNumber Is Not Null Then 1 Else 0 End IsCancelled
FROM dbo.MyOrders As O Outer Apply (Select Top 1 CommissionNumber From [dbo].[CanceledOrders] Where CommissionNumber=O.CommissionNumber) As C