MS SQL Server :: Where Clause Filter

How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim

View Replies !

Filter Criteria - Temp Table Join Or Where Clause?
I have a set of udf's dealing that return a one column table of valuesparsed from a comma delimeted string.For example:CREATE FUNCTION [dbo].[udf_filter_patient](@patient_list varchar(2000))RETURNS @patient TABLE(patient_id int)ASBEGINinsert into @patientselect patient_id from patient-- parse @patient_list stuff excludedRETURNENDI have come up with the following two schemes to use these udfs. Theseexamples are obviously simplified, and I have a handful of stored proceduresthat will use between 10 or more of these filters. If the two areequivalent, I prefer Method 2 because it makes for much neater SQL whenusing many filter criteria.So my question is, will one noticebly outperform the other? Or is there abetter way in which to filter on a list of criteria?Method 1 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASCREATE TABLE #patient(patient_id int)INSERT INTO #patientSELECTpatient_idFROMdbo.udf_filter_patient( @patient_list )SELECT*FROMopen_billsINNER JOIN #patient on #patient.patient_id = open_bills.patient_idGOMethod 2 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASSELECT*FROMopen_billsWHEREopen_bills.patient_id IN ( SELECT patient_id FROMdbo.udf_filter_patient( @patient_list ) )GOThanks for the help!Chris G

View Replies !

Should We See Replication Filter Where Clause Text In Profiler TextData On SQL Server 2005?

We have Merge Replication publications for SQL Server 2005 Compact Edition subscribers.
Some articles have filter statements that send rows to multiple subscribers, based on the value of Host_Name() supplied at run-time.

Our publications work for most subscribers, but we have at least one subscriber who downloads too many rows from one of the filtered tables.

When we run the Select SQL from the article's Filter statement it returns the intended 4 rows for this subscriber.
We cut and pasted the filter statement into query analyzer, substituted the subscriber's value for Host_Name(), executed the statement, and got the proper 4 rows for this subscriber in the results.

But when this subscriber syncs her Compact Edition database it downloads 10 rows - the proper 4 rows that the filter statement should pass, plus 6 other rows that she should not download.
Our hypothesis is that the Filter statement is not properly applied to the article when this subscriber syncs.
Other subscribers get the proper rows when they sync, so the publication's filter statement works in some cases, for some values of Host_Name().

We'd like to see the application of the filter statement at run-time (sync-time), but we have not found the text of the filter statement in SQL Profiler output. Should we expect to see the text of the filter statement in SQL Profiler output?
Is there a better way to debug this error?

FYI, here's the text of the article filter statement:

SELECT <published_columns> FROM [dbo].[TBL_USER] WHERE user_sys_id in (

select u.user_sys_id

from tbl_user u

join tbl_territory t on u.territory_gid = t.territory_gid

where t.terr_no_id like (

select

case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())

when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())

when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())

end

)

)

And here's the statement we ran from Query Analyzer:

declare @id varchar(10)

select @id = 'aultnc'

SELECT * FROM [dbo].[TBL_USER] WHERE user_sys_id in (

select u.user_sys_id

from tbl_user u

join tbl_territory t on u.territory_gid = t.territory_gid

where t.terr_no_id like (

select

case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)

when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)

when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)

end

)

)

Thanks

View Replies !

And/or Filter Field Not Enabled In The Group Filter Tab
Howdy,

I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.

PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?

The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)

View Replies !

Automatic Select Filter (something Like &&"global Table Filter&&")
Hello,

Here is my problem:

I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)

Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join         B on (A.ID = B.REF_ID) where ... /* ... */end;

All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.

However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.

Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.

However, I don't know what is the easiest and fastest way to filter the tables. Example:

I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join         B on (A.ID = B.REF_ID) where ...
   and A.COMPANYID = @CompanyID   and B.COMPANYID = @CompanyID /* ... */end;

Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".

I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.

I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?

Thanks in advance.

Best regards,
Beroetz

View Replies !

GROUP By Clause Or DISTINCT Clause
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View Replies !

Filtering Results In The Where Clause Vs A Having Clause
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.

When on SQL Server 2000 the following statement ran without issue:

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN

(

SELECT TrackID

FROM dbo.Track_ID

GROUP BY TrackID

HAVING MAX(LegNum) = 1 AND

TrackID + 'x1' IN

(

SELECT

dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions

ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)

)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.

I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN
(

SELECT TrackID

FROM dbo.Track_ID

WHERE TrackID + 'x1' IN

(

SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions

ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)
GROUP BY TrackID

HAVING MAX(LegNum) = 1

)

View Replies !

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples:

