Problems with Views - SQL Server Forums

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

General SQL Server Forums

Database Design and Application Architecture

Problems with Views

Author

Topic

khule
Starting Member

2 Posts

Posted - 2008-03-12 : 07:03:01

I am writing a join query with views in SQL server 2k. When I get the output it advances everything by 1 column forward.

e.g If my column names are ID, Name, Year, Month

In the output the ID column contains the Month, the Name column contains data from Id, the Year column contains data from Name.

Plz help

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 07:06:03

Post your query and View definition here.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khule
Starting Member

2 Posts

Posted - 2008-03-14 : 01:01:05

Hi Am posting the select queries below, the current problem at hand is that only the 1st two rows data is getting interchanged, have checked the aliases and they are fine.

the 1st query runs perfectly, the 2nd one gives output (pasted below) it can be seen that year of invoice contains null and month of invoice contains the year data

1st View Starts
Select
Pm.ClientMasterId,Cm.Client_Name MasterClientName,PM.User_Id, PM.User_Name, Pm.Branch, PM.Branchid,Im.Cancelleddate,Im.CancelInvoice,Im.InvoiceNo,
IM.Client_ID,Pm.Client_Name,Program,PRoposal_Sold_Date,Location,Im.Personid,IM.ClientName,IM.Invoiceid,Im.InvoiceDate,Im.Incomedate, Im.Discount, Im.ServiceT, Im.EduCess, Im.Shec,
Im.FinancialYear,Im.TypeId, FY.CurrentYear, Fy.YearFormat,
Month(IncomeDate) MonthofInvoice, Year(IncomeDate) YearofInvoice, PT.TypeName,
Sum(Id.amount) SubTotal,Sum(Id.Amount)* Im.Discount/100 DiscountAmount,
Sum(Id.Amount) - Sum(Id.Amount)* Im.Discount/100 TotalAmount,
((Sum(Id.Amount) - Sum(Id.Amount)* Im.Discount/100) * IM.ServiceT)/100 ServiceTaxAmount,
(((Sum(Id.Amount) - Sum(Id.Amount)* Im.Discount/100) * IM.ServiceT)/100) * IM.EduCess /100 EduCessAmount,
(Sum(Id.Amount) - Sum(Id.Amount)* Im.Discount/100) + (((Sum(Id.Amount) - Sum(Id.Amount)* Im.Discount/100) * IM.ServiceT)/100) + ((((Sum(Id.Amount) - Sum(Id.Amount)* Im.Discount/100) * IM.ServiceT)/100) * IM.EduCess /100) NetAmount
from ClientMaster Cm, InvoiceMaster IM, InvoiceDetails Id,SetFinancialYear FY, DcProgtype PT, vw_AllBusiness PM
Where IM.InvoiceId = Id.InvoiceId
and IM.Client_Id = Pm.Client_id
and CM.Client_id = Pm.Clientmasterid
and IM.FinancialYear = FY.YearId
and IM.TypeId = PT.TypeId
Group by Pm.ClientMasterId,CM.Client_Name,IM.Invoiceid,Im.InvoiceNo,Im.Incomedate,IM.Client_ID,Im.Personid,Pm.Client_Name,Program,PRoposal_Sold_Date,Location, Im.ServiceT, Im.EduCess, Im.Shec, IM.Discount,
Im.FinancialYear,Im.TypeId, FY.CurrentYear, Fy.YearFormat,Im.InvoiceDate,IM.ClientName,
Month(IncomeDate) , Year(IncomeDate) , PT.TypeName , PM.User_Id, Pm.User_name,Pm.Branch,
Pm.Branchid,Im.Cancelleddate,Im.CancelInvoice

2nd View starts

Select * from vw_corporatebusiness order by YearofInvoice ,MonthofInvoice

SELECT YearofInvoice,MonthofInvoice ,
Sum(TotalAmount) AS [Amount],
Sum(TotalAmount)*SharePercentage/100 SharedAmount,
SharePercentage, User_Name, TypeName, User_Id ,Branch,FinancialYear, TypeId,Branchid
FROM vw_invoices vwi, ShareBusiness SB
Where vwi.Client_Id = SB.Client_Id
GROUP BY YearofInvoice ,MonthofInvoice ,
User_Name,Branch,TypeName,Sb.SharePercentage,FinancialYear,User_id,TypeID,Branchid

2nd View Output

YearofInvoice MonthofInvoice Amount SharedAmount SharePercentage User_Name TypeName User_Id Branch FinancialYear TypeId Branchid
------------- -------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- --------------------- -------------------------------------------------- ---------
NULL 2004-05 0.0 0.0 100.000000000000 Vandana Jain NULL 101 Delhi 1 1 3
NULL 2007-08 0.0 0.0 100.000000000000 Ashish Misra NULL 54 Delhi 1 6 3
NULL 2007-08 0.0 0.0 100.000000000000 Bidyut Acharya NULL 139 Kolkatta 1 6 5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources