Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

criteria problem

Posted on 2002-07-25
4
Medium Priority
?
397 Views
Last Modified: 2008-03-06
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(), '%'))
0
Comment
Question by:__Holly__
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 800 total points
ID: 7179295
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
 
LVL 2

Expert Comment

by:niklausj
ID: 7179657
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
 
LVL 1

Author Comment

by:__Holly__
ID: 7180772
man are you sure you arent talking about the NullIf function, instead of the IsNull function?
0
 
LVL 2

Expert Comment

by:niklausj
ID: 7184795
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question