1) Rows ordered using textual id rather than numeric id

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
  select 1 id
  union select 2 id
  union select 11 id
) v
order by
v.id

Result set is ordered as: 1, 11, 2
I expect: 1,2,11

if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
  select 1 id
  union select 2 id
  union select 11 id
) v
cross join (
  select 1 id
  union select 2 id
  union select 11 id
) u
order by
v.id
    ,u.id

Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)   May 3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb 9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View Replies !

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error

ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause

I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View Replies !

Having Clause Without GROUP BY Clause?
Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.

View Replies !

SQL Inner Join Clause And The Where Clause
Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?

Thanks

View Replies !

Diff In On Clause And Where Clause?????
hi..
i have basic question like

what is differance between conditions put in ON clause and in WHERE clause in JOINS????

see conditions that shown in brown color

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

another thing...

if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????

View Replies !

Top Clause With GROUP BY Clause
How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent

thanks in advance

regards
Waqas

View Replies !

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View Replies !

MDX And Filter
I am writing an MDX query and am trying to use Filter with more than one value...similar to an 'or' clause....can you help me?? In the code below I want to return Projects for Employees 142400 OR 2200....is there an 'or' keyword I can use....??????

SELECT{[Hours]} ON COLUMNS,
non empty
filter([Project ID].[Project Id].Members, [Employee]=[142400],[2200]) ON ROWS
FROM [Time and Expenses]
WHERE ([ACTIVE])

Thanks!

Dean

View Replies !

Filter
I need a query that returns every n record from the database and the last record.
For every n-th is easy, but I need a
WHERE Index % n = 0
but I need a last one too.
Any Help.

View Replies !

Using IN As Filter
What is the syntax for using IN as a filter? in SQL it would be IN( 'A', 'B', 'C') but this does not work in Reporting services.

View Replies !

Filter && *
Hi I am wandering if it is possible to achieve the following:

I am using Reporting Services and Reporting services I got a report with a text parameter.
I would like the user to introduce AA and find the product AA and when he introduces AA* it finds AA, AAD, AAC...

I am doing the following:

Filter([DIM Product].[Product].ALLMEMBERS,
,IIF(INSTR(@Product, "*") > 0
,[DIM Product].[Product].currentmember.name = Replace(@Product, "%", "")
,INSTR([DIM Product].[Product].currentmember.name, @Product)
)
)

The problem ist that the function Replace does not work!

Does someone know how to do it?

View Replies !

Filter On ' In Name
Hi,
I have a supllier named K O'Neill and i want to filter on his name in my supplier table . Is this possible ?

When I try Vend Name = 'K O%Neill' it does not work.

Can anyone help?

Thanks.

View Replies !

Filter Help
Hello I am working in an advertising company and I have to find all records who have advertised 7 days back but if one person has advertised more than once means for different products, his name should come only once. How can I filter this. I am totally new to this language I can write simple query but donâ€™t know how to filter it.
Name IndividualID Product Date
A 1 x 2007-01-27.23.59.59.777
A 1 y 2007-01-28.00.00.00.000

b 2 xx 2007-01-28.00.00.00.000

A 1 z 2007-01-28.00.00.00.000

b 2 yy 2007-01-28.00.00.00.000

A 1 p 2007-01-28.00.00.00.000

c 3 m 2007-01-28.00.00.00.000

d 1 n 2007-01-28.00.00.00.000

A 1 s 2007-01-28.00.00.00.000

View Replies !

Formatting A Filter
In a SQLDataSource, the following line throws an error as an undefined function. FilterExpression='left$(lname,1) = "D"'
Isn't this a standard VB function and why does it produce an error. How do I fix?
Thanks

View Replies !

Filter With Wildcards
Hi,
I want to select certain items out of a Table that match a typed in criterium, but I encounter a few problems with this:
I mean "Instring search", so if the string is anywhere in a field or even the whole table the Record should be in the Selection.
How do I make the SelectCommand of the SQL-Datasource use this value (that is typed in a textbox)
How is a Button to invoke the Selectcommand of the Datasource with it?
How does the Selectcommand look like, something like this? WHERE xxx LIKE <blah>
Thanks in advance,
Lex

View Replies !

How To Filter Sqldatasource
Hi,
I am new in framework 2 and I can't find a way to filter the sqldatasource.
I have an sqldatasource control that retrive data from data base-"Select * from myTable"
I set the fiterExpression property-sqlDataSource1.FilterExpression="ID='" + strID + "' " ;
I don't know how to continue from here.If I bound the sqlDataSource1 to a control like gridView it works good and I see the filter oparation. but I want to get the result set in the code and loop threw it like I did with ver 1.1 with sqldataReader:
While sqlDatareader1.Read { myCode ... }
How can I do it with sqlDataSource ?
Thanks,
David

