MS SQL Server :: Sql Replication Filter Clause

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 !

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 !

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 !

Replication With Filter
Hi All,

I've done my replication successfully between SQL Server 2000 and SQL Server CE. But how if I want to filter my replication?

For example :- At Table1 have 5 field which is UserID,Month,Year,Price,ItemCode

I want to filter replication data with UserID and Month and Year. How can I do it?

Thank you!!

View Replies !

Replication Filter Bug?
Im using SQL 2005 SP2 as the publisher and SQL Mobile subscribers and Im convinced I have found a replication bug .....but maybe Im just being stupid!

I have an account manager table which links to companies and users.
The organisation table use a nasty @subset_filterclause filter against the account management table to decide what records to retrieve for each subscriber.

This works fine and updates either way are replicated as you would expect.

However when an account manager is changed and the organisation no longer fulfils the filter criteria, it is not removed from the subscriber (unless I delete the db and download it from scratch)
Surely this is a bug?

Just to clarify the filter clause was ok, I added the account management table to the filtered tables using the exact same @subset_filterclause.

Now when I change an account manager, the account management reocrd is correctly removed from the subscriber but the organisation record remains on the subscriber? Have I missed something obvious?

View Replies !

Setting Up Filter For RDA Replication
Hi!
I have a WM5 device with Sql Server CE 3.0 and Sql Server 2005 on the serverside. I set up RDA and it works fine until I try do pull a little more advanced query. Instead of pulling a simple table with where clause I created a query that joined two tables returning values from only one. It doesn't seem to be possible, is it? If it's not, is it someway I work around this?

/Magnus

View Replies !

How To Filter Condition While Transactional Replication
Hi All,I have a table with a column DeletedDate which stores a logical deleteof a record.I need to set up transactional replication for reporting purposes thatthis deleted records should not be replicated to the subscriber. Thatis, if i see a value on the DeletedDate, I don't want that record tobe picked up for replication.At the same time, when someone marks the record for deletion (byputting a date on the DeleteDate column), I want that record to bedeleted on the subscriber database. (I can also set up a job to do thedeletes on the subscriber but i'd rather let the replication take careof it).Can this scenario be implemented in Microsoft SQL 2000? I wouldappreciate any ideas / thoughts in this matter.Thanks in advance,Aravin Rajendra.

View Replies !

Filter Records During Transactional Replication.
Hi All,I have a table with a column DeletedDate which stores a logical deleteof a record.I need to set up transactional replication for reporting purposes thatthis deleted records should not be replicated to the subscriber. Thatis, if i see a value on the DeletedDate, I don't want that record tobe picked up for replication.At the same time, when someone marks the record for deletion (byputting a date on the DeleteDate column), I want that record to bedeleted on the subscriber database. (I can also set up a job to do thedeletes on the subscriber but i'd rather let the replication take careof it).Can this scenario be implemented in Microsoft SQL 2000? I wouldappreciate any ideas / thoughts in this matter.Thanks in advance,Aravin Rajendra.

View Replies !

Merge Replication And Filter Columns
Hi,

I am trying to merge replicate two tables on two sql 2000 servers. I have got the merge replication working fine between the two servers, however when I remove a column in the "Publication Properties" --> "Filer Columns" it starts reporting errors. I have got the published to run the "Snapshot agent".

The error is as follows:

The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------
The process could not bulk copy into table '"dbo"."userdata"'.
(Source: MYSERVER (Agent); Error number: 20037)
---------------------------------------------------------------------------------------------------------------
Unexpected EOF encountered in BCP data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
---------------------------------------------------------------------------------------------------------------

If I add the column back in the replication starts working fine again.

The reason I am trying to put in a vertical filter is because the table at the subscriber contains columns which should remain independant of the replicated data.

If I change the snapshot agent so that it recreates the table at the subscriber it drops the columns that I've defined in the vertical filter.

In summary, what I'm trying to do is have two identical tables on two servers that have a few columns that do not get replicated. Is this possible or do I need to move these columns into other non-replicated tables? :rolleyes:

Any thoughts or infomation will be gratefuly received.

Matthew.
matthew.cantillon@ymonda.co.uk

View Replies !

Row Filter Error In Snapshot Replication
I am having problems specifying a row filter for a snapshot replication.

Since installing SQL Server 2000, I am unable to specify ANY row filter. No matter what I specify, I get the following error message:

Error 170: Line 1: Incorrect syntax near '0'

For example, trying to match the example given, I try to filter a state code
table based on a single value, and supply the following filter:

SELECT <published_columns> FROM <<table>> WHERE <<table>>.st_cd = 'WI'

I get this same error whether the field is alpha or numeric, whether it is a
new or existing publication, no matter which operators I use (=, >, <>). I
am using SQL Server 2000 with Service Pack 1.

