|
If you are dealing with multiple sources, you need to consider using the Factory pattern. Fortunately for you, MS has spent a lot of time and money extracting the interfaces out into relevant factories. This article is a small starting point for you: http://davidhayden.com/blog/dave/archive/2006/04/04/2900.aspx[^]
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks alot I do appreciate your help
Hassan Amaar
|
|
|
|
|
Not a problem. I'm glad to help with questions like this.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hello,
I have one data table with fields SerialNo, manufacturer, and model. Called AllParts.
Hello,
VS 2005
I have 3 other data tables (software, components, equipment) that have those same fields plus some extra fields.
I want to fill this allParts with the serialNo, manufacturer, and model from the other tables.
So allParts will contain rows from software, components, equipment
a bit like copying all the contents from the 3 tables and merging them into a single data table called allParts.
Can anyone help me with this.
Many thanks,
Steve
|
|
|
|
|
You could create AllParts as a view and then UNION the data from your three tables into here.
|
|
|
|
|
hi
see this link u will get an idea
http://www.vbdotnetheaven.com/UploadFile/gulatisa/MergeTables11272006232307PM/MergeTables.aspx
DataSet dsContract = new DataSet();
DataSet dsMergedContract = new DataSet();
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "usp_getContracts";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "UWYear", DbType.Int32, UWYear);
db.LoadDataSet(dbCommand, dsContract, "ContractDetail");
dsMergedContract.Tables.Add("ContractDetail");
for (int i = 0; i < dsContract.Tables.Count; i++)
{
dsMergedContract.Tables["ContractDetail"].Merge(dsContract.Tables[i]);
}
return dsMergedContract
|
|
|
|
|
If this is just done at the run time and only resultset is required, then I would suggest create an stored procedure which does the following
1. Create a temp table in SP
2. fill this table one by one with the required information
3. execute a select query on this temp table
Hope this solves your purpose
Regards,
Jaiprakash M Bankolli
jaiprakash.bankolli@gmail.com
|
|
|
|
|
hello guys.
i have a question i was hoping someone would be able to help me with. here is my problem:
i have a database with 3 tables, one called CustomerInfo, one called Invoices, and one called CreditNotes. when i link and join the three together i get a new table that has (and looks something like) this:
(please ignore the dashes, they are only used to preserve spacing)
CustomerID CustomerName TransactionType InvoiceValue CreditNoteValue
-----1----------John----------Invoice----------100--------------------
-----2----------Jane--------CreditNote-----------------------200------
what i was wondering is if it is possible to query the DB to get something like this:
CustomerID CustomerName TransactionType Value
----1----------John----------Invoice-------100
----2----------Jane---------CreditNote-----200
the idea is that i would like to merge the two columns (invoice & credit note together) into one Value column. since it is never possible for a TransactionType that is an Invoice to have a value in the CreditNoteValue field or a TransactionType that is a CreditNote to have a value in the InvoiceValue field, there is no possibility of a clash in the Value field (by clash i mean having values for both InvoiceValue and CreditNoteValue and not knowing which one to put in the Value field).
so, if this has made any sense to anyone, can it be done? i also understand that it may not be possible to do in one step and may require multiple sql queries or intermediate tables.
thanking you all in advance for your time. hope to hear from someone soon... 
kind regards, Alex.
"All your base are belong to us..."
- The Nameless One (2110 A.D.)
|
|
|
|
|
Try this:
SELECT CustomerID, CustomerName, TransactionType,
CASE TransactionType WHEN 'Invoice' THEN InvoiceValue
ELSE CreditNoteValue
END AS Value
FROM CustomerTable
|
|
|
|
|
If the InvoiceValue and CreditNoteValue fields are nullable, you could always do this:
SELECT CustomerID, CustomerName, TransactionType,
COALESCE(InvoiceValue, CreditNoteValue, 0)
FROM
... Coalesce looks for the first non-null value, so if InvoiceValue is null it picks up CreditNoteValue. If both are null, it falls back to printing 0.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Is this feature Coalesce available in MS SQL 2005? I though it is available only in oracle ....
Regards,
Jaiprakash M Bankolli
jaiprakash.bankolli@gmail.com
|
|
|
|
|
Jaiprakash M Bankolli wrote: Is this feature Coalesce available in MS SQL 2005? I though it is available only in oracle ....
Nope - it's in SQL Server as well. Has been since 2000.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Its in every version of SQL server I believe. Certainly in 2000
|
|
|
|
|
How to populate data in Excel sheet from database.
|
|
|
|
|
Now, repeat after me. "Google is my friend. Google has the answer. I have Googled it and found the following." Clickety[^]
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O'Hanlon wrote: repeat after me. "Google is my friend. Google has the answer. I have Googled it and found the following."
Very good
|
|
|
|
|
Hi
I have an sql Query to select 2 different date.. "select * from offerdate >= '31/02/2006' and offerdate <= '04/04/2007' " ... this query does not take the TO date (04/04/2007)
if i give the next day date the '05/04/2007' then it is taken....
help ....
thanks in advance
|
|
|
|
|
hi,
try with ..
select * from convert(fromdate,datetime,101)>='02/31/2006' and
convert(todate,datetime,101)>='04/04/2006' and
regards
jijo jose
|
|
|
|
|
hi jo
I want the sqlquery in dmy format....
i used setdateformat dmy ; and my sql query..
|
|
|
|
|
DateTime data type stores both date and time.
when data is given like '04/04/2006' is taken as 04/04/2006 0hrs 0min
that is why dates stored having time greater than 0hrs are not listed.
solution :
1. take dd, mm, yyyy seperately from the column
2. concatenate them into one string
3. convert to datetime using CONVERT function
CONVERT (data_type [(length)], expression [, style])
OR
you can use the next date as you have already did.
Regards
KP
|
|
|
|
|
I am not able to add a foreign key in a table in SQL Server 2005 Compact Edition using ALTER TABLE command.I had tried adding in the same way as in SQL Server 2005 but it dint work out. Kindly some one guide me.
|
|
|
|
|
I tried using this type of syntax
ALTER TABLE Orders ADD FK_Customer_Order FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
Even this failed....Waiting for reply from Coding Gurus....
|
|
|
|
|
Hi,
I have two tables as following:
Table1:
AccountID Symbol Fee Price
1 AAA 10 150
1 AAA 20 250
1 BBB 30 350
2 AAA 40 450
2 CCC 50 550
3 DDD 60 650
4 EEE 70 750
6 HHH 15 250
Table2:
AccountID Symbol Share Dollar
1 AAA 100 1100
1 BBB 200 2200
2 AAA 300 3300
2 AAA 400 4400
2 CCC 500 5500
3 DDD 600 6600
4 FFF 700 7700
5 GGG 800 8800
These two tables have two common fields: AccountId and Symbol. What I want to do is to get all the data from both tables based on the same accountid and the same symbol. If one table (A) doesn't contain the account or symbol in the other table (B), just fill the column in table (A) with blanks. The result should look like:
AccountID Symbol Fee Price Share Dollar
1 AAA 10 150 100 1100
1 AAA 20 250 --- ---
1 BBB 30 350 200 2200
2 AAA 40 450 300 3300
2 AAA -- --- 400 4400
2 CCC 50 550 500 5500
3 DDD 60 650 600 6600
4 EEE 70 750 --- ----
4 FFF -- --- 700 7700
5 GGG -- --- 800 8800
6 HHH 15 250 --- ----
The columns Fee and Price in Table1 don't have relationship with the columns Share and Dollar in Table2. All I need is to display data from both table based on Account and Symbol. And I need to do it using T-SQL.
Any hint and suggestion would be appreciated.
Thanks in advance!
|
|
|
|
|
SELECT a.AccountID, a.Symbol, a.Fee, a.Price, b.AccountID, b.Symbol,
b.Share, b.Dollar
FROM tableA a
FULL OUTER JOIN tableB b IB a.AccountID = b.AccountID AND a.Symbol = b.Symbol
Regards
KP
|
|
|
|
|
Hi, Thank you for your help!
I have gotten the answer from other site:
SELECT IsNull(T1.AccountID, T2.AccountID) AS AccountID,
ISNull(T1.Symbol, T2.Symbol) AS Symbol,
T1.Fee, T1.Price, T2.Share, T2.Dollar
FROM Table1 AS T1 FULL OUTER JOIN Table2 AS T2
ON T1.AccountID = T2.AccountID AND T1.Symbol = T2.Symbol
ORDER BY AccountID, Symbol
It's basically the same as the post from jose.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
