Addning a table to an existing view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Addning a table to an existing view

Author

Topic

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-30 : 02:43:47

I would like to add a field from a table to an existing view without changing the structure of the existing.

Below is the statement for the existing view and the result

SELECT     (SELECT     COUNT(*) AS Expr1
                       FROM          dbo.BillsSections
                       WHERE      (BillIndex = a.BillIndex) AND (Type <> 7) AND (BillSectionIndex > a.BillSectionIndex)) AS IsLast,
                          (SELECT     SUM(Charge) AS Expr1
                            FROM          dbo.BillsSections
                            WHERE      (BillIndex = a.BillIndex)) AS Sums,
                          (SELECT     SUM(Charge) AS Expr1
                            FROM          dbo.BillsSections
                            WHERE      (BillIndex = a.BillIndex) AND (Type = 7)) AS Tax, a.BillIndex, a.BillSectionIndex, a.Type, a.Charge, a.Info1, a.Info2, a.Info3, a.Remark, 
                      b.BillStartDate, b.BillEndDate, b.CreateDate AS BillCreateDate, c.UserID, c.GroupName, c.UserPaymentBalance, d.UserIndex, d.FirstName, 
                      d.MiddleName, d.LastName, d.Company, d.Address1, d.Address2, d.City, d.State, d.Country, d.Zip, d.PhoneHome, d.PhoneWork, d.PhoneFax, d.Email, 
                      d.CreateDate, d.LastModify, d.NewUser, d.LastCharge, d.TotalCharge, d.LastOnlineTime, d.LastTotalTime, d.TotalOnlineTime, d.LastTotalOnlineUpdate, 
                      d.TaxType, d.AdminType, d.CreditCardType, d.CreditCardNumber, d.CreditExpiration, d.CreditHolderName, d.CreditHolderAddress, d.CreditHolderCity, 
                      d.CreditHolderState, d.CreditHolderCountry, d.CreditHolderCityZip, d.CustomInfo1, d.CustomInfo2, d.CustomInfo3, d.CustomInfo4, d.Comments, 
                      d.OnExpireAction, d.NewAccountIndex, d.NewUserBill, d.MBRUserIndex, d.IsMBR, d.AffiliateIndex, d.PaymentMethod, d.PrePayClass, d.EmailFlag1, 
                      d.EmailFlag2, d.EmailFlag3, d.EmailFlag4, d.BankName, d.BankAddress, d.BankAccountName, d.BankAccountNumber, d.TransferCurrencyType, 
                      d.OrangeAccountNumber AS ServiceName, d.WindowsPcNumber, d.MacPcNumber, d.LinuxPcNumber, d.NoOfWifiAvailable, d.NoOfEthernetAvailable, 
                      d.NoOfUSBAvailable, d.AlvarionIDUSerial, d.AlvarionODUSerial, d.RouterBrand, d.RouterSerialNumber, d.BaselineRSSIUp, d.BaselineSNRUp, 
                      d.BaselineModulationUp, d.BaselineRSSIDown, d.BaselineSNRDown, d.BaselineModulationDown, d.BasestationMaster, d.BasestationSectorId, 
                      d.TechnicalPassword, d.Suburb, d.DateOfBirth, d.AuthorisedSignatory, d.CompanyRepresentative, d.RegistrationNumber, d.IDNumber, d.IDType, 
                      e.ServiceName
FROM         dbo.BillsSections AS a LEFT OUTER JOIN
                      dbo.Bills AS b ON a.BillIndex = b.BillIndex LEFT OUTER JOIN
                      dbo.Users AS c ON b.UserIndex = c.UserIndex LEFT OUTER JOIN
                      dbo.UserDetails AS d ON c.UserIndex = d.UserIndex LEFT OUTER JOIN
                      dbo.Services AS e ON a.Info1 = e.ServiceIndex
WHERE     (b.BillType = 1) AND (a.Type <> 7)

Result:

