I want to created a filter that filters on some aggregated data, but keep getting the error listed below. Is there a way around this?
[rsAggregateInFilterExpression] A FilterExpression for the data set €˜DataSet1€™ includes an aggregate function. Aggregate functions cannot be used in data set filters or data region filters.
Build complete -- 1 errors, 0 warnings
i have two tables with primary key and foreign key
how can i filter the data to only show rows which do not have any foreign key
like
table1 table2
Primary key foreign key
1 5
2 4
3 3
4
5
how can i show rows
1 and 2 in table1 ?
Thanks
Hello, If I have a table like this
ID Code
AAAAA X1
AAAAA X2
AAAAA X3
BBBBB X1
BBBBB X2
CCCCC X1
CCCCC X2
CCCCC X3
Is there a way w.out using TSQL to filter the "ID" number which has all three "Code" of X1,X2, X3 associated with it.
In the above example I would only want "ID" AAAAA and CCCCC returned. Thanks in Advance
I want to filter rows in script transformation. Since there is no isnumeric function in SSIS, i want to use script transformation to remove or block some rows in script. My question is how to remove or block some rows from data flow?
For some other reasons, script transformation is my preferred choice for this problem..
Thanks
Saeed
Please refer to the below query. I want to filter inner join rows based on outer query column value (refer to bold text).
SELECT M.MouldId, SI.StockCode, MI.Cavity, MI.ShotCounter, CQ.SumOfCastedQty  as CastedQty, MI.CounterStartupÂ
FROM Â Â Â Â MouldItem MI
JOIN (SELECT JD.MouldId, JC.StockCode, SUM(JS.CastedQty) AS SumOfCastedQty
FROM JobCasting AS JS INNER JOIN JobCreationDet AS JD ON JS.JobDetId = JD.Uniid INNER JOIN JobCreation AS JC ON JD.JobIdx = JC.Uniid
[Code] ....
Good morning,
View 7 Replies View RelatedI have a table:
declare tableName table
(
uniqueid int identity(1,1),
id int,
starttime datetime2(0),
endtime datetime2(0),
parameter int
)
A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).
I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.
If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:
merge ( select * from tableName where id = 10 ) as target
using ...
and I can't have:
merge tableName as target
using [my query] as source on
source.id = target.id and
source.starttime = target.startime and
source.endtime = target.endtime
where target.id = 10
...
This means I cannot filter the set of rows in the target table to a handful of records where id = 10.
I'm running SQL Server 2008 Feb08 CTP and I've got a tablix with column groupings and row groupings which works nicely, I'm a becomming a big fan of the tablix.
However sometimes the filter I have on a column group returns no records and the whole column group disappears.
Is there any way to make this column appear with but with empty cells as I have another tablix down the page with the same columns and I want the columns to all line up on the multiple tablix.
In Version 2005 I used to achieve the equivalent of a column group within a table by adding a list with filter to the cell, this meant the column always was shown, the tablix allows does this much more elegantly but I'll have to go back to the old method if I can't make the column remain.
Am I just missing a simple option?
Nathan
Hi All,
I am working on SQL server 2005 Reports.
I have one report, one dataset is assigned to it, and one table which displays it.
Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox.
I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters?
I want to display parent/child records on report. I am not getting the proper solution.
The data is like this:
Sequence ItemCode IsParent
1 XYZ 0 'do not have child record
2 PQR 1 'have child records with sequence no 3
3 ASD 0
3 AFDGE 0
3 VDC 0
4 ASR 1 'have child records with sequence no 5
5 ASR 0
If IsParent = 1, that record has child records with sequence = parent sequenece + 1
I think u can understand the data I need to bind, and it is like:
XYZ
+ PQR
ASD
AFDGE
VDC
ASR
On + click we can do show/hide of child records.
I m not getting how to achive this in SQL server report. Can u give some hint?
Thanks in advance
Pravin
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
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)
I have 3 tables...
JobRequirements (A)
JobID int
QualificationTypeID int
EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int
Employee (C)
EmployeeID int
EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.
Any sort of help would be highly appreciated.
Thanks,
I have a conditional split in an SSIS package - one split is where if rows are returned according to a specific rule, then insert those rows into to a Recordset Destinationm which points to a variable of Object type.
How I can use this variable to email fellow users. Â For example, what I would like is if ANY rows are returned to the Object variable (1 or more), then I would like to execute an email SP that we have on our server.
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
Any sort of help would be highly appreciated.
Thanks,
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.
Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID )
and the newly inserted row with a insert time stamp
so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond.
example of Data would be like
exisiting data
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
New row to insert would be
12 DDS 15 M DFG ST 4/5/07
the data should reflect
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 4/5/07
12 DDS 15 M DFG ST 4/5/07 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
Please provide your input as much as you can even though it might not be a 100% solution.
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?
View 3 Replies View Related I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
I am facing an issue that Data flow task failing after loading 29000 rows out of 2lakhs rows.
I am loading data from .csv file to OLE DB Destination.
This data flow task is placed inside For each loop container.
is this issue because of any performance issue in SSIS packages such as buffer size.
find the error below:
DFT Load Data from FlatFile:Error: The conditional operation failed.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.Â
The "DER Add Calc Columns" failed because error code 0xC0049063 occurred, and the error row disposition on "DER Add Calc Columns.Outputs[Derived Column Output].Columns[M_VALUE_NUM]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "DER Add Calc Columns" (48) failed with error code 0xC0209029 while processing input "Derived Column Input" (49). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[code]....
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger   Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
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
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.
I need a query that returns every n record from the database and the last record.
For every n-th is easy, but I need a
WHERE Index % n = 0
but I need a last one too.
Any Help.
Hi I am wandering if it is possible to achieve the following:
I am using Reporting Services and Reporting services I got a report with a text parameter.
I would like the user to introduce AA and find the product AA and when he introduces AA* it finds AA, AAD, AAC...
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?
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 RelatedDear All
I have a table with the following structure in sql server 2005
create table app(
sno int,
name varchar(50),
add varchar(50),
city varchar(50),
state varchar(50)
)
it contains the follwing data
------------------------------------------
sno name add city state
------------------------------------------
1 mark street no1 newcity newstate
2 mark street no1 newcity newstate
3 mark street no1 newcity newstate
4 mark street no1 newcity newstate
5 mark street no1 newcity newstate
6 mark street no1 newcity newstate
7 mark street no1 newcity newstate
8 mark street no1 newcity newstate
9 mark street no1 newcity newstate
10 mark street no1 newcity newstate
11 mark street no1 newcity newstate
12 mark street no1 newcity newstate
13 mark street no1 newcity newstate
14 mark street no1 newcity newstate
15 mark street no1 newcity newstate
16 mark street no1 newcity newstate
17 mark street no1 newcity newstate
18 mark street no1 newcity newstate
19 mark street no1 newcity newstate
20 mark street no1 newcity newstate
----------------------------------------
I want to retrive previous 5 records, next 5 records and the record that meet the where condition of a select query.
When I run
select sno,add,name,city,state from app where sno=7
I want the following result
------------------------------------------
sno name add city state
------------------------------------------
2 mark street no1 newcity newstate |
3 mark street no1 newcity newstate |
4 mark street no1 newcity newstate | -- previous 5 records
5 mark street no1 newcity newstate |
6 mark street no1 newcity newstate |
7 mark street no1 newcity newstate --- searched record
8 mark street no1 newcity newstate |
9 mark street no1 newcity newstate |
10 mark street no1 newcity newstate |--- next 5 records
11 mark street no1 newcity newstate |
12 mark street no1 newcity newstate |
----------------------------------------
if there is a method to get the above result set, kindly post the query.
In a SQLDataSource, the following line throws an error as an undefined function. FilterExpression='left$(lname,1) = "D"'
Isn't this a standard VB function and why does it produce an error. How do I fix?
Thanks
Hi,
I am new in framework 2 and I can't find a way to filter the sqldatasource.
I have an sqldatasource control that retrive data from data base-"Select * from myTable"
I set the fiterExpression property-sqlDataSource1.FilterExpression="ID='" + strID + "' " ;
I don't know how to continue from here.If I bound the sqlDataSource1 to a control like gridView it works good and I see the filter oparation. but I want to get the result set in the code and loop threw it like I did with ver 1.1 with sqldataReader:
While sqlDatareader1.Read { myCode ... }
How can I do it with sqlDataSource ?
Thanks,
David
I have two tables A and B. I want to insert data into tables C and D based on join between A and B (A.column = B.column). What is the best way to accomplish this type of task?
IE-------> If column = xyz then insert into table C
IF column = abc then insert into table D
ELSE do_not_insert
Please help.
Regards
Ziggy