Replication Filter Bug?
Feb 20, 2008
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 1 Replies
ADVERTISEMENT
Apr 13, 2006
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 5 Replies
View Related
Nov 20, 2007
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 4 Replies
View Related
Apr 13, 2007
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 3 Replies
View Related
Jun 14, 2006
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 8 Replies
View Related
Aug 12, 2006
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 6 Replies
View Related
Jul 26, 2015
We have a table in an SQL Server 2012 database that stores tree-like structures. Simplified for the purpose of my question, it has the following format:
Id int identity,
ParentId int,
GroupId int
Each record of the table represents an object identified by Id. An object may or may not have a parent in the same table, such that object.ParentId = parentObject.Id. A root object has ParentId = NULL. There are multiple root objects, so the table in fact stores multiple trees. What’s important is that the tree depth is not fixed, i.e. theoretically there can be any number of ancestor generations for an object. GroupId is a property of a root object; in theory none of the children of a root object has to have GroupId <> NULL; it can be assumed that any child has the same GroupId value as its root object.
A sample table having two roots (one grandparent and one parent), one non-root parent/child and 4 child roots:
Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 NULL non-root parent/child
3 2 NULL child
4 2 NULL child
5 NULL 300 root parent
6 5 NULL child
7 5 NULL child
The table is not normalised, i.e. there’s no separate {root_object : group} table. However I don’t think normalising the table would solve the problem.
Now the problem. We need to set up merge replication from the table above (Master table) to the table of the same format in another DB. We need to replicate only those rows of the Master table that have a certain fixed GroupId value, e.g. 200 in the example above. If we ensure that GroupId in all descendant objects of a root object has the same value in the table as the root object itself that would be trivial. The table would look like this:
Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 200 non-root parent/child
3 2 200 child
4 2 200 child
5 NULL 300 root parent
6 5 300 child
7 5 300 child
And the filter would look like this:
WHERE GroupId = 200
However out of performance considerations, we would like to avoid if possible filling GroupId for the descendant objects, because as it must be clear from the above, GroupId for a descendant object is quite easily deducible via a stored procedure or UDF (just need to go up the tree until ParentId = NULL). The problem is, I don’t know how to achieve this in a merge replication filter: it would only allow WHERE conditions and joins. I’ve have not had much luck with joins for merge replication in general, but here we have more complex algorithm, because the number of tree levels can be different for every object. And merge replication would not allow using UDF…
View 2 Replies
View Related
Sep 25, 2007
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 4 Replies
View Related
Jan 22, 2007
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 1 Replies
View Related
May 2, 2007
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 4 Replies
View Related
Apr 15, 2008
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 5 Replies
View Related
Jan 26, 2006
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 14 Replies
View Related
Feb 3, 2007
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 1 Replies
View Related
May 17, 2007
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 6 Replies
View Related
Aug 21, 2006
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 3 Replies
View Related
May 24, 2007
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 1 Replies
View Related
May 22, 2007
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 4 Replies
View Related
Jun 15, 2007
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies
View Related
Sep 13, 2007
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
View 2 Replies
View Related
Jan 17, 2002
Hi,
In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don
View 1 Replies
View Related
Aug 22, 2007
Hello,
I have this problem on a Production database.
DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.
Thank you.
View 4 Replies
View Related
Jun 9, 2008
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 2 Replies
View Related
Jun 8, 2006
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 4 Replies
View Related
Aug 24, 1999
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 1 Replies
View Related
Sep 15, 2004
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 2 Replies
View Related
May 3, 2006
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 1 Replies
View Related
Jun 9, 2004
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 13 Replies
View Related
Dec 27, 2005
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 4 Replies
View Related
Feb 6, 2006
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 5 Replies
View Related
Mar 15, 2008
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 2 Replies
View Related
Apr 14, 2008
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 1 Replies
View Related
Feb 17, 2012
I've got an MDX query I need to modify, but not quite sure how to proceed. I essentially know most of tye syntax, but am at a loss as to where the components of the syntax should appear in the query. Here is the original query:
Code:
WITH
MEMBER [Employee].[FTE vs FL].[CF Total] AS AGGREGATE([Client Facing Disciplines])
MEMBER [Time].[Year - Half Year - Quarter - Month - Date].[Trailing 12 Months] AS AGGREGATE(LASTPERIODS(60, STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)))
MEMBER [Time].[Year - Half Year - Quarter - Month - Date].[YTD] AS AGGREGATE(YTD(STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)))
[Code] ....
This is how I'm sure the two lines that say what should be excluded, I'm just not sure where they should go. I believe a FILTER statement is what I need, but not sure how to format it.
[Employee].[FTE vs FL].[Discipline].&[LOYALTY],
[Employee].[FTE vs FL].[Discipline].[Department].&[OLSON PR Chicago]
MDX is definitely not my strong point.
View 4 Replies
View Related
Apr 3, 2008
Hi ,
account_desc_out,Analysis_Desc_out,Sub_Analysis_Desc_out,debit_out,
credit_out,sub_Analysis_Code_out,Analysis_Code_out
-----------------------------------------------
11066-0001-01 GEN 6633 0.00000000 160.000000006633GEN
11066-0001-01 GEN 6633 160.00000000 0.000000006633GEN
When i run the below query the o/p is comming as above:actually no record should come bcz debit amt and credit amt should knock off .can anyone correct the below query:
select
account_code_desc as'Account_Desc_out',
analysis_code_Desc as'Analysis_Desc_out',
subanalysis_code_desc as 'Sub_Analysis_Desc_out',
case when drcr_flag='dr' then sum(isnull(base_amount,0)) else 0.00 end as 'debit_out',
case when drcr_flag='cr' then sum(isnull(base_amount,0)) else 0.00 end as 'credit_out',
subanalysis_code as 'sub_Analysis_Code_out',
analysis_code as 'Analysis_Code_out'
from Analysis_stg_tb as stg with (NOLOCK)
where subanalysis_code_desc='6633-Goverdhan S'
group by subanalysis_code,analysis_code,
account_code_desc,analysis_code_desc,subanalysis_code_desc,drcr_flag
having sum(case when drcr_flag='cr' then isnull(base_amount,0) else 0.00 end) -
sum(case when drcr_flag='dr' then isnull(base_amount,0) else 0.00 end)<>0
order by Account_Desc_out,Analysis_Desc_out,Sub_Analysis_Desc_out
View 1 Replies
View Related