If nothing else, I am interested to know: has anyone else been successful at specifying a row filter in SQL Server 2000? Is this just a problem at my installation or is this a bug in SQL 2000?

View Replies !

Merge Replication With Dynamic Filter
Brand new to SQL Server 2005 and replication, I was able to use online books info to get a replication topology set up (using wizards) and successfully replicate an article (a single table) to a SQL Server Express database.

I then cleared the subscription table and added a simple dynamic filter (colx = SUSER_SNAME()), which I can't get to work. Based on what I read, I'm thinking that SUSER_SNAME() is not returning the proper value because the merge agent is set to connect to the publisher and distributor by 'impersonating the process account'. This is on the subscription merge agent security form. However, 'using a SQL Server login' is greyed out and nothing I tried will enable it, including setting up logins and SQL user ids on the publisher and playing with entries on the publication properties data partitions page, etc.

I think that I have discovered only some of the pieces that I need and not enough details to find the rest and put them together. Any info on what to have set up where would be appreciated. Any suggestions for 'step by step' information that would help in addition to SQL Server 2005 Books Online would be great.

Thanks in advance.

View Replies !

Filter Dost Not Work In Merge Replication
Hello everyone,
Can any one please help me out how to fix the following? I would greatly appreciate it.

The filter in my merge replication does not work.

I have create the mege replication using T-SQL not from enterprise manager.
by specifiying the following filter clause.
@subset_filterclause = N'UPDATE_DATE > CURRENT_TIMESTAMP - 30'

SQL Server synchronizes the data how ever it does not deletes the records from the subscriber which is 30 days or older.

Other details in the environments
Subscriber is MS Access database with MDAC 2.8 installed on both server and PC
Server does not have SQL Service pack 1 installed.
O/S is Windows 2000 with service pack 4 installed.

thanks
Masanam

View Replies !

Merge Replication Using A Guid As A Dynamic Filter
Hi ...

I am working on a project where the server version of application has vouchers from different entities. I have created a publication manually. My next step was to create a client subscription using rmo and to execute a pull. This part works fine. Code samples from http://msdn2.microsoft.com/en-us/library/ms147314.aspx

My next step would be to implement dynamic filtering using the guid of the entity as a parameter.

I dont want to use suser_sname() or host_name() as I want to use a fixed login for the replication for all users, and a client could have several host dbs (sql express, sql mobile)

My goal would be to pass a guid-value to the HostName Property of the MergePullSubscription class and convert it to an uniquidentifier and use it as a filter as I have not found any other way to pass a guid as a filter.

RMO-Code:

subscription.HostName = "4bb0e468-c68a-4253-ba82-f71c3a6e302d"

Filter:

SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] = dbo.fx_ConvertHostToEntity()

Function:

create function fx_ConvertHostToEntity()
returns uniqueidentifier
as
Begin
declare @host nvarchar(50)
set @host = host_name()
declare @entity uniqueidentifier
set @entity = cast( @host as uniqueidentifier)
return @entity
End

When trying to set the filter sql server complains that a character string cannot be casted to a uniqueidentifier - so i can not set this filter. Is there a way to pass a parameter other then the username or the hostname as a filter?

SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] =@entity, where @entity is a guid

Thanks for your support

Alex

View Replies !

Merge Replication- Update To Filter Table Times Out
I have a merge replication setup in MSSQL 2005. It has one main table which is filtered by host_name and a number of other tables are joined to this filtered table using a merge filters (ID of the main table is FK to the joined tables). The replication works and changes are propogated in both directions from the publisher to the subscriber and vice-versa.

However, an update to the main table itself fails (times out) both in code and the SQL Mgmt Console. If the publication is dropped the update goes through without a problem.

I suspect the merge article triggers are trying to change the snapshot even as the update is taking place and hence the update times out. If there was a way to defer the updating of the snapshot, there might be a solution.

Any help would be greatly appreciated.

Thanks

Reggie

View Replies !

Disappearing Parameterised Filters And Filter Joins In Publication Properties Page Of Replication Monitor
Hi,

We have an issue with our replication configuration when viewed through replication monitor. Parameterised Filters and joined filters don't appear in the gui. However, when we script the publication all the filters are present.

This issue only seems to occur when we have a remote distributor.

I should also point out that we have a merge push topology that uses a custom RMO synchronisation component on a separate server to either the publisher or the distributor. Also all the databases in the topology are called the same name. This has caused us other issues relating to this topology in particular so I raise it here as well although I don't expect it to be the case in this instance.

Any help would be greatly appreciated in clarifying this matter.

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 !

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 !

Merge Replication W/ Dynamic Row Filter - Not 'dynamic' After First Initial Sync?
If anyone could confirm...

SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.

E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.

First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.

However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.

Question: I thought the filters would remain dynamic and be applied on each sync?

I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!

Question: I wonder why if parent records are supplied, why not child records?

If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...

Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?

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 !