Adding a table to an existing view

Question

tkotey 0 Newbie Poster

16 Years Ago

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.

mssql

2 Contributors
2 Replies
106 Views
13 Hours Discussion Span
Latest Post 16 Years Ago Latest Post by tkotey

varmadba 12 Junior Poster in Training

16 Years Ago

change the query from where class and see

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 ON c.UserIndex = b.UserIndex  
RIGHT OUTER JOIN dbo.BillsSections AS a ON b.BillIndex = a.BillIndex 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

let me know if this helps

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

tkotey 0 Newbie Poster · Answer 1 · 2008-06-30T20:20:33+00:00

change the query from where class and see

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 ON c.UserIndex = b.UserIndex  
RIGHT OUTER JOIN dbo.BillsSections AS a ON b.BillIndex = a.BillIndex 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

let me know if this helps

I managed to solve this using ORDER BY a.BillIndex, a.BillSectionIndex.