View Replies !

Filter Expression
HiI have some data that need to be filtered based on a SET of Id's.If it's about a single ID, then i would pass it as a parameter in astored procedure and use it within the the WHERE Clause, but here thoseID's are determined in run time and I can't simply create a a storedprocedure for an unknown amount of ID's.I looked into the SQL Server 8.0 Manual but had no examples how to usethe Function Filter.Generaly, how can filter some records based on a set of ID's?Best regards

View Replies !

Filter A Measure With MDX
HI all friends

I have a Fact table like this

DIMENSSIONSMEASURES
------------------------------
idempidprodcutsalessalesws

I just want make the next query with MDX

SELECT sales
FROM myFactTable
WHERE salesws > 0

Note that salesws is a measure not a dimmension

How it would be in MDX? I need a lot of help

Regards!

View Replies !

Time Filter
Hi,
I need to filter my table to return data between 18:30 and 7:00

I am not sure how to do the half hour and span over midnight.

I think this might work for 18:00 and 7:00.

SELECT *
FROM MyTable
WHERE datePart(hour, Timestamp) Between 18 AND 24 Or datePart(hour, Timestamp) Between 00 AND 7

I would appreciate any help!

Thanks

View Replies !

Problem With Like Filter.
I have a table with a varchar in it.

Inside the var char it contains data like:

Variable.[32] + Variable.[432] * (Variable.[333] / Const.[21])

What I am trying to do is search for all records that are using variable 333

I was trying:

SELECT * FROM my_table WHERE data_field LIKE '%Variable.[333]%'

However, that returning nothing.

I am going to guess that the [ ] need to be escape or something, but I don't know if thats really the issue.

Doing:

SELECT * FROM my_table WHERE data_field LIKE '%Variable%'

Works.

Thanks in advance,
Rich

View Replies !

MDX Filter() Question
Hi all!

I just started to get to know analysis s. and MDX and all that jazz. I created a cube and I don't know how to filter it. For example I have a time dimension and I want to see the cube where orders are before 1999.12.31. How can I do that??? what is the correct syntax or what functions should I use in the MDX builder??

thx for helping!!

View Replies !

Profiler Filter
I have been running traces in SQL Profiler and have not been able to get the filtering to work correctly. I am interested in capturing only statements that use CPU and have setup the filter for CPU > 0. It still returns all of the rows that have Null. Is there any way of filtering out the rows that have nunll for CPU?

Thanks,
Ken Nicholson

View Replies !

How Do I Add A Filter To This Query?
I'm an MDX newbie.. with some help I was able to create a query that returns accurate results. All I need to do now is filter for Completed Work value of greater than 0.

I have tried to use the filter wizard in VS2005, but I have never gotten the desired results.

Platform Info:
SQL Server 2005 Standard
VS2005

---------Start Statement----------------------------------------------------------
WITH

MEMBER [Measures].[Completed Work by WI on dt1] AS

(

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,

[Date].[Year Week Date].[Date].&[2006-04-23T00:00:00],

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work by WI on dt2] AS

(

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,

[Date].[Year Week Date].[Date].&[2006-04-29T00:00:00],

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work] AS

[Measures].[Completed Work by WI on dt2] - [Measures].[Completed Work by WI on dt1]

SELECT
NON empty
{
[Measures].[Completed Work]

}

ON COLUMNS,

NON EMPTY

{
([Assigned To].[Person].[Person],[Work Item].[System_Id].[System_Id],[Work Item].[System_Title].[System_Title])

}

ON ROWS

FROM

[Team System]

----------End Statement---------------------------------------------------

View Replies !

JOIN Filter
I have two tables A and B. I want to insert data into tables C and D based on join between A and B (A.column = B.column). What is the best way to accomplish this type of task?

IE-------> If column = xyz then insert into table C
IF column = abc then insert into table D
ELSE do_not_insert

Please help.
Regards

Ziggy

View Replies !

Filter Date
I got a field called Date#, datatype = datetime, default value = getdate().

So after i insert a record, the Date# will auto record the system date and the time. And my MS SQL Server show: 6/9/2004 12:24:35 PM

However i am not able to filter my record by date. The statement i tried are:

SELECT * from mytable WHERE Date# = '6/9/2004 12:24:35 PM'

SELECT * from mytable WHERE Date# = "6/9/2004 12:24:35 PM"

SELECT * from mytable WHERE Date# = 6/9/2004 12:24:35 PM

SELECT * from mytable WHERE Date# = '6/9/2004'

Pls help me,
Thanks a million

