September 16, 2013 at 11:27 am
Hello all, I "inherited" the following code as part of a query for a report:
-- January
if @MonthUnits = 1 begin
insert into #tmpJDEnbrunits
select '000' + ltrim(MCMCU), GBAN01/100, mcdl02
from [JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0902,
[JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0006
where GBMCU = MCMCU and
GBFY = @fy and GBLT = 'AU' and
GBOBJ = '9900' and GBSUB = '006' and
MCSTYL in ('R') AND MCMCU<' 999' AND
MCRP22<>'X'
end
-- February
if @MonthUnits = 2 begin
insert into #tmpJDEnbrunits
select '000' + ltrim(MCMCU), GBAN02/100, mcdl02
from [JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0902,
[JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0006
where GBMCU = MCMCU and
GBFY = @fy and GBLT = 'AU' and
GBOBJ = '9900' and GBSUB = '006' and
MCSTYL in ('R') AND MCMCU<' 999' AND
MCRP22<>'X'
end
And so on through December.
As you can see, the only thing changing for each month is the two digits at the end of the "GBAN" field (GBAN01 for January, GBAN02 for February, etc.) based on the value of @MonthUnits (again, 1 for Jan, 2 for Feb and so on). I think there has to be a more efficient way of coding this, can anyone help?
Thanks in advance.
September 16, 2013 at 1:05 pm
there area couple of other ways to accomplish this but I dont think either will be more efficient. you can use a case statement or dynamic sql.
here is an example using IF, CASE, and dynamic
CREATE TABLE #temp(a01 INT, a02 INT, a03 INT)
GO
INSERT INTO #temp
VALUES(1,2,3)
GO 10
DECLARE @i INT,
@C NCHAR(3),
@sql NVARCHAR(100)
SET @i = 2
SELECT CASE WHEN @i = 1 THEN a01
WHEN @i = 2 THEN a02
WHEN @i = 3 THEN a03
END AS a
FROM #temp;
SET @C = 'a'+RIGHT('00'+CAST(@i AS NCHAR(1)),2)
PRINT @C
SET @sql = 'select '+@c+' from #temp;'
PRINT @sql
EXEC (@sql);
IF @i = 1
BEGIN
SELECT a01 FROM #temp
END
IF @i = 2
BEGIN
SELECT a02 FROM #temp
END
IF @i = 3
BEGIN
SELECT a03 FROM #temp
END
DROP TABLE #temp
September 16, 2013 at 2:16 pm
Thanks Bob. Maybe "efficient" is the wrong word - it just kills me to see that block of code repeated twelve times.
September 16, 2013 at 7:16 pm
jkalmar 43328 (9/16/2013)
Thanks Bob. Maybe "efficient" is the wrong word - it just kills me to see that block of code repeated twelve times.
Me too.:-)
First, I would be remiss in not saying that whatever table or view that the GBANxx columns are in is in pretty bad shape insofar as normalizing. Assuming that you can't actually do anything to correct that problem, I have a couple of questions to try to arrive at a single query but still flexible solution.
1. What is the datatype of the @MonthUnits variable?
2. What is the datatype of the @FY variable?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2013 at 6:07 am
It's definitely not properly normalized and no, I can't do anything about it right now, unfortunately.
Both those variables are declared as char(2) - here's the code that declares and sets them:
declare @FY1 char(4)
set @FY1 = (DATEPART(yy,DATEADD(m,-2,GETDATE()) ))
declare @fy char(2)
set @fy = SUBSTRING(@fy1,3,2)
declare @MonthUnits char (2)
set @MonthUnits = datepart(mm, dateadd(mm, -2, getdate()))
September 17, 2013 at 10:35 am
INSERT INTO #tmpJDEnbrunits
SELECT
'000' + ltrim(MCMCU),
CASE @MonthUnits WHEN 1 THEN GBAN01 WHEN 2 THEN GBAN02 WHEN 3 THEN GBAN03 --...
END,
mcdl02
FROM [JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0902,
[JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0006
WHERE
GBMCU = MCMCU and
GBFY = @fy and GBLT = 'AU' and
GBOBJ = '9900' and GBSUB = '006' and
MCSTYL in ('R') AND MCMCU<' 999' AND
MCRP22<>'X'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 17, 2013 at 11:01 am
Aaand now it's blindingly obvious. Brilliant. Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply