Solved

criteria problem

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now