• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

criteria problem

here is my criteria:
WHERE     (qb.BehaviorID LIKE COALESCE (dbo.fnGetFrmAdvOptions_filBehavior(), '%')) AND
                      (qb.CategoryID1 LIKE COALESCE (dbo.fnGetFrmAdvOptions_filCategory(), '%'))

i am having problems because it is trying to force the % into  a numeric-- and it cant..

here is the full sql

SELECT     qb.COrder, qb.BOrder, qb.Category, qb.Behavior, sum2.SumOfSafes1 AS SumOfSafes, sum2.SumOfConcerns1 AS SumOfConcerns,
                      sum2.CountOfBehaviorID1 AS Sample, sample1.CountOfBehaviorID1 AS SampleTot,
                      sum2.SumOfSafes1 / NULLIF (sum2.SumOfSafes1 + sum2.SumOfConcerns1, 0) AS pSafe
FROM         dbo.qryBeh() qb LEFT OUTER JOIN
                      dbo.rpqOverview_Sample1() sample1 ON qb.BehaviorID = sample1.BehaviorID LEFT OUTER JOIN
                      dbo.rpqOverview_sum2() sum2 ON qb.BehaviorID = sum2.BehaviorID
WHERE     (qb.BehaviorID LIKE COALESCE (dbo.fnGetFrmAdvOptions_filBehavior(), '%')) AND
                      (qb.CategoryID1 LIKE COALESCE (dbo.fnGetFrmAdvOptions_filCategory(), '%'))
  • 2
1 Solution
Anthony PerkinsCommented:
From MSDN:
Returns the first nonnull expression among its arguments.
All expressions must be of the same type or must be implicitly convertible to the same type.

What are you trying to do?

If you want to use like with an Int column you have to either rearange the query or convert to varchar

drawback of converting is that the int will be compared litteraly and doe to the function index on id col wont be used.

but it seems to me that you want all id's if the function returns null right?
then use

where (dbo.fnGetFrmAdvOptions_filBehavior()is null OR  (qb.CategoryID1 = ISNULL(dbo.fnGetFrmAdvOptions_filCategory(), 0))  ' Debending on ansi settings you need the isnull function because of null propagation, the null replacement value doesn't matter because when function returns null all values are selected anyway.
__Holly__Author Commented:
man are you sure you arent talking about the NullIf function, instead of the IsNull function?
where part:
dbo.fnGetFrmAdvOptions_filBehavior() is null
    will return all data if function returns Null

(qb.CategoryID1 = ISNULL(dbo.fnGetFrmAdvOptions_filCategory(), 0))

If function returns null, this will be treated as 0 to avoid  the  where fn is Null or fn = Null structure
because fn = Null will depending on ANSI Settings either return True or Null if it returns Null it'll invalidate the whole whereclause leading to no rows selected instead of all rows selected.
If function Returns an IDnr, ISNULL(fn, 0) will return the IDnr and compare it against the field.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now