2	288.20000076293945	26.200000762939453	150	1	8	250	0	0	0	For Dates 06/05/2008 - 06/05/2008 (Testraccounttype).	6/5/2008 12:00:00 AM	6/5/2008 11:59:59 PM	6/5/2008 3:02:49 PM	Orch3163370	CPE-CORP	-2612.60669	51	Orange	ISP Orange	Camphill	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	sylvester.chibamo@orange.co.bw	3/20/2008 12:00:00 AM	6/27/2008 10:50:08 AM	False	254.1	3400.8098989999994	0	86399	99005	6/26/2008 11:59:59 PM	0	5	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Livebox	LK07193DP120395	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
1	288.20000076293945	26.200000762939453	150	2	1	1	0	0	0	For Dates 06/04/2008 - 06/04/2008 (Testraccounttype).	6/5/2008 12:00:00 AM	6/5/2008 11:59:59 PM	6/5/2008 3:02:49 PM	Orch3163370	CPE-CORP	-2612.60669	51	Orange	ISP Orange	Camphill	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	sylvester.chibamo@orange.co.bw	3/20/2008 12:00:00 AM	6/27/2008 10:50:08 AM	False	254.1	3400.8098989999994	0	86399	99005	6/26/2008 11:59:59 PM	0	5	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Livebox	LK07193DP120395	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
0	288.20000076293945	26.200000762939453	150	3	10	11	1	0	0	For Dates 06/04/2008 - 06/04/2008 (Testraccounttype) - Mail box package of 4 Email for 12 Pula.	6/5/2008 12:00:00 AM	6/5/2008 11:59:59 PM	6/5/2008 3:02:49 PM	Orch3163370	CPE-CORP	-2612.60669	51	Orange	ISP Orange	Camphill	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	sylvester.chibamo@orange.co.bw	3/20/2008 12:00:00 AM	6/27/2008 10:50:08 AM	False	254.1	3400.8098989999994	0	86399	99005	6/26/2008 11:59:59 PM	0	5	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Livebox	LK07193DP120395	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	MBox Package
2	288.20000076293945	26.200000762939453	151	1	8	250	0	0	0	For Dates 06/05/2008 - 06/05/2008 (Testraccounttype).	6/5/2008 12:00:00 AM	6/5/2008 11:59:59 PM	6/5/2008 3:02:49 PM	Orch3163372	CPE-STAFF	-204122.1	53	Orange Test Livebox	Sly	NULL	Orange Botswana	P/Bag Bo64	Po Box 50349	Gaborone	NULL	NULL	NULL	3163370	3163370	3163372	sylvester.chibamo@orange.co.bw	4/15/2008 12:00:00 AM	6/16/2008 10:10:18 AM	False	35933.320627	369863.662552	87393	86399	929535	6/26/2008 11:59:59 PM	0	0	0	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	350	NULL	NULL	NULL	This is for test purposes.	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	N/aBarclays	Po Box 50349
Gaborone	Current	n/a	BWP	3232323	5	0	0	3	2	2	SO733005199	will update	Livebox	LK07193DP120297	-71	25	QAM64 3/4	-60	30	QAM 64 3/4	Poso House	1	user21	Block 6	02/09/1978	n/a	Sylvester 	n/a	681519109	Botswana	NULL
1	288.20000076293945	26.200000762939453	151	2	1	1	0	0	0	For Dates 06/04/2008 - 06/04/2008 (Testraccounttype).	6/5/2008 12:00:00 AM	6/5/2008 11:59:59 PM	6/5/2008 3:02:49 PM	Orch3163372	CPE-STAFF	-204122.1	53	Orange Test Livebox	Sly	NULL	Orange Botswana	P/Bag Bo64	Po Box 50349	Gaborone	NULL	NULL	NULL	3163370	3163370	3163372	sylvester.chibamo@orange.co.bw	4/15/2008 12:00:00 AM	6/16/2008 10:10:18 AM	False	35933.320627	369863.662552	87393	86399	929535	6/26/2008 11:59:59 PM	0	0	0	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	350	NULL	NULL	NULL	This is for test purposes.	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	N/aBarclays	Po Box 50349
Gaborone	Current	n/a	BWP	3232323	5	0	0	3	2	2	SO733005199	will update	Livebox	LK07193DP120297	-71	25	QAM64 3/4	-60	30	QAM 64 3/4	Poso House	1	user21	Block 6	02/09/1978	n/a	Sylvester 	n/a	681519109	Botswana	NULL
0	288.20000076293945	26.200000762939453	151	3	10	11	1	0	0	For Dates 06/04/2008 - 06/04/2008 (Testraccounttype) - Mail box package of 4 Email for 12 Pula.	6/5/2008 12:00:00 AM	6/5/2008 11:59:59 PM	6/5/2008 3:02:49 PM	Orch3163372	CPE-STAFF	-204122.1	53	Orange Test Livebox	Sly	NULL	Orange Botswana	P/Bag Bo64	Po Box 50349	Gaborone	NULL	NULL	NULL	3163370	3163370	3163372	sylvester.chibamo@orange.co.bw	4/15/2008 12:00:00 AM	6/16/2008 10:10:18 AM	False	35933.320627	369863.662552	87393	86399	929535	6/26/2008 11:59:59 PM	0	0	0	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	350	NULL	NULL	NULL	This is for test purposes.	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	N/aBarclays	Po Box 50349
Gaborone	Current	n/a	BWP	3232323	5	0	0	3	2	2	SO733005199	will update	Livebox	LK07193DP120297	-71	25	QAM64 3/4	-60	30	QAM 64 3/4	Poso House	1	user21	Block 6	02/09/1978	n/a	Sylvester 	n/a	681519109	Botswana	MBox Package
1	0.59000003337860107	NULL	157	1	1	-1	0	0	0	For Dates 06/08/2008 - 06/08/2008 (Testraccounttype).	6/8/2008 12:00:00 AM	6/8/2008 11:55:33 AM	6/8/2008 11:55:33 AM	Orch3163370	CPE-CORP	-2612.60669	51	Orange	ISP Orange	Camphill	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	sylvester.chibamo@orange.co.bw	3/20/2008 12:00:00 AM	6/27/2008 10:50:08 AM	False	254.1	3400.8098989999994	0	86399	99005	6/26/2008 11:59:59 PM	0	5	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Livebox	LK07193DP120395	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
0	0.59000003337860107	NULL	157	2	100	1.59	0	162	0	For Dates 06/08/2008 - 06/08/2008 (Testraccounttype).	6/8/2008 12:00:00 AM	6/8/2008 11:55:33 AM	6/8/2008 11:55:33 AM	Orch3163370	CPE-CORP	-2612.60669	51	Orange	ISP Orange	Camphill	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	sylvester.chibamo@orange.co.bw	3/20/2008 12:00:00 AM	6/27/2008 10:50:08 AM	False	254.1	3400.8098989999994	0	86399	99005	6/26/2008 11:59:59 PM	0	5	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Livebox	LK07193DP120395	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
1	13.200000047683716	1.2000000476837158	158	1	1	1	0	0	0	For Dates 06/10/2008 - 06/10/2008 (Testraccounttype).	6/10/2008 12:00:00 AM	6/10/2008 12:00:00 AM	6/10/2008 8:59:23 AM	Orch3163372	CPE-STAFF	-204122.1	53	Orange Test Livebox	Sly	NULL	Orange Botswana	P/Bag Bo64	Po Box 50349	Gaborone	NULL	NULL	NULL	3163370	3163370	3163372	sylvester.chibamo@orange.co.bw	4/15/2008 12:00:00 AM	6/16/2008 10:10:18 AM	False	35933.320627	369863.662552	87393	86399	929535	6/26/2008 11:59:59 PM	0	0	0	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	350	NULL	NULL	NULL	This is for test purposes.	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	N/aBarclays	Po Box 50349
Gaborone	Current	n/a	BWP	3232323	5	0	0	3	2	2	SO733005199	will update	Livebox	LK07193DP120297	-71	25	QAM64 3/4	-60	30	QAM 64 3/4	Poso House	1	user21	Block 6	02/09/1978	n/a	Sylvester 	n/a	681519109	Botswana	NULL
0	13.200000047683716	1.2000000476837158	158	2	10	11	1	0	0	For Dates 06/10/2008 - 06/10/2008 (Testraccounttype) - Mail box package of 4 Email for 12 Pula.	6/10/2008 12:00:00 AM	6/10/2008 12:00:00 AM	6/10/2008 8:59:23 AM	Orch3163372	CPE-STAFF	-204122.1	53	Orange Test Livebox	Sly	NULL	Orange Botswana	P/Bag Bo64	Po Box 50349	Gaborone	NULL	NULL	NULL	3163370	3163370	3163372	sylvester.chibamo@orange.co.bw	4/15/2008 12:00:00 AM	6/16/2008 10:10:18 AM	False	35933.320627	369863.662552	87393	86399	929535	6/26/2008 11:59:59 PM	0	0	0	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	350	NULL	NULL	NULL	This is for test purposes.	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	N/aBarclays	Po Box 50349
Gaborone	Current	n/a	BWP	3232323	5	0	0	3	2	2	SO733005199	will update	Livebox	LK07193DP120297	-71	25	QAM64 3/4	-60	30	QAM 64 3/4	Poso House	1	user21	Block 6	02/09/1978	n/a	Sylvester 	n/a	681519109	Botswana	MBox Package
2	12.680000305175781	NULL	159	1	10	1.06	1	0	0	For Dates 06/08/2008 - 06/11/2008 (Corporate) - Mail box package of 4 Email for 12 Pula.	6/8/2008 11:55:33 AM	6/11/2008 4:54:03 PM	6/11/2008 4:54:03 PM	Orch3163370	CPE-CORP	-2612.60669	51	Orange	ISP Orange	Camphill	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	sylvester.chibamo@orange.co.bw	3/20/2008 12:00:00 AM	6/27/2008 10:50:08 AM	False	254.1	3400.8098989999994	0	86399	99005	6/26/2008 11:59:59 PM	0	5	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Livebox	LK07193DP120395	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	MBox Package
1	12.680000305175781	NULL	159	2	10	9.68	8	0	0	For Dates 06/08/2008 - 06/11/2008 (Corporate) - DNS Hosting Service.	6/8/2008 11:55:33 AM	6/11/2008 4:54:03 PM	6/11/2008 4:54:03 PM	Orch3163370	CPE-CORP	-2612.60669	51	Orange	ISP Orange	Camphill	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	sylvester.chibamo@orange.co.bw	3/20/2008 12:00:00 AM	6/27/2008 10:50:08 AM	False	254.1	3400.8098989999994	0	86399	99005	6/26/2008 11:59:59 PM	0	5	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Livebox	LK07193DP120395	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	DNSHosting
0	12.680000305175781	NULL	159	3	10	1.94	10	0	0	For Dates 06/08/2008 - 06/11/2008 (Corporate) - Mail Relay hosting service..	6/8/2008 11:55:33 AM	6/11/2008 4:54:03 PM	6/11/2008 4:54:03 PM	Orch3163370	CPE-CORP	-2612.60669	51	Orange	ISP Orange	Camphill	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	sylvester.chibamo@orange.co.bw	3/20/2008 12:00:00 AM	6/27/2008 10:50:08 AM	False	254.1	3400.8098989999994	0	86399	99005	6/26/2008 11:59:59 PM	0	5	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Livebox	LK07193DP120395	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Mail Relay Hosting
4	170.44000248610973	NULL	160	1	1	0.17	0	0	0	For Dates 06/10/2008 - 06/12/2008 (Testraccounttype).	6/10/2008 12:00:00 AM	6/12/2008 11:42:46 AM	6/12/2008 11:42:46 AM	Orch3163372	CPE-STAFF	-204122.1	53	Orange Test Livebox	Sly	NULL	Orange Botswana	P/Bag Bo64	Po Box 50349	Gaborone	NULL	NULL	NULL	3163370	3163370	3163372	sylvester.chibamo@orange.co.bw	4/15/2008 12:00:00 AM	6/16/2008 10:10:18 AM	False	35933.320627	369863.662552	87393	86399	929535	6/26/2008 11:59:59 PM	0	0	0	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	350	NULL	NULL	NULL	This is for test purposes.	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	N/aBarclays	Po Box 50349
Gaborone	Current	n/a	BWP	3232323	5	0	0	3	2	2	SO733005199	will update	Livebox	LK07193DP120297	-71	25	QAM64 3/4	-60	30	QAM 64 3/4	Poso House	1	user21	Block 6	02/09/1978	n/a	Sylvester 	n/a	681519109	Botswana	NULL
3	170.44000248610973	NULL	160	2	10	1.83	1	0	0	For Dates 06/10/2008 - 06/12/2008 (Testraccounttype) - Mail box package of 4 Email for 12 Pula.	6/10/2008 12:00:00 AM	6/12/2008 11:42:46 AM	6/12/2008 11:42:46 AM	Orch3163372	CPE-STAFF	-204122.1	53	Orange Test Livebox	Sly	NULL	Orange Botswana	P/Bag Bo64	Po Box 50349	Gaborone	NULL	NULL	NULL	3163370	3163370	3163372	sylvester.chibamo@orange.co.bw	4/15/2008 12:00:00 AM	6/16/2008 10:10:18 AM	False	35933.320627	369863.662552	87393	86399	929535	6/26/2008 11:59:59 PM	0	0	0	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	350	NULL	NULL	NULL	This is for test purposes.	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	N/aBarclays	Po Box 50349
Gaborone	Current	n/a	BWP	3232323	5	0	0	3	2	2	SO733005199	will update	Livebox	LK07193DP120297	-71	25	QAM64 3/4	-60	30	QAM 64 3/4	Poso House	1	user21	Block 6	02/09/1978	n/a	Sylvester 	n/a	681519109	Botswana	MBox Package
2	170.44000248610973	NULL	160	3	10	16.67	8	0	0	For Dates 06/10/2008 - 06/12/2008 (Testraccounttype) - DNS Hosting Service.	6/10/2008 12:00:00 AM	6/12/2008 11:42:46 AM	6/12/2008 11:42:46 AM	Orch3163372	CPE-STAFF	-204122.1	53	Orange Test Livebox	Sly	NULL	Orange Botswana	P/Bag Bo64	Po Box 50349	Gaborone	NULL	NULL	NULL	3163370	3163370	3163372	sylvester.chibamo@orange.co.bw	4/15/2008 12:00:00 AM	6/16/2008 10:10:18 AM	False	35933.320627	369863.662552	87393	86399	929535	6/26/2008 11:59:59 PM	0	0	0	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	350	NULL	NULL	NULL	This is for test purposes.	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	N/aBarclays	Po Box 50349
Gaborone	Current	n/a	BWP	3232323	5	0	0	3	2	2	SO733005199	will update	Livebox	LK07193DP120297	-71	25	QAM64 3/4	-60	30	QAM 64 3/4	Poso House	1	user21	Block 6	02/09/1978	n/a	Sylvester 	n/a	681519109	Botswana	DNSHosting
1	170.44000248610973	NULL	160	4	10	3.33	10	0	0	For Dates 06/10/2008 - 06/12/2008 (Testraccounttype) - Mail Relay hosting service..	6/10/2008 12:00:00 AM	6/12/2008 11:42:46 AM	6/12/2008 11:42:46 AM	Orch3163372	CPE-STAFF	-204122.1	53	Orange Test Livebox	Sly	NULL	Orange Botswana	P/Bag Bo64	Po Box 50349	Gaborone	NULL	NULL	NULL	3163370	3163370	3163372	sylvester.chibamo@orange.co.bw	4/15/2008 12:00:00 AM	6/16/2008 10:10:18 AM	False	35933.320627	369863.662552	87393	86399	929535	6/26/2008 11:59:59 PM	0	0	0	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	350	NULL	NULL	NULL	This is for test purposes.	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	N/aBarclays	Po Box 50349
Gaborone	Current	n/a	BWP	3232323	5	0	0	3	2	2	SO733005199	will update	Livebox	LK07193DP120297	-71	25	QAM64 3/4	-60	30	QAM 64 3/4	Poso House	1	user21	Block 6	02/09/1978	n/a	Sylvester 	n/a	681519109	Botswana	Mail Relay Hosting
0	170.44000248610973	NULL	160	5	100	148.44	0	15200	0	For Dates 06/10/2008 - 06/12/2008 (Testraccounttype).	6/10/2008 12:00:00 AM	6/12/2008 11:42:46 AM	6/12/2008 11:42:46 AM	Orch3163372	CPE-STAFF	-204122.1	53	Orange Test Livebox	Sly	NULL	Orange Botswana	P/Bag Bo64	Po Box 50349	Gaborone	NULL	NULL	NULL	3163370	3163370	3163372	sylvester.chibamo@orange.co.bw	4/15/2008 12:00:00 AM	6/16/2008 10:10:18 AM	False	35933.320627	369863.662552	87393	86399	929535	6/26/2008 11:59:59 PM	0	0	0	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	350	NULL	NULL	NULL	This is for test purposes.	NULL	NULL	False	0	False	NULL	0	NULL	False	False	False	False	N/aBarclays	Po Box 50349
Gaborone	Current	n/a	BWP	3232323	5	0	0	3	2	2	SO733005199	will update	Livebox	LK07193DP120297	-71	25	QAM64 3/4	-60	30	QAM 64 3/4	Poso House	1	user21	Block 6	02/09/1978	n/a	Sylvester 	n/a	681519109	Botswana	NULL

