To Fetch Common Items From Two Rows
Feb 22, 2008
Suppose a table contains fields such as member_id,item1,item2 and item3.Then how can we fetch common items between two rows.
eg:- mem_id item1 item2 item3
1 a b c
2 b c d
3 d f g
common items between first 2 rows is 'b' and 'c'.
How can we write a query for this.
thanks in advance
View 1 Replies
ADVERTISEMENT
Nov 15, 2013
I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.
Checkbox tag:
<asp:CheckBoxList ID="cblGroup" Style="vertical-align: baseline" runat="server" CssClass="chkbox">
</asp:CheckBoxList>
Select Query:SqlCommand cmd = new SqlCommand("select * from AppInvent_Test where Designation= '" + cblGroup.SelectedValue + "'", con);
View 2 Replies
View Related
Apr 4, 2008
Dear Friends,
Is there any way to display a table data separately like odd rows and even rows?I dont know this is possible or not?If it is possible means how can i achieve it?Please guide me a proper way.
Thanks all!
kiruthika
http://www.ictned.eu
View 3 Replies
View Related
Oct 30, 2005
Hell All
1 fetch -- 0 -1000 rows
2 fetch -- 1001 -2000 rows
1 fetch -- 2001 -3000 rows
1 fetch -- 3001 -4000 rows
so no
I am really dying to have a sql statement to fetch like this manner...
i know using TOP keyword to fetch first 1000 but how can i fetch the next
1000 .
Thanks in advance
View 2 Replies
View Related
Nov 29, 2007
Hi,
I am trying to create a SSIS package, which will extract data from a SQL server view and populate the data in our local SQL server database tables. My objective is to get the data from the view such that only inserted and updated rows are fetched from the view.
Note: the view does not expose any updated date type of column thru which I can check. So I guess I have to compare each and every field with my destination table row's fields.
I would appreciate any suggestions on how to approach the problem.
Thanks in advance.
View 1 Replies
View Related
Aug 9, 2014
I don't understand using a dynamic cursor.
Summary
* The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats.
* As far as I know a fetch-next-statement always returns a single row?
* Using a static cursor works as aspected.
* Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function
create partition function fnTestPartition01( smallint )
as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
[Code]....
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
View 8 Replies
View Related
Sep 15, 2007
Hi,
I am having problem with the CRecorset::Update() function.
I have declared a CRecordset object in client Application in VC++6.0.
I open the recordset in dynamic mode.
Before opening the recordset , I have obtained the UPDLOCK on the base table.( Obviously this UPDLOCK is in transaction).
When I try to call CRecordset::Update() it gives me exception 16931(There are no rows in the current fetch buffer).
I have define a clustered index on this table.
But I still get the exception 16931 on calling CRecorset::Update().
Any help is much appreciated.
Thanks in advance.
View 2 Replies
View Related
Aug 28, 2014
I have a table in the following format:
Matter IDIndexDescription
00103-000486Litigation
00103-0004857Trial
00245-000156Deposition
00245-0001557Hearing
I need each matter in a single row with descriptions as columns:
Matter IDDescription_6Description_57
00103-00048LitigationTrial
00245-00015DepositionHearing
View 4 Replies
View Related
Nov 18, 2007
Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department: Sells:
1 Meat
1 Rice
1 Orange
2 Orange
2 Apple
3 Pears
The Data I would like read separated by Semi-colon:
Department: Sells:
1 Meat;Rice;Orange
2 Orange;Apple
3 Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
View 2 Replies
View Related
Jan 31, 2006
I am working with the following two tables:
Category(NewID,OldID)
Link(CategoryID,BusinessID)
All fields are of Integer Type.
I need to write a stored procedure in sql 2000 which works as follows:
Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)
Then for each rows fetched from last query, execute a update query in the Link table.
For Example,
Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..
UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID
Please help me with the code.
Thanks,
anisysnet
View 1 Replies
View Related
Apr 17, 2007
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
View 3 Replies
View Related
Feb 25, 2015
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
View 8 Replies
View Related
Apr 10, 2015
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). In some cases, items can be components of another item (called "Kits"). In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". In our Items table, we have a field called "Parent_Item_Id". Records for Child Items contain the Item Id of the parent. So a sample of my database would be the following:
ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. So an example of the report would be the following:
Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100
How I can setup my report to group properly?
View 6 Replies
View Related
May 12, 2006
I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)
idserialisOk
-------------------
2AAA1
3BBB0
5dfds0
6CCC1
7fdfd 0
8AAA0
9CCC0
I want to DELETE each Row IN
SELECT doublons.serial, Count(doublons.serial) AS 2Times
FROM doublons
GROUP BY doublons.serial
HAVING Count(doublons.serial)>1
and WHERE isOK = 0
in my exemple , after deleting, my table must look like
idserialisOk
-------------------
8AAA1
9CCC1
3BBB0
5dfds0
7fdfd0
thank you for helping
View 10 Replies
View Related
Mar 13, 2007
I'm building an exception management system into my application.
What are the most common errors when working with a DAL and a SQL server.
The biggest problem can be Database connection.
My questions:
* Are all SQL related errors catched by the SqlExeption class?
* Which are the most common sql errors?
View 1 Replies
View Related
Mar 31, 2004
I have a stored procedure I use to fill my datagrid..... While doing this is there a way to get the number of records returned...
I use a datareader........any help
View 2 Replies
View Related
Aug 14, 2005
I just inherited a Java application with a fairly complex data modelthat does not yet have any indexes except those on primary keys. It isstill in development and before I get to do any performance testing Iwant to put some default indexes on it. I realize that this a veryvague request but it's better than nothing so I want to start withthose that are generally a good idea and then tune those areas thatrequire more fine grained approach. By the way, I'm mostly a Javaprogrammer so please forgive my DB ignorance and I thank you for anyhelp.Since the full schema is quite large I will use a simple example (fullycited below my questions). Here is list of indexes that I think wouldbe a good idea. If anyone can add to it or comment on my questions Iwould appreciate it.1. Index on primary keys in all three tables. My understanding thatthis indexing happens automatically just by declaring that a column isa PK.ALTER TABLE employees ADD PRIMARY KEY (emp_id);ALTER TABLE employee_addresses ADD PRIMARY KEY (address_id);ALTER TABLE departments ADD PRIMARY KEY (dept_id);Question: Does index get created automatically because this is a PK?2. Index on foreign keys in the children tables to prevent deadlocksand lock escalations.CREATE INDEX fk_index ON employee_addresses (emp_id)3. Indexes on common queries on all three tables.CREATE INDEX common_query_idx on employees(last_name, first_name,position)CREATE INDEX common_query_idx on departments(last_name, first_name,position)CREATE INDEX common_query_idx on employee_addresses(street, city)Question: Given that the searches can be on any field separately andin any combination should I also put an index on each columnindividually or will the composite index take care of individualsearches as well? For example, will the above indexes be sufficientfor the following SELECT:SELECT e.last_name, e.first_name from employees e, departments d,employee_addresses ea, dept_employees de WHERE e.emp_id = de.emp_id ANDd.dept_id = de.dept_id AND ea.emp_id = e.emp_id AND e.position ='master chief' AND d.dept_name = 'galactic affairs' AND ea.city='LosAngeles'4. Unique index on the association table. Again this is accomplishedusing PKALTER TABLE dept_employees ADD PRIMARY KEY (dept_id, emp_id)Question: Is the index on (dept_id, emp_id) automatic because of PK?5. The association table has to go both ways and PK takes care only ofthe first half. Thus add an index to go the other way.create unique index dept_employee_idx on dept_employee(emp_id,dept_id)Question: should I use INDEX ORGANIZED TABLE?Question: should I have UNIQUE qualifier on the second index given thatPK already takes care of it?Thanks,Robert===== EXAMPLE ======1) An employee can be in many departments and a department can containmany employees.2) Common searches for employees are on last_name, first_name,position, department_name, department_location separately and in anycombination.3) There are common searches for departments that contain certainemployees e.g. find all departments containing John Smith.CREATE TABLE employees(emp_id INTEGER NOT NULL,last_name VARCHAR(50) NOT NULL,first_name VARCHAR(25) NOT NULL,position VARCHAR(10) NOT NULL);CREATE TABLE employee_addresses(address_id INTEGER NOT NULL,emp_id INTEGER NOT NULL,street VARCHAR(50) NOT NULL,city VARCHAR(25) NOT NULL,);CREATE TABLE departments(dept_id INTEGER NOT NULL,dept_name VARCHAR(50) NOT NULL,dept_location VARCHAR(25) NOT NULL,);CREATE TABLE dept_employees(dept_id INTEGER NOT NULL,emp_id INTEGER NOT NULL,);ALTER TABLE employee_addresses ADD FOREIGN KEY (emp_id) REFERENCESemployees(emp_id)ALTER TABLE dept_employees ADD FOREIGN KEY (emp_id) REFERENCESemployees(emp_id)ALTER TABLE dept_employees ADD FOREIGN KEY (dept_id) REFERENCESdepartments(dept_id)
View 7 Replies
View Related
Aug 19, 2005
Hi, I would like to setup a common password for all the MS SQL Server Users. Is there any generalized script for the same ? Thanks In Advance.
View 7 Replies
View Related
Jun 12, 2004
Is it possible to have fetch within a fetch? I am getting this error message "A cursor with the name 'crImgGrp' does not exist." So i separate the process into two stored procedures?
CREATE PROCEDURE TrigSendPreNewIMAlertP2
@REID int
AS
Declare @RRID int
Declare @ITID int
Declare @intIMEmail varchar(300)
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID
Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @RRID
open crImpGrp
fetch next from crImgGrp
into
@ITID
while @@fetch_status = 0
EXEC TrigSendNewIMAlertP2 @ITID
FETCH NEXT FROM crImpGrp
into
@ITID
close crImpGrp
deallocate crImpGrp
while @@fetch_status = 0
FETCH NEXT FROM crReqRec
into
@RRID
close crReqRec
deallocate crReqRec
GO
View 1 Replies
View Related
May 28, 2008
Hi
I was studying Common Table expression in Sql server 2005.
I have written the code
Declare @PictureArray as varchar(200)
Set @PictureArray = '';
with UserProfile_CTE(UserPicture)
As(
select @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
select @PictureArray
)
select * from UserProfile_CTE
But I am getting the error
Incorrect syntax near '='
I am getting the error in the lineselect @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
But I don't know the reason for this,
Kindly advice
Regards
Karan
View 3 Replies
View Related
Jan 7, 2006
hi i am using MS SQL Server 2000, i want to delete the common row from a table
table name EMP
rows
Eno Name Sal
1 Jim 1000
2 Mark 1200
1 Jim 1000
2 Mark 1250
1 Jim 1300
no primary key defile in to the table ....
i want to delete the row of
1 Jim 1000
please anybody can can help me ...
samarjit
View 1 Replies
View Related
Dec 1, 2005
I have a database with 10 tables
I have a field named CustID .... I want to find out how many tables have this field CustID
Now one way was check up the design of every table & see whether its there or not (the field)
is there any query through which we can get this info Please help
View 4 Replies
View Related
May 11, 2006
In my experience of going through plenty of interviews, I have came across a question that I am reminded of by someone's signature.
The common question in this instance is:
What is the advantage of a cursor over a stored procedure? Vice Versa?
I could only, in my so few experiences, have answered that stored procedure consumes less system resources than a cursor. The advantage a cursor has is record by record examination and looping capabilities.
Needless to say that whenever asked those questions, I haven't landed the position. So I'm wondering, what is the more appropriate answer?
View 20 Replies
View Related
Aug 8, 2006
Hi,
Do anyone know or have a suggestion how to get commun columns name between several table in a SQL server database
For example I have table_1(name, age, school) table_2(name, address, city) and table_3(name, department, company)
name is the common column name (I don't care about the data) in this case. Is there a way to do it simple and easy ?
THANK YOU
View 6 Replies
View Related
Sep 21, 2007
Hi - I'm trying to construct a select statement from the following tables(see example)
_________________________________
|Customer | Product |Shop |
|----------|----------|---------|
|Customer1 | Milk |Dairy |
|Customer2 | Cream |Dairy |
|Customer3 | Milk |Dairy |
|Customer1 | Trainers |Sports|
|Customer2 | Football |Sports|
_________________________________
REQUIRED RESULT SET: Milk, Cream.
Basically I want to select all of the Products (no duplicates) where EVERY customer(1, 2 & 3) have bought a from a common shop type: i.e 'Trainers' and 'Football' should not be selected as Customer3 has not bought any goods from a sports store.
This is an example of a larger problem where there can be numerous products, customers and shops. Here's a DESCRIBE of the relevent columns in each of the tables:
CUSTOMER:
customerID
PRODUCT:
productID
customerID
shopID
SHOP:
shopID
This looks like it should be easy but my SQL isn't the best ;-)
I'd really appreciate any help you could give!!
Cheers!
View 1 Replies
View Related
Jan 14, 2008
I try to find the best design for a SQL Server 2005 database structure.
I have the following 'objects':
Facilities
Departments - 'dependents' of a Facility
Users
Addresses - each of the above can have none or more Addresses.
The basic tables design would be:
CREATE TABLE [dbo].[Facility](
[cFacilityID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED ,
[cFacilityName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Department](
[cFacilityID] [nvarchar](5) NOT NULL FOREIGN KEY REFERENCES [dbo].[Facility] ([cFacilityID]) ON UPDATE CASCADE ON DELETE CASCADE,
[cDepartmentID] [nvarchar](3) NOT NULL,
[cDepartmentName] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED ( [cFacilityID], [cDepartmentID])
)
GO
CREATE TABLE [dbo].[User](
[cUserID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED,
[cUserName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Addresses](
[pkAddress] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[cAddress] [nvarchar](255) NULL,
[cZip] [nvarchar](5) NULL,
[cEmail] [nvarchar](255) NULL
)
GO
The question regards the Addresses table:
How can I design the Addresses table(s) so I can enforce relational integrity and cascade update and delete for each Facility, Department and User tables?
I figured out the following options, but none achives my requirements:
1. An Address table for each 'object': FacilityAddress, DepartmentAddress, UserAddress with the corresponding foreign key in each Address table. This solution duplicates all the addresses fields (which are more than I showed in the example).
2. A common Addresses table with a discriminator field (1=Facility, 2=Department, 3=User). This doesn't allow me to enforce referential integrity with constraint, maybe only with triggers.
3. A common Addresses table with a foreign key for each 'object (fkFacility, fkDepartment, fkUser) allowing nulls. In this case, the 'dependence' between Facility and Department stops me to enforce cascade deletes/updates on both Facility and Department foreign key (circular...).
4. An intermediate (link) table between each 'object' and Addresses table with the foreign keys (Facility, Addresses). Again, no referential integrity.
So, is there a method to design such structure?
PS. Please keep in mind that this is just a simplified example (reduced at the key information) of my real structure.
Thanks,
Ioan
View 8 Replies
View Related
Oct 2, 2007
At the risk of asking a stupid question -Is anyone familiar with either a query against the systables or maybean outside tool that will provide a list of the most common joins thathave been made in user created views? I'm not talking about tablerelationships that are established at the database level, but rather,I'm referring to the ability to find which joins have been utilized inpoorly constructed databases where no relationships were establishedin the first place.Thanks in advance -Cindy T.
View 2 Replies
View Related
Jul 20, 2005
What is the SQL Server equivalent of DB2 common table expressions? Forexample,with gry(year,count) as(select floor(sem/10),count(distinct ssn)from gradesgroup by floor(sem/10))select year,sum(count) Head_Count from grygroup by yearhaving year >= 1980;N. ShamsundarUniversity of Houston
View 2 Replies
View Related
Dec 18, 2006
I see that part of SP2 is the new CC certification -- can anyone give me some more details. What level is it at? EAL4? I can't seem to find the certificate on the CC Web site but it could be because it's not SP2 yet.
View 3 Replies
View Related
Jan 22, 2008
Hi there!
I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.
When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.
First, what do you think of that implementation?
We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...
What is the common solution for this need?
Thanks
Frantz
View 4 Replies
View Related
Jul 19, 2007
The comapny i work for has a server running the following.
Opteron 246x2
2 Gig memory
320 Gig Sata 2 drives
Windows 2003 Standard Edition
SQL Server 2005 Express Edition. The Free one.
There are approx 10 users that connect to the server.
There are two programs which seem to use sql server. Act 7.0 and service ceo.
When the computer is rebooted its at 0% for sqlserver.exe
Than when all connect it maxes it 50% and its steady there. Seems Service ceo affects it the most.
Told by comapny need to buy full blown sql server to resolve problem. But i dont think this is the problem.
Questions: Is it common for server to be at 50% all the time with sql server running?
And if its not is there a way to reduce the sql cpu usage.
I am new to sql server and have done alot of research and fixes. Ive unsitalled and reinstalled all sql instances and done the tweaks suggested. Any fresh ideas would be great thanks.
John
View 3 Replies
View Related
Feb 14, 2008
I need to get the 5 most common occurences from a table. Not really sure of the SQL statement I would use to do that. Would someone be kind enough to throw out a select statement that would do this? psuedo code would be fine, I just need to know the jest of how to do it. I originally thought Select Top 5 would work, but then I actually started thinking and realized it wasnt it. So now im stuck. Any help would be appreciated.
Thanks,
Steve
EDIT: I should add that this will be coming from a view with multiple tables..thanks
View 3 Replies
View Related