View Replies !

Using Like To Filter Result

Hi

when i execute this query

select * from Authors

where name like '%%' and id like '%%'

--name is type of varchar and id is type of int

i get all the result in the db

and if i put any filteration inside '%%' for the name or the id i get the filtered result correct

but i am trying to use this query inside a stored procedure

so i can send the id and name as parameters and when they are

empty i want to get all the result

i have done it for the name only but not for the id like this

declare @x varchar

set @x='r' --or ican set it to '' so i can get all results

select * from Authors

where name like '%'+@x+'%'

Any Help

View Replies !

Filter By Date From VB

I have been trying to write a simple database program using VB and SQL Express. In doing so, I have run into a problem. I am trying to filter by a Date value. I have the following SQL in the background.

SELECT     TransID, BIN, CarrierName, CheckDate, NumberOfClaims, BeginningDate, EndingDate, CheckAmount, Code, CheckNumber
FROM         Payments
WHERE     (CheckDate=@CheckDate)
ORDER BY CheckDate DESC

I am using a datetimepicker to select a date value and pass it in. Doesn't work. I get an error about "errors converting string to date" or something like that. I thought datetimepickers passed date values.

Anyway, just executing the SQL query and typing in the parameter value gives the same results.

Please help. What am I doing wrong.

Thanks for the help

If you need additional info, please let me know.

View Replies !

Pre-filter Data In SQL
I need to query the Employees table in the ABC database to find all employees who report to EmployeeID 5 (indicated by a value of 5 in the €˜ReportsTo€™ column) and who have the word €˜Sales€™ in their job title (i.e the Title column). In my database the Employees table is very large so you want to pre-filter the data before doing more complicated processing on it. How would I use the following statement to to pre-filter the data?

SELECT * FROM EMPLOYEES WHERE ReportsTo = 5

View Replies !

Disable Filter
i am having a query in front end like this..

func()

select * from table where cond1 and cond2 and col.port in <string> ... goes

end

i passing this string from other function, i may be not be needing this filter(underlined) for some functionality, but i will be using the same function for both the functionalities

i need a string that should tell the sql server to allow all the possible values for that column, so by disabling the filter which i am using...

pls tell me URGENT
Thanks in advance

View Replies !

Filter Error
I have No Filters in Place but I Keep getting this error on a couple reports.

An error has occurred during report processing.

The processing of SortExpression for the table €˜table2€™ cannot be performed. The comparison failed. Please check the data type returned by the SortExpression.

View Replies !

Filter On Row Group
Hello,

I can add the following filter to row group 1 in my matrix, "=Count(Fields!event_id.Value) >= 2", but if I try the same filter in row group 2 in the matrix (a group with nearly exact same configuration as group 1), I get the following error:

"Operation is not valid due to current state of the object".

I am trying to add a filter to a matrix that looks like:

Item                                 Value Value      Total

Item1    Item2                 5           5             10

Item3    Item4                 15         8             23

I want to filter the matrix based on the value of the total column, allowing the user to enter an actual value, or a Top N value, or a Top %. I can accomplish this on the first row group, but putting it in row group 2 doesn't work. Please help.

Thanks, Michael

View Replies !

Difference In Filter
Hello,
I have a List on my report, can someone explain to me the difference between entering the same filter expression in List Property -> Filter Tab and entering it in List Property -> Detail Group -> Filter Tab? I'm getting different resultset for each location.
thanks

View Replies !

Date Filter
I have a report in Visual Studio that I want to limit to just the activity for today. Normally I would add a WHERE stmt to the sql script that says docdate=getdate(). However this does not return any data to me in VS. I have tried many different syntax. Does anyone have the correct syntax or filter value that works in Visual Studio? Thank you.

View Replies !

Like Statement In A Filter
In a report I want a parametered filter on a specific text. I tried with the following statement:

=Iif(Parameters!Filter.Value="Example",(Fields!Warehouse_Class_Code.Value),"NULL") like %TEXT%

But no result. Please help!

View Replies !

Set Filter To Date Now()
I want to set a filter to calculate the date range to be 1 year ago to last month. Is this possible?

ps. Im working with a AS cube.

View Replies !

Filter Measure
I have a delima.

I have a dataset that needs to return "Fatalities Involving drivers age 15-17". For years 2003 , 2004, 2005.

So i set up 3 different filters: Year : 2003,2004,2005

Person type: Driver

Age : 15,16,17

Now this is fine for getting a crash count and fatalities (for teen drivers). But i want the fatalities for everyone ( All Person Types) and (All Ages) ...how do i go about this. All i get right now, is the fatalities for Drivers 15-17.

Please help..im confused!

View Replies !

Filter Top N Groups
Using: SSRS 2005, SSAS 2005Question: Can we use Filter Top N on Groups?
Detail:I have a table in RS with data sourced from an AS cube that has a structure like this:

   Category     Date              Quantity
   ========     ========          ========
+ Cat1
                2008-01-01          3,000
        2008-01-02          5,000
        2008-01-03          7,000
        2008-01-04          9,000
        2008-01-05          4,000
        2008-01-06          2,000
        2008-01-07          5,000
   Cat1Total         35,000

+ Cat2
                2008-01-01          2,000
        2008-01-02          5,000
        2008-01-03          6,000
        2008-01-04          8,000
        2008-01-05          3,000
        2008-01-06          1,000
        2008-01-07          5,000
   Cat2Total         30,000

+ Cat n...

I want to return only the top 10 categories by descending quantity.
I can't get the filter to work on either the table or group level properties.

FILTER:Expression: =sum(Fields!Quantity.Value)Operator: TopNValue: 10ERROR:Failed to evaluate the FilterValue

Do the Top and Bottom functions only work on the detail level?
Is there some other method I should be using?

I've tried to do this with the query using the MDX AGGREGATE function but having trouble getting it to return proper results. (Details in this thread: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3203246&SiteID=17 )

So as a workaround to get the project delivered on time, I'm reluctantly bringing in the date level and trying to do the aggregations and filtering in the RS table.

Any help would be appreciated.

-Michael

View Replies !

Group Filter

Hi,

I have a report that is displaying two groups. I have a filter on the second group (group 2). If this filter is true the detail, group 2 header and group 1 header are all successfully displayed. If this filter is false the detail and group 2 header are successfully not displayed, however the group 1 header is still displayed even there are no group 2 values within that group 1 value that satisfy the filter. What is the best way to ensure the group 1 header is not displayed in this situation?

Thanks in advance.

TeamGD

View Replies !

Grouping Filter
Need help understanding the Group/Filter.

I have Northwinds Products as a dataset.

I have a table with a Header/GroupHead/Detail/GroupFoot/Footer.

In the Detail I have ProductName in one column and UnitPrice in another.

For the Group Expression, I'm just using "1", so that's just everything. But I'm trying to then use a Filter within that group like the following

Expression:   =Fields!UnitPrice.Value
Operator:       >
Value:           =15

I expect only Product with values > 15, but I still get all everything.

View Replies !

Filter Efficiency
Hello, I have numerous filters applied to my Row Group in a Matrix. The filters are all of the type Sum(aggvalue.Value) Top N or Top % or > or < variety. My question is whether the row group filter is applied only once at the end of tabulation, or whether it is applied once for each record in the DataSet. I don't want this filter running 500,000 times for 500,000 records. But I am happy with it running just 1 time at the end to restrict the row groups.

Michael

View Replies !

How To Filter On Totals
Hi,

I have a report which shows data such like this

Date

Description

Debit Amount

Credit Amount

Tax

Balance

31-Dec-9999

CHARGE

197.3600

0.0000

11.8400

11.8400

23-Jun-1992

PAYMENT

12.0000

209.2000

10.0000
12.890

22-Jun-1992

CHARGE

157.3600

0.0000

15.1600

17.8400

I put a detail group on the report for this output.
I wanted to add a summary at the end of the report. which will show me the total according to filter like (if i add group footer it shows sum for all description and only in grouped row but i wanted to show total at the end and only desired groups) Here is an example.
I also had created a calculated filed to calculate totals =IIF( Fields!AccountClass.Value="CHRGE", Fields!SalesTax.Value,0)
it works fine but when i try to "sum" this filed it shows error instead of result.

Tax (for charge) = 17.00
Tax (for payment) = 10.00

i have a lot of descriptions but wanted to show the sum of only given descriptions.

If anyone know about it please inform me. I am new to Sql server reporting.
thanks in advance

View Replies !

How Would I Filter By Date?
Hi

I am trying to make a chart by using data from a database.

What I am trying to do is this.

The user will have 3 options

Option 1.

View last quiz

Option 2.

View Data by week(up to 4 weeks).

Option 3.

View by month

Later once I get these down I will have view 7 days worth of data.

So I am not sure how to do option 2 and 3. I have not tired to do Option 1 yet so can't say yet.

Like say if I have a months worth of data and they choose Option 2 it should make 4 columns on my graph. However I don't know how to make my sql to filter like that.

A user also can do 0 or more quiz's a day so first all the values(total right, total wrong, total assitance needed) would need to be added up. After that the filter would have to be taken place so that it filters.

So how would i do something like this?

View Replies !