By using this query I can get the result as I want but now there is another problematic while condition.
通过使用这个查询,我可以得到我想要的结果,但现在有另一个有问题的条件。
SELECT distinct
concat([Segment_0], '-' , [Segment_1], '-',[Segment_2]) As 'AcctCode',
T1.[AcctName],
T2.[Name],
concat(T3.[Code],'-',T3.[Name]),
T0.[DebLTotal] AS 'ANNUAL BUDGET KSH',
T0.[DebRLTotal] AS 'UTILISED BUDGET',
T3.[Name] ,
T3.[Code],
(T0.[DebLTotal] - T0.[DebRLTotal]) AS 'BALANCE BUDGET',
(SELECT concat(T3.Code , '-', T3.[Name]) WHERE T3.SegmentId = '1') AS 'Project',
(SELECT concat(T3.Code , '-', T3.[Name]) WHERE T3.SegmentId = '2') AS 'Distt'
FROM
OBGT T0
INNER JOIN
OACT T1 ON T0.[AcctCode] = T1.[AcctCode]
INNER JOIN
OBGS T2 ON T0.[Instance] = T2.[AbsId]
INNER JOIN
OASC T3 ON (T3.SegmentId = '1' AND T3.Code = [Segment_1] )
OR (T3.SegmentId = '2' AND T3.Code = [Segment_2])
If I put condition in the where clause like Project = '100' and Distt = '001', it gives me no data. It should be there.
如果我把条件放在像Project ='100'和Distt ='001'这样的where子句中,它就不会给我任何数据。应该在那里。
concat([Segment_0], '-' , [Segment_1], '-', [Segment_2])
should also be changed according to the condition as we are joining SegmentId to those columns.
也应该根据我们将SegmentId加入这些列的条件进行更改。
Please help
USE [RCTP_NGO]
GO
/****** Object: Table [dbo].[OACT] Script Date: 06/Nov/2015 4:28:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OACT](
[AcctCode] [nvarchar](15) NOT NULL,
[AcctName] [nvarchar](100) NULL,
[CurrTotal] [numeric](19, 6) NULL,
[EndTotal] [numeric](19, 6) NULL,
[Finanse] [char](1) NULL,
[Groups] [nvarchar](8) NULL,
[Budget] [char](1) NULL,
[Frozen] [char](1) NULL,
[Free_2] [char](1) NULL,
[Postable] [char](1) NULL,
[Fixed] [char](1) NULL,
[Levels] [smallint] NULL,
[ExportCode] [nvarchar](10) NULL,
[GrpLine] [int] NULL,
[FatherNum] [nvarchar](15) NULL,
[AccntntCod] [nvarchar](15) NULL,
[CashBox] [char](1) NULL,
[GroupMask] [smallint] NULL,
[RateTrans] [char](1) NULL,
[TaxIncome] [char](1) NULL,
[ExmIncome] [char](1) NULL,
[ExtrMatch] [int] NULL,
[IntrMatch] [int] NULL,
[ActType] [char](1) NULL,
[Transfered] [char](1) NULL,
[BlncTrnsfr] [char](1) NULL,
[OverType] [char](1) NULL,
[OverCode] [nvarchar](8) NULL,
[SysMatch] [int] NULL,
[PrevYear] [char](1) NULL,
[ActCurr] [nvarchar](3) NULL,
[RateDifAct] [nvarchar](15) NULL,
[SysTotal] [numeric](19, 6) NULL,
[FcTotal] [numeric](19, 6) NULL,
[Protected] [char](1) NULL,
[RealAcct] [char](1) NULL,
[Advance] [char](1) NULL,
[CreateDate] [datetime] NULL,
[UpdateDate] [datetime] NULL,
[FrgnName] [nvarchar](100) NULL,
[Details] [nvarchar](254) NULL,
[ExtraSum] [numeric](19, 6) NULL,
[Project] [nvarchar](20) NULL,
[RevalMatch] [char](1) NULL,
[DataSource] [char](1) NULL,
[LocMth] [char](1) NULL,
[MTHCounter] [int] NULL,
[BNKCounter] [int] NULL,
[UserSign] [smallint] NULL,
[LocManTran] [char](1) NULL,
[LogInstanc] [int] NULL,
[ObjType] [nvarchar](20) NULL,
[ValidFor] [char](1) NULL,
[ValidFrom] [datetime] NULL,
[ValidTo] [datetime] NULL,
[ValidComm] [nvarchar](30) NULL,
[FrozenFor] [char](1) NULL,
[FrozenFrom] [datetime] NULL,
[FrozenTo] [datetime] NULL,
[FrozenComm] [nvarchar](30) NULL,
[Counter] [int] NULL,
[Segment_0] [nvarchar](20) NULL,
[Segment_1] [nvarchar](20) NULL,
[Segment_2] [nvarchar](20) NULL,
[Segment_3] [nvarchar](20) NULL,
[Segment_4] [nvarchar](20) NULL,
[Segment_5] [nvarchar](20) NULL,
[Segment_6] [nvarchar](20) NULL,
[Segment_7] [nvarchar](20) NULL,
[Segment_8] [nvarchar](20) NULL,
[Segment_9] [nvarchar](20) NULL,
[FormatCode] [nvarchar](210) NULL,
[CfwRlvnt] [char](1) NULL,
[ExchRate] [char](1) NULL,
[RevalAcct] [nvarchar](15) NULL,
[LastRevBal] [numeric](19, 6) NULL,
[LastRevDat] [datetime] NULL,
[DfltVat] [nvarchar](8) NULL,
[VatChange] [char](1) NULL,
[Category] [int] NULL,
[TransCode] [nvarchar](4) NULL,
[OverCode5] [nvarchar](8) NULL,
[OverCode2] [nvarchar](8) NULL,
[OverCode3] [nvarchar](8) NULL,
[OverCode4] [nvarchar](8) NULL,
[DfltTax] [nvarchar](8) NULL,
[TaxPostAcc] [char](1) NULL,
[AcctStrLe] [nvarchar](2) NULL,
[MeaUnit] [nvarchar](10) NULL,
[BalDirect] [nvarchar](4) NULL,
[UserSign2] [smallint] NULL,
[PlngLevel] [nvarchar](2) NULL,
[MultiLink] [char](1) NULL,
[PrjRelvnt] [char](1) NULL,
[Dim1Relvnt] [char](1) NULL,
[Dim2Relvnt] [char](1) NULL,
[Dim3Relvnt] [char](1) NULL,
[Dim4Relvnt] [char](1) NULL,
[Dim5Relvnt] [char](1) NULL,
[AccrualTyp] [char](1) NULL,
[DatevAcct] [int] NULL,
[DatevAutoA] [char](1) NULL,
[DatevFirst] [char](1) NULL,
[SnapShotId] [int] NULL,
[PCN874Rpt] [char](1) NULL,
[SCAdjust] [char](1) NULL,
[BPLId] [int] NULL,
[BPLName] [nvarchar](100) NULL,
[SubLedgerN] [nvarchar](60) NULL,
[VATRegNum] [nvarchar](32) NULL,
[ActId] [nvarchar](210) NOT NULL,
[ClosingAcc] [nvarchar](15) NULL,
[PurpCode] [nvarchar](2) NULL,
[RefCode] [nvarchar](30) NULL,
[U_COR_FIN_Cls] [nvarchar](32) NULL,
CONSTRAINT [OACT_PRIMARY] PRIMARY KEY CLUSTERED
(
[AcctCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[OASC] Script Date: 06/Nov/2015 4:28:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OASC](
[SegmentId] [smallint] NOT NULL,
[Code] [nvarchar](20) NOT NULL,
[Name] [nvarchar](100) NULL,
[ShortName] [nvarchar](10) NULL,
[UserSign] [smallint] NULL,
CONSTRAINT [OASC_PRIMARY] PRIMARY KEY CLUSTERED
(
[SegmentId] ASC,
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[OBGS] Script Date: 06/Nov/2015 4:28:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OBGS](
[AbsId] [int] NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[BaseId] [int] NULL,
[InitRate] [numeric](19, 6) NULL,
[Locked] [char](1) NULL,
[FinancYear] [datetime] NOT NULL,
[IsMain] [char](1) NULL,
[DataSource] [char](1) NULL,
[RoundSys] [smallint] NULL,
[UserSign] [smallint] NULL,
[OcrCode] [nvarchar](8) NULL,
[OcrCode2] [nvarchar](8) NULL,
[OcrCode3] [nvarchar](8) NULL,
[OcrCode4] [nvarchar](8) NULL,
[OcrCode5] [nvarchar](8) NULL,
[PrjCode] [nvarchar](20) NULL,
[U_COR_OcrCode] [nvarchar](200) NULL,
[U_COR_PrjCode] [nvarchar](200) NULL,
CONSTRAINT [OBGS_PRIMARY] PRIMARY KEY CLUSTERED
(
[AbsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[OBGT] Script Date: 06/Nov/2015 4:28:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OBGT](
[AbsId] [int] NOT NULL,
[AcctCode] [nvarchar](15) NOT NULL,
[BgdCode] [int] NULL,
[FatherCode] [nvarchar](15) NULL,
[FthrPrcnt] [numeric](19, 6) NULL,
[DebLTotal] [numeric](19, 6) NULL,
[CredLTotal] [numeric](19, 6) NULL,
[DebSTotal] [numeric](19, 6) NULL,
[CredSTotal] [numeric](19, 6) NULL,
[DebRLTotal] [numeric](19, 6) NULL,
[CrdRLTotal] [numeric](19, 6) NULL,
[DebRSTotal] [numeric](19, 6) NULL,
[CrdRSTotal] [numeric](19, 6) NULL,
[FtrIDRLSum] [numeric](19, 6) NULL,
[FtrIDRSSum] [numeric](19, 6) NULL,
[FtrICRLSum] [numeric](19, 6) NULL,
[FtrICRSSum] [numeric](19, 6) NULL,
[FtrODRLSum] [numeric](19, 6) NULL,
[FtrOCRLSum] [numeric](19, 6) NULL,
[FtrODRSSum] [numeric](19, 6) NULL,
[FtrOCRSSum] [numeric](19, 6) NULL,
[FinancYear] [datetime] NOT NULL,
[Instance] [int] NOT NULL,
[UserSign] [smallint] NULL,
[SCNCounter] [smallint] NULL,
CONSTRAINT [OBGT_PRIMARY] PRIMARY KEY CLUSTERED
(
[AbsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Finanse] DEFAULT ('N') FOR [Finanse]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Budget] DEFAULT ('N') FOR [Budget]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Frozen] DEFAULT ('N') FOR [Frozen]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Postable] DEFAULT ('Y') FOR [Postable]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Levels] DEFAULT ((2)) FOR [Levels]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_CashBox] DEFAULT ('N') FOR [CashBox]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_GroupMask] DEFAULT ((1)) FOR [GroupMask]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_RateTrans] DEFAULT ('Y') FOR [RateTrans]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_TaxIncome] DEFAULT ('N') FOR [TaxIncome]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_ExmIncome] DEFAULT ('N') FOR [ExmIncome]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_ActType] DEFAULT ('N') FOR [ActType]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Transfered] DEFAULT ('N') FOR [Transfered]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_BlncTrnsfr] DEFAULT ('N') FOR [BlncTrnsfr]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_OverType] DEFAULT ('N') FOR [OverType]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_SysMatch] DEFAULT ((-1)) FOR [SysMatch]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_PrevYear] DEFAULT ('N') FOR [PrevYear]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Protected] DEFAULT ('N') FOR [Protected]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_RealAcct] DEFAULT ('N') FOR [RealAcct]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Advance] DEFAULT ('Y') FOR [Advance]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_RevalMatch] DEFAULT ('N') FOR [RevalMatch]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_DataSource] DEFAULT ('N') FOR [DataSource]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_LocMth] DEFAULT ('Y') FOR [LocMth]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_LocManTran] DEFAULT ('N') FOR [LocManTran]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_LogInstanc] DEFAULT ((0)) FOR [LogInstanc]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_ObjType] DEFAULT ('1') FOR [ObjType]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_ValidFor] DEFAULT ('N') FOR [ValidFor]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_FrozenFor] DEFAULT ('N') FOR [FrozenFor]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Counter] DEFAULT ((0)) FOR [Counter]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_CfwRlvnt] DEFAULT ('N') FOR [CfwRlvnt]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_ExchRate] DEFAULT ('Y') FOR [ExchRate]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_VatChange] DEFAULT ('Y') FOR [VatChange]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_TaxPostAcc] DEFAULT ('N') FOR [TaxPostAcc]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_BalDirect] DEFAULT ('0') FOR [BalDirect]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_MultiLink] DEFAULT ('N') FOR [MultiLink]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_PrjRelvnt] DEFAULT ('N') FOR [PrjRelvnt]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Dim1Relvnt] DEFAULT ('N') FOR [Dim1Relvnt]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Dim2Relvnt] DEFAULT ('N') FOR [Dim2Relvnt]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Dim3Relvnt] DEFAULT ('N') FOR [Dim3Relvnt]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Dim4Relvnt] DEFAULT ('N') FOR [Dim4Relvnt]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_Dim5Relvnt] DEFAULT ('N') FOR [Dim5Relvnt]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_AccrualTyp] DEFAULT ('N') FOR [AccrualTyp]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_DatevAutoA] DEFAULT ('N') FOR [DatevAutoA]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_DatevFirst] DEFAULT ('Y') FOR [DatevFirst]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_SnapShotId] DEFAULT ((0)) FOR [SnapShotId]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_PCN874Rpt] DEFAULT ('N') FOR [PCN874Rpt]
GO
ALTER TABLE [dbo].[OACT] ADD CONSTRAINT [DF_OACT_SCAdjust] DEFAULT ('N') FOR [SCAdjust]
GO
ALTER TABLE [dbo].[OBGS] ADD CONSTRAINT [DF_OBGS_Locked] DEFAULT ('N') FOR [Locked]
GO
ALTER TABLE [dbo].[OBGS] ADD CONSTRAINT [DF_OBGS_IsMain] DEFAULT ('N') FOR [IsMain]
GO
ALTER TABLE [dbo].[OBGS] ADD CONSTRAINT [DF_OBGS_DataSource] DEFAULT ('N') FOR [DataSource]
GO
ALTER TABLE [dbo].[OBGS] ADD CONSTRAINT [DF_OBGS_RoundSys] DEFAULT ((0)) FOR [RoundSys]
GO
ALTER TABLE [dbo].[OBGT] ADD CONSTRAINT [DF_OBGT_Instance] DEFAULT ((1)) FOR [Instance]
GO
1 个解决方案
#1
1
You have to make it in 2 parts
你必须分为两部分
First, is to create a new view
首先,是创建一个新视图
CREATE VIEW [dbo].[View1]
AS
SELECT DISTINCT
T1.AcctName, T2.Name, T0.DebLTotal AS 'ANNUAL BUDGET KSH', T0.DebRLTotal AS 'UTILISED BUDGET', T3.Name AS Expr1, T3.Code,
T0.DebLTotal - T0.DebRLTotal AS 'BALANCE BUDGET',
(SELECT { fn CONCAT(T3.Code, T3.Name) } AS Expr1
WHERE (T3.SegmentId = '1')) AS 'Project',
(SELECT { fn CONCAT(T3.Code, T3.Name) } AS Expr1
WHERE (T3.SegmentId = '2')) AS 'Distt'
FROM dbo.OBGT AS T0 INNER JOIN
dbo.OACT AS T1 ON T0.AcctCode = T1.AcctCode INNER JOIN
dbo.OBGS AS T2 ON T0.Instance = T2.AbsId INNER JOIN
dbo.OASC AS T3 ON T3.SegmentId = '1' AND T3.Code = T1.Segment_1 OR T3.SegmentId = '2' AND T3.Code = T1.Segment_2
GO
and then to select from that view as below
然后从该视图中选择如下
SELECT View1.*
FROM View1
WHERE (Project = N'100') AND (Distt = N'001')
#1
1
You have to make it in 2 parts
你必须分为两部分
First, is to create a new view
首先,是创建一个新视图
CREATE VIEW [dbo].[View1]
AS
SELECT DISTINCT
T1.AcctName, T2.Name, T0.DebLTotal AS 'ANNUAL BUDGET KSH', T0.DebRLTotal AS 'UTILISED BUDGET', T3.Name AS Expr1, T3.Code,
T0.DebLTotal - T0.DebRLTotal AS 'BALANCE BUDGET',
(SELECT { fn CONCAT(T3.Code, T3.Name) } AS Expr1
WHERE (T3.SegmentId = '1')) AS 'Project',
(SELECT { fn CONCAT(T3.Code, T3.Name) } AS Expr1
WHERE (T3.SegmentId = '2')) AS 'Distt'
FROM dbo.OBGT AS T0 INNER JOIN
dbo.OACT AS T1 ON T0.AcctCode = T1.AcctCode INNER JOIN
dbo.OBGS AS T2 ON T0.Instance = T2.AbsId INNER JOIN
dbo.OASC AS T3 ON T3.SegmentId = '1' AND T3.Code = T1.Segment_1 OR T3.SegmentId = '2' AND T3.Code = T1.Segment_2
GO
and then to select from that view as below
然后从该视图中选择如下
SELECT View1.*
FROM View1
WHERE (Project = N'100') AND (Distt = N'001')