Filter Criteria - Temp Table Join Or Where Clause?
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 ) )
GO
Thanks for the help!
Chris G
View Complete Forum Thread with Replies
Related Forum Messages:
Best Practice: Use Values In WHERE Clause Or Create And Join Temp Table ?
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
View Replies !
Best Practice Question: JOIN Criteria Vs. WHERE Criteria
For example, consider the following queries: DECLARE @SomeParam INT SET @SomeParam = 44 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? Thanks.
View Replies !
Join Or Temp Table
Hi, I am working on a data warehouse and need to do some data pruning. My question is, the tables that are affected can be joined, but these two tables have a many-to-many relationship. The easiest way I can think of would be to dump the results of a initial query into a temp table and do another select to process the values in the temp table. There is a better way, though, isn't there? thanks in advance billy
View Replies !
Filter Same Group By Different Criteria
In my report I have group1 grouped by order, under that is group2 grouped by unit, under that is group3 grouped by cost_type, under that is details In group2 general expression 1st line is =unit. The 2nd line is =iif(cost_type=Serial, 2, 1) this works fine(for now). If a unit has cost type of Serial I get two groups of that unit. The filter expression of group3 is =Fields!cost_type.Value. The Operator is != and the value is Serial. This works great. for the first grouping of group2. on the second grouping of group2 there are no values in the columns. I want the second group to be filtered so only the Serial values show. What am I missing? Also I have the details visibilty set as hidden=True and ToggleItem=cost_type. So the second grouping of group 2 can be expanded to show the details.
View Replies !
WHERE Clause Not Picking Up 2nd Field Criteria
Hello, 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 TIA, Bruce Wexler
View Replies !
Where Clause Filter
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. TIA.
View Replies !
Additional Criteria In LEFT JOIN
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
View Replies !
Sql Replication Filter Clause
Hello, I am using the below sql to filter rows in sql transactional replication. I need to the EditionIds that I get from the filtering below to get all their article. How can I do that because it is not accepting joins in the replication filter window SELECT <published_columns> FROM <<TABLE>> WHERE <<TABLE>>.EditionDate = 2/2/2005 thanks
View Replies !
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 !
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 !
RDA Filter With Join?
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? Jesus saves. But Gretzky slaps in the rebound.
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 !
JOIN Filter Problem
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?
View Replies !
Join Filter Failure
Hello there, 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. Thanks in advance
View Replies !
Merge: Records Associated By A JOIN Filter Not Being Sent To A Subscriber
Hi, 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.
View Replies !
Join Filter Causes Conflicting Locking Hints
Hi, 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. Thanks for any help Graham
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 !
Multiple Criteria In WHERE Clause (was &"T-SQL&")
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 ?
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 !
Update Data To A Table From The Sum Of A Field From Another Table Based On Some Criteria
Hello Friends, I have two tables, And also I have Sample data in them. create table X (y int, m int, v int) insert into X select 2007,1,5 insert into X select 2007,1,3 insert into X select 2007,2,9 insert into X select 2007,2,1 select * from X Create table Y (fy int, fm int, v int) insert into Y select 2007,1,0 insert into Y select 2007,2,0 insert into Y select 2007,3,0 select * from X select * from Y I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m Using temporary table cannot be done. Thanks in Advance, Babz
View Replies !
Where Clause With LIKE 'xxx%' But There Is A JOIN..
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.
View Replies !
JOIN Outside Of WHERE Clause ? ? ? ?
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) eg. VIEW 1 row1 row2 row3 note_row (from view 2) fsdfs sdfsdf sdfsdf <no note> sdfs sdfsd sdfsd "note" sdfsdf sdfsdf ssdfsd <no note> 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? THANKS!
View Replies !
Or In A Join Clause
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 (and ya, I didn't design this datamodel).
View Replies !
Temp Table Vs Global Temp Table
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. thanks!! Toni Eibner
View Replies !
JOIN With ORDER BY Clause?
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?
View Replies !
Multiple Join Clause
I have a table "Users" like this: GroupId CompanyId UserId I need to query the users getting the company's and group's names, but I only know how to join one table. Example: Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name From Users JOIN Groups ON Users.GroupId = Groups.Id Hon can I add the companies table in the Join ? Thanks, Moshe
View Replies !
Differ Bet WHERE Clause && INNER JOIN?
In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set. In layman's terms, what is the difference? Any examples? Thanks in advance. ddave
View Replies !
Right Outer Join With Where Clause
Using SQL Server 2005 I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped. I need a to show the count of each events that have happened to it within a certain time period. This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table. What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count) Any thoughts? I'm a complete newbie at this by the way. Thanks Code Snippet SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit FROM DeviceEventDurationLog RIGHT OUTER JOIN DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID WHERE (DeviceEventDurationLog.TimeIn > @StartDate) AND (DeviceEventDurationLog.TimeIn < @EndDate) GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit ORDER BY DeviceStatusWords.DeviceName
View Replies !
Help Using An Outer Join When Using A Where Clause
I am trying to get all of the Fund_cdes to show up even if there was no transaction on the brkg fact table. The problem I coming up with is I am also retricting what I show in the brkg fact table so I am not getting all of the row from the fund table. How do I write a left join that shows all of the fund cdes SELECT SEP_ACCOUNT.sep_acct_cde as Account, FUND.fund_cde as FUND, BRKG_FACT.accum_unit_cnt as Units_Purchased, BRKG_FACT.transaction_amt as Amount_Purchased FROM BRKG_FACT SEP_ACCOUNT FUND where BRKG_FACT.sep_acct_id_num = SEP_ACCOUNT.sep_acct_id_num brkg_fact.FUND_ID_NUM = FUND_DIM.FUND_ID_NUM brkg_fact.SEP_ACCT_ID_NUM = 5 and brkg_fact.product_cde <> 'MM' and brkg_fact.transaction_amt <= 0 and brkg_fact.source_sys_id_num <> 3 and brkg_fact.source_sys_id_num <> 5 and BRKG_FACT.trans_process_dte >= '1/1/2008' and BRKG_FACT.trans_process_dte <= '1/2/2008' order by fund_cde current output ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED U BLCD -0.01137 -1.48000 U BOND -0.01283 -1.67000 U CGDE -0.06743 -0.95000 U EQIN -0.13277 -2.39000 U GRST -0.11799 -4.07000 U IX4S -0.53996 -12.55000 U LCCS -0.18216 -5.31000 wanted output ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED U BLCD -0.01137 -1.48000 U BOND -0.01283 -1.67000 U BWDS NULL NULL U CGDE -0.06743 -0.95000 U EQIN -0.13277 -2.39000 U GAFR NULL NULL U GRST -0.11799 -4.07000 U IX4S -0.53996 -12.55000 U LIGE NULL NULL U LCCS -0.18216 -5.31000
View Replies !
Update Table Where It Matches Criteria From Another Table
I am trying to clean up an old database someone developed. I took the data and grouped the information that matched I then imported it into a trips table. The matching data is all in the where statement. It shrunk 16k records for just under 2k records. Now I am trying to right a query to relate the 2 tables and update the tripid# to the triplist of people who actually attended. Declare @Trip varchar(500) Declare @Destination Varchar(500) Declare @LeaveDate varchar(500) Declare @LeaveTime Varchar(500) Declare @ReturnDate varchar(500) Declare @ReturnTime varchar(500) Declare @Comment varchar(500) Declare @RescheduleDate Varchar(500) Declare @Rec int Select @Rec = Rec# From Trips Select @Trip = Trip from [Trips] where Trips.rec# = @Rec Select @Destination = Destination from [Trips] where Trips.rec# = @Rec Select @LeaveDate = LeaveDate from [Trips] where Trips.rec# = @Rec Select @LeaveTime = LeaveTime from [Trips] where Trips.rec# = @Rec Select @ReturnDate = ReturnDate from [Trips] where Trips.rec# = @Rec Select @ReturnTime = ReturnTime from [Trips] where Trips.rec# = @Rec Select @Comment = Comment from [Trips] where Trips.rec# = @Rec Select @RescheduleDate = RescheduleDate from [Trips] where Trips.rec# = @Rec Update TripList SET tripid# = @Rec WHERE (Trip = @Trip) and (Destination = @Destination) and (LeaveDate = @LeaveDate) and (LeaveTime = @LeaveTime) and (ReturnDate = @ReturnDate) and (ReturnTime = @ReturnTime) and (Comment = @Comment) and (RescheduleDate = @RescheduleDate)
View Replies !
Adding Conditions In The ON Clause Of A JOIN
Hi Faculties,I have two queries which give me the same output.-- Query 1SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.idWHERE cat.id = 1;-- Query 2SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.id AND cat.id = 1;The first query uses the WHERE clause and the second one has all theconditions in the ON clause. Is there anthing wrong with the secondapproach in terms of performance? Please suggest.Thanks in advanceJackal
View Replies !
ANSI-92 Inner Join Vs. Where Clause Syntax
Having problems rewriting my join condition using the "inner join" syntax. My query, working with an intersection table: SELECT Description, EmailAddress FROM Accounts_Roles r, Accounts_Users u, Accounts_UserRoles ur WHERE r.RoleID = ur.RoleID AND u.UserID = ur.UserID This works fine, but i want to write it using 'inner join' style, so I tried: SELECT Description, EmailAddress FROM Accounts_Roles r, Accounts_Users u INNER JOIN Accounts_UserRoles ur ON r.RoleID = ur.RoleID AND u.UserID = ur.UserID which gives me an error (The column prefix 'r' does not match with a table name or alias name used in the query.) Any ideas as to how I'm screwing this up would be appreciated. Thanks, Gordon Z
View Replies !
Index / Join / Where Clause Very Slow
Hello, first of all, some facts of the case: Table Master Table Dimension ID Code Price ID Name 1 A44333 5000 1 "Scanner" 2 D442 3000 2 "Notebook" 3 D6644 4000 3 "Banana" I join both tables on ID and search one time for ID and another time for Name. Looks like (a) SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id WHERE master.id=1 AND Code like 'A44' (b) SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id WHERE Name = 'Scanner' AND Code like 'A44' Why does query (b) take longer than query (a)? Dimension has 12 Rows and Master has about 24M Rows. For index I did Create Index IX_Master_ID on Master(ID) Create Index IX_Master_Code on Master(Code) Create Index IX_Dimension_ID on Dimension(ID) Create Index IX_Dimension_Name on Dimension(Name) I noticed, that when i leave the Code like 'A44' clause, query (a) and (b) do take same time. I'm really confused. Can someone please help me out? Thank you Silas
View Replies !
Can We Have An Inner Join Clause In An Update Statement
Hi, I'm trying to inner join an update statement. Something like this: update #point_connection_temp AS a inner join #point_connection_temp_two as b on a.key_fld = b.key_fld set a.next_point = b.next_point where #point_connection_temp.next_point is null order by a.key_fld I'm getting an error message:Incorrect syntax error near AS Any help will be greatly appreciated.Thank you!!!!!!!!!1
View Replies !
Replication (With Join Clause) URGENT !!!
Hi , I have the following scenario in my replication setup 1 ) Publisher / distrubutor (one server) 2 ) Subscriber ( another server) The question that i have is this. I want to push data from a article in Publisher to Subscriber The data from publisher is to be filtered Horinzontally. But in filter clause(where clause) i need to join 3 or more tables..to get my required Filtered rows. HOW Do i Do it ??? any suggestions Is the join class supported in Replication ( i may go for Merge or Snapshot replication) as this process once in 15 days. Do any one has the idea Thanks in advance Vik
View Replies !
Any Benefit From Filtering In Join Vs. The Where Clause?
Just curious. The exec plan is the same for both qry's, and they both show the same estimated row counts @ the point of question in the exec plan. The exec times are roughly the same, any variances I'm attributing to db load from other things going on, since any benefits of one over the other are not consistent from execution to execution. So is there any benefit to filtering in the join conditions vs. the where clause? My thinking was that by filtering earlier in the qry (when joining) as opposed to "waiting" to do it in the where clause, the rest of the qry after the join would inherently be dealing w/a smaller result set for the rest of it's execution, thus improving performance. After the exec plan checking I did, I guess I was wrong. Seems that Sql Server is intelligent about such filtering when analyzing the entire qry, and building its execution accordingly. The execution plan for both qry's showed the same where clause argument for the tables being joined. Filtering in where clause.... Code: select... FromtProject p with (noLock) jointProjectCall pc with (noLock) on P.ID = pc.project_id jointStore S with (noLock) on pc.store_id = s.id jointZip Z with (noLock) on Z.zip5 = s.zip5 jointManager M on M.ID = case ... end leftjoin ( selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed fromtCall C whereAnswer > 0 and question_id in (1, 2) group by projectCall_Recnum ) as C on pc.recnum = c.recnum wherepc.removed = 0 andp.cancelled = 0 andp.deleted = 0 ands.closed = 0 ands.deleted = 0 andyear(getDate()) between year(P.startDate) and year(P.expDate) Filtering in joins... Code: select... FromtProject p with (noLock) jointProjectCall pc with (noLock) on P.ID = pc.project_id and pc.removed = 0 and p.cancelled = 0 and p.deleted = 0 and year(getDate()) between year(P.startDate) and year(P.expDate) jointStore S with (noLock) on pc.store_id = s.id jointZip Z with (noLock) on Z.zip5 = s.zip5 and s.closed = 0 and s.deleted = 0 jointManager M on M.ID = case ... end leftjoin ( selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed fromtCall C whereAnswer > 0 and question_id in (1, 2) group by projectCall_Recnum ) as C on pc.recnum = c.recnum
View Replies !
Filtering Records Through Join Or Where Clause
Hi All, Can anybody tell me which of the following is the most efficient query if i have huge tables. SELECT *FROM Tab1 Inner join Tab2 ON Tab1.Col1 = Tabl2.Col1 AND Tab1.Col1 = 5 OR SELECT *FROM Tab1 Inner join Tab2 ON Tab1.Col1 = Tabl2.Col1WHERE Tab1.Col1 = 5 As long as i explored this, Sql Server Query Execution Plan shows the similar cost for both cases. Is there any difference? If yes why? Thanks in advance. Regards, Sulaman Riaz
View Replies !
SQL Query Help - Right Outer Join With Where Clause
Using SQL Server Reporting services 2005 I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped. I need a table to show the count of each events that have happened to it within a certain time period. This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table. What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count) Any thoughts? I'm a complete newbie at this by the way. Thanks Code Snippet SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit FROM DeviceEventDurationLog RIGHT OUTER JOIN DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID WHERE (DeviceEventDurationLog.TimeIn > @StartDate) AND (DeviceEventDurationLog.TimeIn < @EndDate) GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit ORDER BY DeviceStatusWords.DeviceName
View Replies !
Select Inner Join With Where Clause Problems
Hello All, I have a question about a Select over 2 Tables, with the Following Scenario (Not all Products (ARTICULOS) haves CARAC's on the CFG_CARAC_ARTICULOS table): Picture of the tables here: http://www.pci-baleares.com/pantallazoSql.jpg We have per example 7 Slots (Motherboard, CPU, VGA Card, RAM, TOWER, etc...) When we fill the Slot with a CPU-> Then we open the Slot for VGA CARD, we do the Followin Select: SELECT dbo.ARTICULOS.* FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO Ok it brings up ALL Graphic Cards because they dont depends on CPU Now we go to the Motherboard Slot And we make the following Select to obtain the compatible Motherboards: SELECT dbo.ARTICULOS.* FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO WHERE ((dbo.CFG_CARAC_ARTICULOS.ID_CARAC = 7) AND (dbo.CFG_CARAC_ARTICULOS.VALOR = 'PCI-E')) AND ((ID_CARAC = 1) AND (VALOR = '775')) We check the motherboards if they support PCI-E (because we selected a Graphic card of that, and SOCKET 775 because the CPU) But SQL return 0 Rows, if we do the following Select: SELECT dbo.ARTICULOS.* FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO WHERE ((dbo.CFG_CARAC_ARTICULOS.ID_CARAC = 7) AND (dbo.CFG_CARAC_ARTICULOS.VALOR = 'PCI-E')) OR SELECT dbo.ARTICULOS.* FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO WHERE ((ID_CARAC = 1) AND (VALOR = '775')) It return Rows, it happens just if the Where clause haves more as 1 specifications... Any solution for it? It drives me crazy :D Thanks and regards Marc Hägele
View Replies !
|
TRACKER
