December 8, 2009 at 3:54 pm
When using Select ... Case WHEN ColA IS NULL Then ColB ELSE ColC AS D FROM ..., this to build a VIEW: when selecting from the view with a WHERE on D, is not filtering - all records are returned when only 1 should be returned. When changing view to ISNULL(ColA,0)>0 then subsequent Select... Where against the view filter as expected. ColA is an int.
Any ideas why IS NULL does not seem to be responding? Thanks!
December 8, 2009 at 4:31 pm
Please provide the DDL for the base table and the view (CREATE TABLE and CREATE VIEW statements), sample data for the base table in a readily consummable format (one that can be cut/paste/run in SSMS), expected results based on sample data, and finally your code that you have written.
December 9, 2009 at 6:55 am
Defintely need more information. At the least the full query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 10, 2009 at 9:19 am
Just included the underlying table containing the column in question, as well as the view. There are several more tables creating other columns in the view but they didnt seem necessary for this question. Either way, please let me know if more info is needed, and thanks so much for any insight!
CREATE TABLE [dbo].[RepairOrder](
[RepairOrderID] [int] IDENTITY(1,1) NOT NULL,
[BranchID] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
[RepairOrderNumber] [int] NULL,
[OriginalRepairOrderID] [int] NULL,
[RepairOrderStatusID] [int] NOT NULL,
[RepairOrderInvoiceID] [int] NULL,
[UnitInventoryID] [int] NULL,
[MeterReadingID] [int] NULL,
[ECMMeterReadingID] [int] NULL,
[CustomerID] [int] NOT NULL,
[CompanyName] [varchar](100) NOT NULL,
[CustomerPORequired] [bit] NOT NULL,
[CustomerPONumber] [varchar](30) NULL,
[OwningCustomerID] [int] NOT NULL,
[OwningCompanyName] [varchar](100) NOT NULL,
[UseLocalSalesTax] [bit] NOT NULL,
[UseLocalSalesTaxOverride] [bit] NOT NULL,
[CreditReserveAmount] [decimal](19, 2) NULL,
[TaxBodyID] [int] NOT NULL,
[TaxCodeID] [int] NOT NULL,
[OpenDate] [datetime] NOT NULL,
[PromisedDate] [datetime] NULL,
[CompletionDate] [datetime] NULL,
[ArrivalDate] [datetime] NULL,
[IsContract] [bit] NOT NULL,
[BranchPaymentMethodID] [int] NOT NULL,
[ROPrefix] [varchar](2) NULL,
[AddUserID] [int] NOT NULL,
[AddDate] [datetime] NOT NULL,
[UpdateUserID] [int] NOT NULL,
[LastUpdate] [datetime] NOT NULL,
[OpenDateTimeZone] [decimal](4, 2) NULL,
[PromisedDateTimeZone] [decimal](4, 2) NULL,
[CompletionDateTimeZone] [decimal](4, 2) NULL,
[ArrivalDateTimeZone] [decimal](4, 2) NULL,
[AddDateTimeZone] [decimal](4, 2) NULL,
[LastUpdateTimeZone] [decimal](4, 2) NULL,
[AmountReceived] [decimal](19, 2) NULL,
[CheckNumber] [varchar](35) NULL,
[CreditCardNumber] [varchar](35) NULL,
[DownLoadROSweepToCatepillar] [datetime] NULL,
[ROTotal] [decimal](19, 2) NULL,
[LocalPurchaseOrderID] [int] NULL,
[ROTaxTotal] [decimal](19, 2) NULL,
[CashPaymentMethodID] [int] NULL,
[isROLocked] [bit] NULL,
[CreditAuthorizationAmount] [decimal](19, 2) NULL,
[CreditAuthorizationNumber] [varchar](15) NULL,
[CreditAuthorizationOverrideDays] [int] NULL,
[CreditAccountNumber] [varchar](25) NULL,
[DriverNumber] [varchar](10) NULL,
[SalespersonName] [varchar](50) NOT NULL,
[CustomerTaxNumber] [varchar](20) NOT NULL,
[CreditCardExpiration] [datetime] NULL,
[CreditAuthorizationExpiration] [datetime] NULL,
[LockUserID] [int] NULL,
[LockDate] [datetime] NULL,
[LockTimeZone] [decimal](4, 2) NOT NULL,
[AlternateAccounting] [varchar](25) NOT NULL,
[AlternateAccountingCustomerID] [int] NULL,
[BillingCustomerTax] [decimal](19, 2) NOT NULL,
[UserRepairOrderStatusID] [int] NULL,
[ControlNumberRepairOrderID] [int] NULL,
[OwningWorkPhone] [varchar](30) NULL,
[ControlNumber] [varchar](20) NULL,
[RemanVendor] [varchar](10) NULL,
[RemanPartNumber] [varchar](50) NULL,
[RemanDescription] [varchar](50) NULL,
[RemanQuantity] [int] NULL,
[RemanCost] [decimal](19, 3) NULL,
[RemanCoreVendor] [varchar](10) NULL,
[RemanCorePartNumber] [varchar](50) NULL,
[RemanCoreDescription] [varchar](50) NULL,
[RemanCoreCost] [decimal](19, 3) NULL,
[RemanAverageCost] [decimal](19, 3) NULL,
[RemanOldCost] [decimal](19, 3) NULL,
[RemanOldAverageCost] [decimal](19, 3) NULL,
CONSTRAINT [PK_RepairOrder] PRIMARY KEY NONCLUSTERED
(
[RepairOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
CREATE VIEW [dbo].[vw_TechnicianView]
AS
SELECT
RO.RepairOrderID,
TL.BranchID AS BranchID,
RO.BranchID as roBranchID1,
TL.DepartmentID AS DepartmentID,
D1.Department AS Department,
RO.OwningCompanyName,
CASE
WHEN RO.RepairOrderID IS NULL THEN TL.BranchID
ELSE RO.BranchID
END ROBranchID
FROM Technician T
LEFT JOIN TechnicianLocation TL
ON T.TechnicianID = TL.TechnicianID
LEFT JOIN Department D1
ON TL.DepartmentID = D1.DepartmentID
LEFT JOIN OpenBarcodeTime OBT
ON T.TechnicianID = OBT.TechnicianID
LEFT JOIN RepairOrder RO
ON OBT.RepairOrderID = RO.RepairOrderID
WHERE T.Inactive = 0
When running the SELECT including the OwningCompanyName the View brings back more results than the WHERE specifies. Such as 1, 2, 10 etc.
SELECT ROBranchID, OwningCompanyName FROM vw_TechnicianView WHERE ROBranchID = 1
Data Returned
1 TRANSPORT COMPANY
1 PIPELINE COMPANY
10 NULL
1 USED INVOICE
2 NULL
3 NULL
12 NULL
If you remove the OwningCompanyName and only select the ROBranchID in the column list then the WHERE filters correctly only returning values of 1.
SELECT ROBranchID, FROM vw_TechnicianView WHERE ROBranchID = 1
1
1
1
1
1
1
Also, if the ROBranchID CASE statement within the view is as follows it returns the incorrect results based off the WHERE clause when selecting from the view.
CASE
WHEN RO.RepairOrderID IS NULL THEN TL.BranchID
ELSE RO.BranchID
END AS ROBranchID,
1 TRANSPORT COMPANY
1 PIPELINE COMPANY
10 NULL
1 USED INVOICE
2 NULL
3 NULL
12 NULL
But if the CASE statement is changed to the following below then the WHERE clause filters correctly on ROBranchID when selecting from the view even when both columns (ROBranchID and OwningCompanyName) are specified within the SELECT.
CASE
WHEN ISNULL(RO.RepairOrderID, 0) > 0 THEN RO.BranchID
ELSE TL.BranchID
END ROBranchID,
1 COMBINED COMPANY
1 PIPELINE COMPANY
1 NULL
1 NULL
1 NULL
1 NULL
December 10, 2009 at 9:42 am
Please post the Graphical Execution Plans for both statements. Basically it looks like the Optimizer it taking 2 paths to get the data based on the columns requested. By changing the path the behavior of the query is changing because of the OUTER JOIN's.
All the table structures with some test data like outlined in the first article linked in my signature would also help us do some testing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 10, 2009 at 11:46 am
And please use the [ code ] tags.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply