I 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 a
stored procedure and use it within the the WHERE Clause, but here those
ID's are determined in run time and I can't simply create a a stored
procedure for an unknown amount of ID's.
I looked into the SQL Server 8.0 Manual but had no examples how to use
the Function Filter.
Generaly, how can filter some records based on a set of ID's?
Using VWD I have created a search feature using the LIKE clause. The filter expression on my SQLDataSource allows the user to search the Description field of a database and yield a result that contains the exact word or phrase entered into a textbox. Assuming that the user enters more than one word, my understanding is that the search result is limited to database rows that contain the EXACT phrase (such as found in an advanced Google search using the “with the exact phrase” option). The current filter expression is: Description LIKE '%{0}%' For example, if “John Smith” is typed into the search textbox, the results will include a row with: 1. “John Smith is my neighbor” but NOT a row with 2. “John is my neighbor. His last name is Smith”. How does one modify the filter expression so that the search result is like the Google “with all the words” search option, where the search results are limited to records in which all the words typed into the textbox are present but not necessarily in the EXACT continuous order? In the example above, BOTH Descriptions would be returned in the search results when “John Smith” in typed into the search textbox. Thanks for any help you can provide in helping me refine my search options.
In my report I want to use OR condition instead of AND in Table Filter Expression. By default this option is disabled in VS2005 Pro. How do I enable this? or is there any other way to do this? Thanks in advance.
I tried enclosing the query in Abs( ), which I have done in the past, although in the past I wasn't using Lookup functions so I don't know if that is the reason it won't work.
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter - Language Code = GetUserCulture() Which translates to this in the database query (for us english) - table.language_code = 'EN-us' And of course I need it to look like this - table.language_code = 'ENG'
I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this Language Code = dbo.ConvertFcnIWrote(GetUserCulture()) Or how I would access the custom assembly in the filter expression.
Do you have a recommended implementation for this situation?
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)?
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)
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls as connectionString works
However - I'm trying to deploy the package - and trying to use expression: @[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls" which causes the same error to occur
(Same error with other Excel source also: Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1 )
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
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
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.
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...
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
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
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
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?
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
I have an sql script that has 2 main blocks of Where filter which I'd call as Filter1 and Filter2 as follows:
Select DisplayColumns... From InterestingTables Where (Filter1) --W1 AND --W2 NOT --W3 (Filter2) --W4
Note that Filter1 and Filter2 are composite filters (inner filters nested; it shouldn't matter as long as I have the outer parenthesis as above, right?). Now, say SetF1ExF2 is the result set of the sql script above. Additionally, let's say I obtain the result set SetF1 by having W2 to W4 commented out and SetF1AndF2 comes from commenting out W3. Shouldn't the following hold: SetF1AndF2 = SetF1 - SetF1ExF2 I am having a discrepancy between the values with SetF1AndF2 < SetF1 - SetF1ExF2.
I've got an MDX query I need to modify, but not quite sure how to proceed. I essentially know most of tye syntax, but am at a loss as to where the components of the syntax should appear in the query. Here is the original query:
Code:
WITH MEMBER [Employee].[FTE vs FL].[CF Total] AS AGGREGATE([Client Facing Disciplines]) MEMBER [Time].[Year - Half Year - Quarter - Month - Date].[Trailing 12 Months] AS AGGREGATE(LASTPERIODS(60, STRTOMEMBER(@SelectedMonthEx, CONSTRAINED))) MEMBER [Time].[Year - Half Year - Quarter - Month - Date].[YTD] AS AGGREGATE(YTD(STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)))
[Code] ....
This is how I'm sure the two lines that say what should be excluded, I'm just not sure where they should go. I believe a FILTER statement is what I need, but not sure how to format it.
[Employee].[FTE vs FL].[Discipline].&[LOYALTY], [Employee].[FTE vs FL].[Discipline].[Department].&[OLSON PR Chicago]
Hi , account_desc_out,Analysis_Desc_out,Sub_Analysis_Desc_out,debit_out, credit_out,sub_Analysis_Code_out,Analysis_Code_out -----------------------------------------------
11066-0001-01 GEN 6633 0.00000000 160.000000006633GEN
11066-0001-01 GEN 6633 160.00000000 0.000000006633GEN
When i run the below query the o/p is comming as above:actually no record should come bcz debit amt and credit amt should knock off .can anyone correct the below query:
select account_code_desc as'Account_Desc_out', analysis_code_Desc as'Analysis_Desc_out', subanalysis_code_desc as 'Sub_Analysis_Desc_out', case when drcr_flag='dr' then sum(isnull(base_amount,0)) else 0.00 end as 'debit_out', case when drcr_flag='cr' then sum(isnull(base_amount,0)) else 0.00 end as 'credit_out', subanalysis_code as 'sub_Analysis_Code_out', analysis_code as 'Analysis_Code_out' from Analysis_stg_tb as stg with (NOLOCK) where subanalysis_code_desc='6633-Goverdhan S' group by subanalysis_code,analysis_code, account_code_desc,analysis_code_desc,subanalysis_code_desc,drcr_flag having sum(case when drcr_flag='cr' then isnull(base_amount,0) else 0.00 end) - sum(case when drcr_flag='dr' then isnull(base_amount,0) else 0.00 end)<>0 order by Account_Desc_out,Analysis_Desc_out,Sub_Analysis_Desc_out
Hi every one, I want to filter application name in sql profiler, so I write SQL% (for every one who connects by sql qery analyzer) in "application name" filter box but it doesn't work correctly,and shows all applications. Why it works incorrect? Thank You Kini
Ok I am experimenting with the data in this database and trying to determine the best way to write this query. I need to find patients that visited in 2005 or 2006 but have not come back since. At first I was doing it looking at patients that have only came in one time ever and that being in 2005 and 2006 but I think I may be missing some patients. I want to narrow it to patients that came in during 2005 and 2006 but have not come back since but I think I am just getting an unduplicated list of patients that visited during those years. Am I on track to do this with the query I have written and if so any ideas?
With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as ( select distinct person_id,person_nbr,first_name,last_name,date_of_birth
from (select count(*) as countenc,a.person_id,a.person_nbr, a.first_name,a.last_name, a.date_of_birth from person a join patient_encounter b on a.person_id = b.person_id where year(b.create_timestamp) IN (2005,2006) group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth)tmp /*where tmp.countenc <=1*/ ) select description,mstr_list_item_desc, count(*) from ( select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name, tmp.last_name,tmp.date_of_birth,d.payer_name,year(b.create_timestamp)as YOS,f.mstr_list_item_desc from PersonMIA tmp join person a on a.person_id = tmp.person_id join patient_encounter b on a.person_id = b.person_id join provider_mstr c on b.rendering_provider_id = c.provider_id cross apply(select top 1 payer_name,payer_id from person_payer where person_id = tmp.person_id order by payer_id) d join payer_mstr e on d.payer_id = e.payer_id join mstr_lists f on e.financial_class = f.mstr_list_item_id where c.description = 'Leon MD, Enrique' /*and year(b.create_timestamp) IN (2005,2006)*/ group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name, tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp, f.mstr_list_item_dessc)tmp2 group by description,mstr_list_item_desc
How to write sql query that will filter between two dates (user input in text box1 and text box2) in the below sql statement .
SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1 UNION ALL SELECT 'Table2', SUM(a) - SUM(b) FROM table2 UNION ALL SELECT 'Table3', SUM(a) - SUM(b) FROM table3
The revision numbers (revno) are unique per itemcode.I would like to get only the rows with the highest revision per itemcode, but I also need the prodspecUID with is the index key and therefore unique.I have tried MAX() and TOP 1's in sub queries, but up until now without any result.
I've setup RDA (Sql Server 2005) for my WM5 device and it works great to pull a simple table, even with a simple where clause.
I tried to join to tables to limit the number of records but I'm getting an error saying something about cannot track because of multitable query. So is it only possible to pull simple tables with RDA. If it is, is there a workaround?
Hello , I am Joining two tables and trying to filter the data based on some conditions. SELECT SERVICE_TICKET.SERVICE_TICKET_CODE,BuildingAddress.BuildingAddress, BuildingAddress.UserID,SERVICE_TICKET.PROBLEM_REPORTED, SERVICE_TICKET.CONTACT_PERSON, SERVICE_TICKET.STATUS FROM BuildingAddress INNER JOIN SERVICE_TICKET ON BuildingAddress.Customer_Site_Int_ID = SERVICE_TICKET.CUST_SITE_INT_ID WHERE (JoinTable.UserID = @parameter) AND (SERVICE_TICKET.SERVICE_TICKET_CODE LIKE 'HD' + '%') See in the query at the WHERE clause I am using JoinTable.UserID column actually I know that it is wrong . Could some one tell me how the query should be so that I can get the records from the join which matches the UserID column to a parameter. Hope that my question is clear. Thanks
