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(), '%'))
LVL 1
__Holly__Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
From MSDN:
<quote>
COALESCE
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.
</quote>

What are you trying to do?

Anthony
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
niklausjCommented:
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.
0
__Holly__Author Commented:
man are you sure you arent talking about the NullIf function, instead of the IsNull function?
0
niklausjCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.