I would like to add a new field called Balance from another table to the existing view but without changing the structure of the view.

This what I did before but it is not giving me the desirable result

SELECT DISTINCT TOP (100) PERCENT
                          (SELECT     COUNT(*) AS Expr1
                            FROM          dbo.BillsSections
                            WHERE      (BillIndex = a.BillIndex) AND (Type <> 7) AND (BillSectionIndex > a.BillSectionIndex)) AS IsLast,
                          (SELECT     SUM(Charge) AS Expr1
                            FROM          dbo.BillsSections AS BillsSections_2
                            WHERE      (BillIndex = a.BillIndex)) AS Sums,
                          (SELECT     SUM(Charge) AS Expr1
                            FROM          dbo.BillsSections AS BillsSections_1
                            WHERE      (BillIndex = a.BillIndex) AND (Type = 7)) AS Tax, a.BillIndex, a.BillSectionIndex, a.Type, a.Charge, a.Info1, a.Info2, a.Info3, a.Remark, 
                      b.BillStartDate, b.BillEndDate, b.CreateDate AS BillCreateDate, c.UserID, c.GroupName, c.UserPaymentBalance, d.UserIndex, d.FirstName, 
                      d.MiddleName, d.LastName, d.Company, d.Address1, d.Address2, d.City, d.State, d.Country, d.Zip, d.PhoneHome, d.PhoneWork, d.PhoneFax, d.Email, 
                      d.CreateDate, d.LastModify, d.NewUser, d.LastCharge, d.TotalCharge, d.CustomInfo4, d.Comments, e.ServiceName, dbo.AccountTypes.AccountName, 
                      dbo.BillingTransactions.Balance, dbo.BillingTransactions.CreateDate AS TransDate
