CODEWITHSUNDEEP
t-sqlstored-procedures
A.Chandler
A.Chandler

Reputation: 23

SQL WHERE clause only returning one of the results when there is 2 expected results

I have a Where clause in Stored Procedure that only returns one of 2 possible returns in a where clause 

GO
/****** Object:  StoredProcedure [dbo].[RPT_HC_ShiftRates]    Script Date: 01/23/2018 10:51:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure  [dbo].[RPT_HC_ShiftRates]

AS --variables
BEGIN
select c.Company
, c.OurReference
, Shifts1 as [Split shifts]
, Shifts2 as [Invoiced at one rate]
, s.PortalApproval
, s.Rates
, s.Breaks
, c.ClientID


from ClientSectorDefinedColumns s
join clients c on c.ClientID = s.ClientID
left OUTER JOIN ClientBranches AS CB ON C.ClientID = CB.BranchClientId

where (Shifts1 = 'y' or Shifts2 = 'y' or s.Rates is not null or s.Breaks is not null)
and (c.OurReference like 'P00%' or c.OurReference = 'HEA%')  
and C.ClientID in (select objectid from SectorObjects where SectorId in (58, 59, 60, 61, 62, 63, 64, 65, 66, 47 ))

END

The line I suspect is causing the issue is : 

and (c.OurReference like 'P00%' or c.OurReference = 'HEA%')

This is only returning LIKE 'P00%' whereas it should be returning one or another.

I am new to SQL and only completed 20761B with little luck finding an answer in my training material.

Thanks for any assistance!

Upvotes: 1

Views: 230

Answers (1)

Ruud Helderman
Ruud Helderman

Reputation: 11018

Apparently you had the intention of doing a pattern match. In the first part of the condition you did it the right way:

c.OurReference like 'P00%'

but in the second part, you accidentally used = instead of LIKE:

c.OurReference = 'HEA%'

A mistake like this is easily overlooked. The right-hand side of the condition does contain a percent sign (%), but in the absence of LIKE, it is not interpreted by SQL Server as a wildcard. Instead, the character is matched 'as-is'. The condition will only succeed if column OurReference is literally HEA%. In your database, that is never the case; no records will match that part of the condition.

Upvotes: 1

Related Questions