I don't how to modify it in the below query.
Could you plz. modify it in the below query.
----------------------------------------------
Create Procedure both_str_proc_v12
@relyr char(4),
@relno char(2),
@dotno char(1),
@budget57 char(6),
@budget58 char(6),
@budget59 char(6),
@region78 char(15),
@region80 char(15),
@region82 char(15),
@region84 char(15),
@status98 char(1),
@status100 char(1)
as
BEGIN
SELECT dbo.RSM_Project.LOB, dbo.RSM_Project.ProjScope,
dbo.RSM_Project.RelYear, dbo.RSM_Project.ProjectType, dbo.RSM_Project.RelNum,
dbo.RSM_Project.DotNum, dbo.RSM_Project.BudgetCategory,
dbo.RSM_Project.ProjSeqNum, dbo.RSM_Project.ProjectId,
dbo.RSM_Project.ProjectTitle,
PCF=(dbo.RSM_Project.BizPCFImpact+'/'+dbo.RSM_Project.ITPCFImpact),
APPS = SUM(APPSSME+APPSDev+APPSSMEICF+APPSDevICF),
AutoBatch=SUM(AutobatchSME+AutobatchDev+AutobatchSMEICF+AutobatchDevICF),
AutoEdge=SUM(AutoEDGESME+AutoEDGEDev+AutoEDGESMEICF+AutoEDGEDevICF),
FireBatch =SUM(FirebatchSME+FirebatchDev+FirebatchSMEICF+FirebatchDevICF),
eAuto=SUM(eAutoSME+eAutoDev+eAutoSMEICF+eAutoDevICF),
eHome =SUM(eHomeSME+eHomeDev+eHomeSMEICF+eHomeDevICF),
FPPS=SUM(FPPSSME+FPPSDev+FPPSSMEICF+FPPSDevICF+CDJSME+CDJDev+CDJSMEICF+CDJDevICF+FireNetworkSME+FireNetworkDev+FireNetworkSMEICF+FireNetworkDevICF),
FireEdge=SUM(FireEDGESME+FireEDGEDev+FireEDGESMEICF+FireEDGEDevICF),
Tables =SUM(TablesSME+TablesDev+TablesSMEICF+TablesDevICF),
Total= SUM(APPSSME+APPSDev+APPSSMEICF+APPSDevICF+
AutobatchSME+AutobatchDev+AutobatchSMEICF+AutobatchDevICF+
AutoEDGESME+AutoEDGEDev+AutoEDGESMEICF+AutoEDGEDevICF+
FirebatchSME+FirebatchDev+FirebatchSMEICF+FirebatchDevICF+
eAutoSME+eAutoDev+eAutoSMEICF+eAutoDevICF+
FPPSSME+FPPSDev+FPPSSMEICF+FPPSDevICF+CDJSME+CDJDev+CDJSMEICF+CDJDevICF+FireNetworkSME+FireNetworkDev+FireNetworkSMEICF+FireNetworkDevICF+
eHomeSME+eHomeDev+eHomeSMEICF+eHomeDevICF+
FireEDGESME+FireEDGEDev+FireEDGESMEICF+FireEDGEDevICF+
TablesSME+TablesDev+TablesSMEICF+TablesDevICF),
dbo.RSM_Project.Emergency, dbo.RSM_Project.ProjCategory,
dbo.RSM_Project.BRISubmittedBy, dbo.RSM_Project.ITSME,
CEA =SUM(CEASME+CEADEV+CEASMEICF+CEADEVICF),
Region=dbo.RSM_ProjectState.Region+space(10),
dbo.RSM_Project.ProjStatus
INTO #RptResult
FROM dbo.RSM_Project, dbo.RSM_ProjectROM, dbo.RSM_ProjectState
WHERE dbo.RSM_Project.ProjSeqNum = dbo.RSM_ProjectROM.ProjSeqNum AND
dbo.RSM_Project.ProjSeqNum *= dbo.RSM_ProjectState.ProjSeqNum AND
(((dbo.RSM_Project.RelYear)= @relyr) AND
((dbo.RSM_Project.RelNum)= @relno) AND
((dbo.RSM_Project.DotNum)= @dotno) AND
((dbo.RSM_Project.BudgetCategory)= @budget57 Or
(dbo.RSM_Project.BudgetCategory)= @budget58 Or
(dbo.RSM_Project.BudgetCategory)= @budget59) AND
((dbo.RSM_ProjectState.Region)= @region78 Or
(dbo.RSM_ProjectState.Region)= @region80 Or
(dbo.RSM_ProjectState.Region)= @region82 Or
(dbo.RSM_ProjectState.Region)= @region84) AND
((dbo.RSM_Project.ProjStatus)= @status98 Or
(dbo.RSM_Project.ProjStatus)= @status100) AND
((dbo.RSM_Project.BudgetCategory) in ('BASE','ECP','CAP')) AND
((dbo.RSM_Project.Projecttype) in
('RTDP','R&R','CLNUP','REBL','PROJ','IDF','CAP','PST')))
GROUP BY dbo.RSM_Project.LOB,
dbo.RSM_Project.ProjScope,
dbo.RSM_Project.RelYear,
dbo.RSM_Project.ProjectType,
dbo.RSM_Project.RelNum,
dbo.RSM_Project.DotNum,
dbo.RSM_Project.BudgetCategory,
dbo.RSM_Project.ProjSeqNum,
dbo.RSM_Project.ProjectId,
dbo.RSM_Project.ProjectTitle,
dbo.RSM_Project.BizPCFImpact,
dbo.RSM_Project.ITPCFImpact,
dbo.RSM_Project.Emergency,
dbo.RSM_Project.ProjCategory,
dbo.RSM_Project.BRISubmittedBy,
dbo.RSM_Project.ITSME,
dbo.RSM_ProjectState.Region,
dbo.RSM_Project.ProjStatus
ORDER BY (dbo.RSM_Project.BudgetCategory) DESC,
case
when LOB = 'A' then 1
when LOB = 'B' then 2
when LOB = 'F' then 3
end,
case
when Projecttype = 'PROJ' then 1
when Projecttype = 'IDF' then 2
when Projecttype = 'CLNUP' then 3
when Projecttype = 'REBL' then 4
when Projecttype = 'R&R' then 5
when Projecttype = 'RTDP' then 6
when Projecttype = 'CAP' then 7
when Projecttype = 'PST' then 8
end
DECLARE @ResultCnt int, @ResCounter int, @ProjSeqNum int, @RegionCnt int,
@RegionCounter int, @AllRegions varchar(30), @Region varchar(10)
Select * into #temp from #RptResult
Select @ResultCnt = count(*) from #temp
Set Rowcount 1
Select @ResCounter = 0
WHILE @ResCounter <= @ResultCnt
BEGIN
Select @ProjSeqNum = ProjSeqNum FROM #temp
set rowcount 0
IF (Select Count(*) FROM RSM_ProjectState WHERE ProjSeqNum = @ProjSeqNum
and Region is Not Null) > 0
BEGIN
Select Region into #region from RSM_ProjectState WHERE ProjSeqNum =
@ProjSeqNum and Region is Not Null
Select @RegionCnt = Count(*) FROM #region
set rowcount 1
Select @RegionCounter = 1, @AllREgions = ''
IF @RegionCnt = 1 ---- Handles ONE region ----------------
BEGIN
Select @AllRegions = Region from #region
END
ELSE
BEGIN ------ Handles Multiple Regions ------------------
While @RegionCounter <= @RegionCnt
BEGIN
Select @Region = ltrim(rtrim(Region)) FROM #region
IF IsNull(charindex(@Region,@AllRegions),'0') = 0
BEGIN
IF @RegionCounter = 1
BEGIN SELECT @AllRegions = @Region END
ELSE
BEGIN Select @AllRegions = IsNull(@AllRegions,'') +
',' + @Region END
END
SELECT @RegionCounter = @RegionCOunter + 1
Delete from #region
END
UPDATE #RptResult
Set Region = @AllRegions
Where ProjseqNum = @ProjSeqNum
END
Drop Table #region
END
Set Rowcount 1
SELECT @ResCounter = @ResCounter + 1
Delete from #temp
END
Set rowcount 0
SELECT * from #RptResult
--Drop table #temp, #RptResult
END
GO
-------------------------------------------------
thanks for your hel in advance.
Krishnakanth. VM
John W. Vinson said:
when I modify the query as follows.
CASE budgetcategory
WHEN 'ECP' THEN 1
WHEN 'CAP' THEN 2
ELSE 3
END as SortVal1
CASE ProjectType
WHEN 'PROJ' THEN 1
WHEN 'IDF' THEN 2
WHEN 'CLNUP' THEN 3
WHEN 'REBL' THEN 4
WHEN 'R&R' THEN 5
WHEN 'RTDP' THEN 6
WHEN 'CAP' THEN 7
ELSE 8
END as SortVal2
I am getting the error message as follows.
Server: Msg 156, Level 15, State 1, Procedure auto_or_fire_str_proc_vx1,
Line 88
Incorrect syntax near the keyword 'as'.
Server: Msg 156, Level 15, State 1, Procedure auto_or_fire_str_proc_vx1,
Line 150
Incorrect syntax near the keyword 'END'.
Is this in Microsoft Access, or in SQL/Server? Access does not support
the CASE syntax, which is probably why you're getting the error.
Try using the Switch() function instead:
SELECT Switch([budgetcategory] = "ECP", 1, [budgetcategory] = "CAP",
2, True, 3) AS SortVal1,
Switch([ProjectType] = "PROJ", 1,
[ProjectType] = "IDF", 2,
[ProjectType] = "CLNUP", 3,
[ProjectType] = "REBL", 4,
[ProjectType] = "R&R", 5,
[ProjectType] = "RTDP", 6,
[ProjectType] = "CAP", 7,
True, 8) AS SortVal2
John W. Vinson [MVP]
