Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

criteria problem

Posted on 2002-07-25
4
393 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 200 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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