Filter Criteria - Temp Table Join Or Where Clause?
Jul 20, 2005
I have a set of udf's dealing that return a one column table of values
parsed from a comma delimeted string.
For example:
CREATE FUNCTION [dbo].[udf_filter_patient]
(
@patient_list varchar(2000)
)
RETURNS @patient TABLE
(
patient_id int
)
AS
BEGIN
insert into @patient
select patient_id from patient
-- parse @patient_list stuff excluded
RETURN
END
I have come up with the following two schemes to use these udfs. These
examples are obviously simplified, and I have a handful of stored procedures
that will use between 10 or more of these filters. If the two are
equivalent, I prefer Method 2 because it makes for much neater SQL when
using many filter criteria.
So my question is, will one noticebly outperform the other? Or is there a
better way in which to filter on a list of criteria?
Method 1 :
CREATE PROC sp__filter_open_bills
@patient_list varchar(2000)
AS
CREATE TABLE #patient
(
patient_id int
)
INSERT INTO #patient
SELECT
patient_id
FROM
dbo.udf_filter_patient( @patient_list )
SELECT
*
FROM
open_bills
INNER JOIN #patient on #patient.patient_id = open_bills.patient_id
GO
Method 2 :
CREATE PROC sp__filter_open_bills
@patient_list varchar(2000)
AS
SELECT
*
FROM
open_bills
WHERE
open_bills.patient_id IN ( SELECT patient_id FROM
dbo.udf_filter_patient( @patient_list ) )
Hi, I am using a SQL back end to dynamically populate an asp.net report/page. As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable. My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query. Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause? Any help gratefully appreciated. Thanks. John
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.
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.
SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID WHERE B.SomeParamColumn = @SomeParam
SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.
So is there any other way to handle multiple criteria in T-SQL ?
I have this SP that works, except I need to add another field value for the WHERE clause. As you can see I have "WM" but I need to add "PR", and those two are definitely in the table field. I've tried a variety of syntax arrangements using the AND operator, the OR operator, the & operator, just a comma between the two, nothing between the two. Can someone please show me what I'm doing wrong. It fileters for "WM" fine, but I also need it to filter in the WHERE clause for "PR". Here is the SP:
CREATE procedure spDemoSchedule (@beginDate varchar(10), @endDate varchar(10), @storeNum int) AS
SELECT Progstats.[Program#], Progstats.KCKOFF, Progstats.ProgramName, Progstats.Parent, Store.[Str#], Store.Status, Progstats.Dept, Store.[Program#], Product.[Item#], Product.[Item] FROM Progstats INNER JOIN Product ON Progstats.[Program#] = Product.[Program#] INNER JOIN Store ON Progstats.[Program#] = Store.[Program#] WHERE Progstats.KCKOFF BETWEEN @beginDate AND @endDate AND Store.[Str#]=@storeNum AND Progstats.CLASS="WM" GO
In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).
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 have a stored procedure which is used to search records in the database based on the data sent from the web page. There are several search fields and all of them are in one table (Table1) except the "CallerName" field which is in a different table (Table2). Since I had to show CallerName also in the gridview apart from other columns, I did a LEFT JOIN (using field CallerNumber) to show all the required fields from Table1 and CallerName from Table2. Now heres the problem. Since CallerName is a search criteria, its supposed to be in the WHERE clause after the JOIN or in the JOIN clause itself. The problem is, if I put it in WHERE clause, the result set doesn't show records from Table1 which do not have a matching CallerNumber in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName FROM Table1 T1 LEFT JOIN Table2 T2 on T1.CallerNumber = T2.CallerNumber WHERE T1.CallerNumber = 'some number' AND T2.CallerName = 'some name' If I put it in the JOIN condition, it works just like a LEFT JOIN is supposed to work, showing all the records in Table1 and also those which had CallerName in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName FROM Table1 T1 LEFT JOIN Table2 T2 on T1.CallerNumber = T2.CallerNumber AND T2.CallerName = 'some name' WHERE T1.CallerNumber = 'some number'
1st SQL won't work for me because it doesn't show all the records in Table1, even when no search criteria is specified.2nd SQL won't work for me because it shows more than required when just CallerName is sent from the web page as search criteria. It will show all the records where CallerName is "some name" and also all the additional records (since it is a left join). Can I get the goodness of both in one or do I have to create two separate Stored Procedures? Thanks all,Bullpit
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/ SELECT a.[naics] ,a.[ust_code] ,a.[port] ,a.[all_qty_1_yr] ,a.[all_qty_2_yr]
[Code] ....
Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
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)?
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
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'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?
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)
Hello, I am sort of new to SQL so I thought I would ask you people about this problem I'm having. Let's say I have two tables defined as follows, where the Ids are the primary keys -
Team Table Player Table
id Name TeamId PName 1 Gophers 1 Carman 2 Pioneers 1 Stoa 1 Wheeler 2 Bozak 2 Butler 2 Ostrow
Now let's say I join them using the following statement
SELECT * FROM Team INNER JOIN Player ON Player.TeamId = Team.Id
and it gives me the following - id Name TeamId PName
1 Gophers 1 Carman
1 Gophers 1 Stoa
1 Gophers 1 Wheeler
2 Pioneers 2 Bozak 2 Pioneers 2 Butler
2 Pioneers 2 Ostrow
But what I want as my end result, and I don't know if this is possible using SQL is this -
id Name TeamId PName
1 Gophers 1 Wheeler
NULL NULL 1 Stoa
NULL NULL 1 Carman
2 Pioneers 2 Ostrow NULL NULL 2 Bozak
NULL NULL 2 Butler
Can this be done in SQL using JOIN and how would I do it?
I'm experiencing a really weird problem: I have a SqlCE subscriber connecting via IIS to a SQL Server 2005 database. The database publisher/distributor and the web server are on different machines (IIS being in a DMZ) and I want to avoid the use of Kerberos delegation to share priviledges on the snapshot folder. I configured the web synchronization to accept basic authentication and obviously the subscriber sends @internetlogin and @internetpassword. Plus, I used a DB authentication on the SQL Server database. First problem, if i disable on the publisher configuration the anonymous authentication, replication fails (I expected to be an authenticated user) If i enable anonymous authentication, the replication succeed but ONLY if I don't use join filters. Everything goes well with static filters, overlapping/non overlapping articles, download only tables. Using a dynamic filter results in a "The merge process was unable to deliver the snapshot to the Subscriber" error.
I really can't imagine a way to fix this behaviour.
I have a merge (SQL 2005 Standard -> Express) topolgoy which is having problems
The main problem is that the join filters don't seem to work for one area and I am hoping someone can help me with some troubleshooting advice
There are 140+ tables in the topology but the ones causing particular pain are a parent child relationship where the child is actually a bridge/linking table to another table.
Therefore although it is a parent child in the replication filters it is the reverse. i.e. the child has the paramterised filter on it and the parent is one level down joined by it's id. There are other tables joined to this parent table but it stays at three levels deep. The @join_unique_key therefore is set to 0 as is the partition options for the parent /child relationship.
However, when we synchronise we have a problem. The rows get inserted in to the database in RI order but only the child records are replicated down to the subscriber.
The child table with the parameterised filter has 13 articles joined to it in total and one of the other branches of join filters go down as deep as four levels. Most though do not.
Does anyone have any suggestions as to why this might be happening? Any help would be greatly appreciated.
Cheers, James
P.S. I should add this problem only occurs when the edits are made at the publisher. If new records are added at the subscriber everything is fine.
Please refer to the below query. I want to filter inner join rows based on outer query column value (refer to bold text).
SELECT M.MouldId, SI.StockCode, MI.Cavity, MI.ShotCounter, CQ.SumOfCastedQty as CastedQty, MI.CounterStartup FROM MouldItem MI JOIN (SELECT JD.MouldId, JC.StockCode, SUM(JS.CastedQty) AS SumOfCastedQty FROM JobCasting AS JS INNER JOIN JobCreationDet AS JD ON JS.JobDetId = JD.Uniid INNER JOIN JobCreation AS JC ON JD.JobIdx = JC.Uniid
Using Merge replication, I have a table that is filtered using the HOST_NAME() function. The filter also makes use of a function (as the HOST_NAME() is overriden to return some complex data).
Everything replicates and filters just fine. but when I add a join filter on a different table (the join filter is a simple foreign key join) I get the following error when the snapshot agent is run:
Message: Conflicting locking hints are specified for table "fn_GetIDList". This may be caused by a conflicting hint specified for a view. Command Text: sp_MSsetup_partition_groups Parameters: @publication = test1
fn_GetIDList is the function used in the original filter.
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 ?
I think I am getting 0 records returned... because.... I am joining the third value based on a zip code. The two tables if directly compared to each other would never have an = match. SELECT t2.company_name, t2.firstname, t2.lastname, modelname, configname, format, version, username, t2.zip,
t2.country FROM EtechModelRequests JOIN CC_Host.dbo.USR_SC as t2 ON Cast(t2.user_id As char) = username --JOIN --Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON PostalCode = zip WHERE RequestDateTime > CONVERT(DATETIME, '2007-09-1 00:00:00', 102) AND interfacename LIKE '%download%' AND
result=0 AND country='CA' --AND t3.PostalCode Like 'z1x%' ORDER BY company_name I was trying to do it by using a Where clause AND t3.PostalCode Like 'z1x%' that I will later turn into an Input Parameter after I get it working. Is there anyway to trim the PostalCode to the first three characters during the join process? Something like Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON LEFT(PostalCode, 3) = zip Not sure I got the LEFT function syntax correct even. Help appreciated.
I'm joining one table on to another table using one of 2 possibile fields (so table 1 key one can either match table 2 key 1 or key 2)... When the first key is null for a record, the script is to attempt to join using the second key instead. It is possible to have both values present, in which case the first one should be used.
I've taken a few runs at this so far:
... from table1 t1 left join table2 t2 on (t1.key1 = t2.key1 or t1.key1 = t2.key2)
If either t2.key1 or t2.key2 are populated, this works. Unfortunately, it's bringing back multiple records if both key1 and key2 are populated. Question # 1... Is there a different relational operator I can be using instead of OR that would logically look like 'if thie first key didn't find anything try the second instead'?
As an alternative, I've put the NVL to use...
NVL(t2.key1, t2.key2) = t1.key1
That seems to work, but it's pretty heavy on the server. Any suggestions on how else to handle this scenario would be greatly appreciated
view 1 I have a view that is drawing from two tables. Each table contains fields representing cube coordinates. The view is filtering the results based on some simple logic (where the defference between two values in the tables are greater than x) this part works fine.
view 2 notes field I want to include a note field in my view. This field will contain the contents of a note field from another view. This second view also contains coordinates that I can use to map the notes to the appropriate rows in view 1. However, if I join the views in my FROM clause, I will end up filtering my resultset down to rows that correspond to view 2's contents.
I want to have the full contents of view 1, displayed with a note field containing the note field content from view 2 only in the rows that have corresponding notes. (some rows will have notes, some will not)
so... my question: is there any way that I can include this field without joining the views in my FROM clause (meking my resultset exclusive)..... possibly somehow in fields list of the select statement?
I think this is a very simple question, however, I don't know the answer. What is the difference between a regular Temp table and a Global Temp table? I need to create a temp table within an sp that all users will use. I want the table recreated each time someone accesses the sp, though, because some of the same info may need to be inserted and I don't want any PK errors.
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?