What is the best way to go about merging the results of two fields in
the same table?
I have two different fields that hold lists of names, some of them
identical, some different. From those I'd like to generate a merged
list with all the distinct names.
For example:
Field1 contains: Jack, Joe, Jim, Paul
Field2 contains: Peter, Paul, Joe, Jim
The result should be: Jack, Joe, Jim, Peter, Paul
If possible, I would like to create a view using two different tables, but with the fields merged (ie. create one table from two tables). The table structures are pretty much the same. Can you do this from a singe SQL statement? Any ideas? My only alternative is creating a temp file that inserts records from both tables, but I want to avoid doing that.
View 6 Replies View RelatedHi all,Here is my problem, I have 3 tables :People-------------IDPeopleFirstnameLastnameCars------------IDPeopleCarnameBoats------------IDPeopleBoatname1 person can have 0 or n car/boatI want to a result set displaying : Firstname, Lastname, NumberOfCars,NumberOfBoatsI have two queries, but i want to merge the results in one. how can i dothis ?This one gives me FIRSTNAME, LASTNAME and CARCOUNT------------------------------------SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,COUNT(dbo.Cars.CarName) AS CARCOUNTFROM dbo.People LEFT OUTER JOINdbo.Cars ON dbo.People.IDPeople = dbo.Cars.IDPeopleGROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastNameThis one gives me FIRSTNAME, LASTNAME and BOATCOUNT------------------------------------SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,COUNT(dbo.Boats.BoatName) AS BOATCOUNTFROM dbo.People LEFT OUTER JOINdbo.Boats ON dbo.People.IDPeople = dbo.Boats.IDPeopleGROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastNameThanks in advancePhil
View 2 Replies View Related
hi,
my first query is:
"SELECT TBL_STOK.stok_adi, TBL_STOK.fiyat1 FROM TBL_STOK INNER JOIN" _
TBL_BARKOD ON TBL_STOK.stok_id = TBL_BARKOD.stok_id " _
where TBL_BARKOD.barkod=@barkod"
second query :
"SELECT TBL_STOKDEPO.fiyat1 FROM TBL_BARKOD left outer JOIN TBL_STOKDEPO ON TBL_BARKOD.stok_id = TBL_STOKDEPO.stok_id" _
where TBL_BARKOD.barkod=@barkod and TBL_STOKDEPO.depo_kod=@depokod "
i want to merge these queries' results.first query returns 2 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1)
second query returns 1 column (TBL_STOKDEPO.fiyat1) .but i want a query that returns 3 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1,TBL_STOKDEPO.fiyat1)
Remove Fields From results from Innerjoin In Sql
--------------------------------------------------------------------------------
Hi, I' ve been struggling for a few days with following problem.
I have a query with 2 tables (below). They are joined by one key field checknr.
I would only like to see field comm from table OTCUser.checklistcomm, but only the last result which are conform to the condition. So the fields in bold shouldn't appair.Could someone help me ?
SELECT MAX(p.Modifdatum) AS modifdatumv, OTCUser.checklistcomm.comm, p.Status, p.station, p.checkitem
FROM OTCUser.checkliststat p INNER JOIN
OTCUser.checklistcomm ON p.Checknr = OTCUser.checklistcomm.checknr
GROUP BY p.station, p.checkitem, OTCUser.checklistcomm.comm, p.Status
HAVING (p.Status = '2') AND (p.station = '00BE104740')
ORDER BY p.station, p.checkitem
modifdatum comm status checknr station
10/04/2007 11:15:20 comment0 2 00BE104740 14
10/05/2007 8:57:45 comment1 2 00BE104740 14
10/10/2007 11:59:35 comment2 2 00BE104740 14
10/04/2007 11:15:20 comment 2 00BE104740 16
10/04/2007 11:15:20 comment 2 00BE104740 17
10/08/2007 17:20:43 comment 2 00BE104740 18
10/04/2007 11:15:20 comment 2 00BE104740 22
10/04/2007 11:15:20 comment 2 00BE104740 24
10/04/2007 11:15:20 comment 2 00BE104740 25
10/04/2007 11:15:20 comment 2 00BE104740 26
10/08/2007 17:20:43 comment 2 00BE104740 26
10/04/2007 11:15:20 comment1 2 00BE104740 27
10/08/2007 17:20:43 comment2 2 00BE104740 27
10/04/2007 11:15:20 comment 2 00BE104740 31
If I query sql server I get 10 results. But when I use if (myReader.Read()) I get only 7 results. I found that there was a Null field in the DB. I changed it and it worked.
The problem is I don't want to touch the database and set all null fields. There must be a way to get all results including the Null using sqlDataReader so that if (myReader.Read()) is used it does the right comparison.
// This code is called 10 times with a select * from where item="xxx"
P21Conn.Open();
SqlDataReader myReader = cmd.ExecuteReader();
if (myReader.Read()) {
thanks
Rod
Hi All,
I have two SQL queries that we would like to automate. Ideally we want them to both be scheduled to run and dump their results to a single Excel spreadsheet with two workbooks, one for each query
Is it possible to do this? If not, sending each query to a seperate XLS or CSV file would be OK
Here are the queries:
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, p21_view_unvouched_po_currency_report.po_line_number, po_line.created_by, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home
FROM P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report, P21.dbo.po_line po_line
WHERE po_line.po_no = p21_view_unvouched_po_currency_report.po_no AND po_line.line_no = p21_view_unvouched_po_currency_report.po_line_number
and
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, contacts.last_name, p21_view_inventory_return_hdr.buyer_id, p21_view_unvouched_po_currency_report.po_line_number, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home
FROM P21.dbo.contacts contacts, P21.dbo.p21_view_inventory_return_hdr p21_view_inventory_return_hdr, P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report
WHERE p21_view_inventory_return_hdr.return_number = p21_view_unvouched_po_currency_report.unvouched_document_no AND contacts.id = p21_view_inventory_return_hdr.buyer_id
Hi
I have a strange issue here.
Both the input datasets of the merge join are sorted . Everything works fine if the dataset doesn't contain speacial characters like - (hipen).
This is the sorted dataset.ASI311_3ASI311_3ASI312ASI311ASIKit1ASIKit1
Merge Joiner has to look for ASI311 and finds it in the list and everything works as expected. But if the dataset is changed to
ASI311_3ASI311_3ASI-312ASI311ASIKit1ASIKit1
Then it doesn't find the ASI311 in the above dataset ?
Only change between the two datasets is the - (hipen) which is added in one of the field. Does SSIS has a differenent precedence of the special characters than that of the Database. Both the datasets are on SQLServer.
Can anyone point out the issue here?
Thankyou
Vidya
I just read something interesting here: http://www.informit.com/library/content.asp?b=STY_Sql_Server_7&seqNum=101
A column type that helds an expression, and in queries returns the results.
That sounds excelent in my database, to save some code in my client applications. E.g adding price totals, and taxes of an order.
I can't find any info about this in later versions of SQL server. Is this not possible anymore?
Perhaps I was seeing things earlier, and it's really a minor thing, but when I display query results in Management Studio, isn't there a setting that turns the background of cells containing NULL to a pale yellow color? I could've sworn it used to do this, and I've searched high and low for a preference setting, but no joy.
Does this setting exist?
Thanks,
Dave
I have implemented replication between 2 servers (SERVER1 AND SERVER2) and for test purposes i just have 1 table(Table1) in the publisher.
View 1 Replies View RelatedI have database on SQL Server 2000 set up with a merge publication.This publication is configured with a number of dynamic filters toreduce the amount of data sent to each client. Each client has ananonymous pull subscription. The merge process can be triggered by thewindows sync manager and my application.To improve performance I have created some helper tables to hold themapping between user login and primary keys of selected entities.For the replicated data to be correct the contents of the helper tablesneeds to be up to date.I need to fire off a stored procedure on the publisher beforereplication starts to verify that this data is up to date. I can notsee any documented way of doing this however I have been experimentingwith some unorthodox systems.Firstly has anyone any ideas?I have been considering adding a trigger to some of the tables used bythe Microsoft replication code - yes I know this is very nasty.My problems arise because executing this stored procedure will causesome data to be updated. In updating data we could create a newgeneration in the database. I must therefore run my stored procedurebefore any the Microsoft code makes any generation checks / updates.Anyone done anything similar, Anyone have any better ideas?Any comments would be gratefully received.
View 1 Replies View Related
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedI am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
Hi,
I'm using merge replication to maintain a backup copy of my main (publisher)MSDE database. A push subscription periodically (1 per minute) updates the backup DB.
It's intended that if the main db goes down then the backup (subscription) db can be configured as a publisher. This must all be performed via scripting.
The initial configuration of the main publisher and subscription is controlled via scripting, which works fine.
The problems occur when I try to configure the subsciber to become a publisher. A script is executed on the subscriber but fails at the point when it's configuring the publisher detail. The error is something like "unable to configure a publication for a database setup as an anonymous subscription".
I'm guessing that there are subscritpion artifacts added to the database which need to be removed before it can be configured as a new publisher.
Please help,
Jez W
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View RelatedI have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies View RelatedHi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
BrianAustinHope83432230.7187500.7352941.15625014612201221
GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519
MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218
RobBennettMelville83029240.8275860.8333331.55172411904102117
AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
View 6 Replies View RelatedPardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
Hi,
I came to know that MERGE INTO is not supported in SQL Server 2005. Is there any work around for MEREGE INTO in SQL Server 2005, which does not degrade the performance to much extend. I mean is there any other way we can implement the same stuff.
Thanks in Advance
Jiju Jagadish
I have the following structure of 2 XML documents:
Code Snippet
<?xml version="1.0" encoding="UTF-8"?>
<Annonce xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="">
<P Num="100" NumP="37" TypeP="NUM">
<E Num="XYZ" Date="22/06/2007" PE="15" >
<S Code="75">
<C Num="24">
<Q N="0" L="1">
</C>
...
</S>
</E>
</P>
</A>
How do I merge these 2 documents using the XML task in SSIS? I think I need to extract to remove the <?xml...> line from the 2nd document, but I'm really not sure what to put in the XPathString property. My documents should be the same except for the <S> tags, which will be different. The other elements should be the same. Any help would be much appreciated. Thanks!
Hello,Is there a way to merge the result of a query into one row?For example, let say I want the a list of name of people of 20 year soldMy result will look like that: NAME1 john2 Mike3 Craig I would like to have the result in one row: NAME1 John, Mike, CraigIs there a way to do that?Thanks
View 3 Replies View RelatedHi everbody,
I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.
Thanks in advance