FROM         dbo.AccountTypes INNER JOIN
                      dbo.Users AS c ON dbo.AccountTypes.AccountIndex = c.AccountIndex INNER JOIN
                      dbo.BillingTransactions ON c.UserIndex = dbo.BillingTransactions.UserIndex RIGHT OUTER JOIN
                      dbo.Bills AS b RIGHT OUTER JOIN
                      dbo.BillsSections AS a ON b.BillIndex = a.BillIndex ON c.UserIndex = b.UserIndex AND 
                      dbo.BillingTransactions.Reference = a.BillIndex LEFT OUTER JOIN
                      dbo.UserDetails AS d ON c.UserIndex = d.UserIndex LEFT OUTER JOIN
                      dbo.Services AS e ON a.Info1 = e.ServiceIndex
WHERE     (b.BillType = 1) AND (a.Type <> 7)
ORDER BY a.BillIndex DESC

I need help. Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 03:06:39

When you add the new column to view, obviously its structure will change.Didint understand what you're asking for?

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-30 : 03:17:44

quote:
Originally posted by visakh16

When you add the new column to view, obviously its structure will change.Didint understand what you're asking for?

I am using Mail Merge to produce an invoice by getting data from a view. When I add a field from another table into the existing view view and I run my mail merge, I get duplicated data and sometimes I don't get all the data. This only happens when I add a new field to the existing view. I need to add the new field but I guess I have to accept the fact that the structure changes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources