October 13, 2014 at 12:31 am
Hi
I have a scenario where the second SELECT statement in my query has to be removed such that the logic needs to be included in the CTE select statement itself.
How this can be done? The columns in the final SELECT statement is having bit complexity.
My query is below:
WITH Local_EOL_Dioxin_Statistic_Import_New
AS
(
SELECT
CASE
WHEN ISNUMERIC(vr.stringValue) = 1 THEN vr.stringValue
ELSE ''
END AS MessergebnisNum,
WHEN ISNUMERIC(vr.stringValue) = 1 AND
vr.stringvalue NOT LIKE '%[^-+.]%' THEN CASE
WHEN (CONVERT(float, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (REPLACE (REPLACE (LTRIM(RTRIM(pt.limitOfQuantification)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),',','.')) * 100 > CONVERT(float, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (REPLACE (REPLACE (LTRIM(RTRIM(pt.limitOfQuantification)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),',','.')) * 100) -- multiply by 100 to eliminate the data type conversion confusion.
THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (REPLACE (REPLACE (LTRIM(RTRIM(pt.limitOfQuantification)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),',','.')
ELSE LTRIM(RTRIM(CONVERT(nvarchar(20), pt.limitOfQuantification)))
END
ELSE LTRIM(RTRIM(CONVERT(nvarchar(20), pt.limitOfQuantification)))
END AS LOQ,
CONVERT(nvarchar(20), ISNULL(vr.UncertaintyValue, '')) AS Messunsicherheit
FROM SampleTestFractions st
JOIN ValidatedResults VR
ON st.SampleTestFractionId = vr.SampleTestFractionId
JOIN PARAMETERS p
ON VR.PARAMETERID = P.PARAMETERID
AND P.ISDELETED = 0
JOIN ParametersTests AS pt
ON pt.parameterId = p.parameterId
AND pt.testId = t.TestId
AND pt.isDeleted = 0
SELECT
CASE
WHEN @LanguageSqlId = 2 AND @LanguageIncId = 5 THEN ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(MessergebnisNum)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'.',','), '')
ELSE ISNULL(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(MessergebnisNum)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'')
ENDAS MessergebnisNum,
CASE isDisplay_LOQ
WHEN 0x0 THEN ''
ELSE CASE
WHEN LEFT(LTRIM(MessergebnisAlpha), 1) = '<'
THEN CASE WHEN @LanguageSqlId = 2 AND @LanguageIncId = 5 THEN ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(MessergebnisAlpha)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'.',','), '')
ELSE ISNULL(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(MessergebnisAlpha)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'')
END
ELSE CASE WHEN @LanguageSqlId = 2 AND @LanguageIncId = 5 THEN ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(LOQ)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'.',','), '')
ELSE ISNULL(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(LOQ)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'')
END
END
END AS LOQ,
CASE
WHEN @LanguageSqlId = 2 AND @LanguageIncId = 5 THEN ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(CONVERT(nvarchar(20),MessunsicherheitDefault * 100))),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'.',','), '')
ELSE ISNULL(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(CONVERT(nvarchar(20), MessunsicherheitDefault * 100))),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'')
END AS Messunsicherheit -- Messunsicherheit (English: uncertainty) calculation
FROM Local_EOL_Dioxin_Statistic_Import_New T2
WHERE T2.testCode <> 'GFKND' OR ISNULL(T2.MessergebnisNum, '') <> ''
ORDER BY Probennummer,
[Parameter]
,
October 13, 2014 at 1:14 am
Why is it an issue there is a bit of complexity in the final SELECT?
You can rewrite the CTE so that it also does the logic of the final SELECT (by using subqueries), but what do you gain from it? Execution plan will stay the same.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2014 at 2:14 am
Koen. Thanks for your comment.
Please help me in rewriting the query. It is a code optimization comment to rewrite it.
October 13, 2014 at 3:18 am
It seems there is a ) missing that closes your CTE.
Where does it need to be?
After " AND pt.testId = t.TestId
AND pt.isDeleted = 0"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2014 at 3:45 am
I am posting the modified code. Sorry for the confusion.
WITH Local_EOL_Dioxin_Statistic_Import_New
AS
(
SELECT
CASE
WHEN ISNUMERIC(vr.stringValue) = 1 THEN vr.stringValue
ELSE ''
END AS MessergebnisNum,
CASE
WHEN ISNUMERIC(vr.stringValue) = 1 AND
vr.stringvalue NOT LIKE '%[^-+.]%' THEN CASE
WHEN (CONVERT(float, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (REPLACE (REPLACE (LTRIM(RTRIM(pt.limitOfQuantification)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),',','.')) * 100 > CONVERT(float, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (REPLACE (REPLACE (LTRIM(RTRIM(pt.limitOfQuantification)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),',','.')) * 100) -- multiply by 100 to eliminate the data type conversion confusion.
THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (REPLACE (REPLACE (LTRIM(RTRIM(pt.limitOfQuantification)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),',','.')
ELSE LTRIM(RTRIM(CONVERT(nvarchar(20), pt.limitOfQuantification)))
END
ELSE LTRIM(RTRIM(CONVERT(nvarchar(20), pt.limitOfQuantification)))
END AS LOQ,
CONVERT(nvarchar(20), ISNULL(vr.UncertaintyValue, '')) AS Messunsicherheit
FROM SampleTestFractions st
JOIN ValidatedResults VR
ON st.SampleTestFractionId = vr.SampleTestFractionId
JOIN PARAMETERS p
ON VR.PARAMETERID = P.PARAMETERID
AND P.ISDELETED = 0
JOIN Tests t
ON st.TestId = t.testId
JOIN ParametersTests AS pt
ON pt.parameterId = p.parameterId
AND pt.testId = t.TestId
AND pt.isDeleted = 0
)
SELECT
CASE
WHEN @LanguageSqlId = 2 AND @LanguageIncId = 5 THEN ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(MessergebnisNum)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'.',','), '')
ELSE ISNULL(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(MessergebnisNum)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'')
ENDAS MessergebnisNum,
CASE isDisplay_LOQ
WHEN 0x0 THEN ''
ELSE CASE
WHEN LEFT(LTRIM(MessergebnisAlpha), 1) = '<'
THEN CASE WHEN @LanguageSqlId = 2 AND @LanguageIncId = 5 THEN ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(MessergebnisAlpha)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'.',','), '')
ELSE ISNULL(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(MessergebnisAlpha)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'')
END
ELSE CASE WHEN @LanguageSqlId = 2 AND @LanguageIncId = 5 THEN ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(LOQ)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'.',','), '')
ELSE ISNULL(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(LOQ)),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'')
END
END
END AS LOQ,
CASE
WHEN @LanguageSqlId = 2 AND @LanguageIncId = 5 THEN ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(CONVERT(nvarchar(20),MessunsicherheitDefault * 100))),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'.',','), '')
ELSE ISNULL(REPLACE(REPLACE(REPLACE(Replace(Replace(Replace(LTRIM(RTRIM(CONVERT(nvarchar(20), MessunsicherheitDefault * 100))),Char(13),''),Char(9),''),Char(10),''),'<',''),'*',''),' ',''),'')
END AS Messunsicherheit -- Messunsicherheit (English: uncertainty) calculation
FROM Local_EOL_Dioxin_Statistic_Import_New T2
WHERE T2.testCode <> 'GFKND' OR ISNULL(T2.MessergebnisNum, '') <> ''
ORDER BY Probennummer,
[Parameter]
October 13, 2014 at 5:18 am
The following columns are missing in the CTE:
MessergebnisAlpha, MessunsicherheitDefault, testCode, Probennummer, [Parameter] and isDisplay_LOQ.
Also, in the CTE, you have the following CASE expression:
CASE WHEN (CONVERT(FLOAT,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(pt.limitOfQuantification)),CHAR(13),''),CHAR(9),''),CHAR(10),''),'<',''),'*',''),' ',''),',','.')) * 100
>CONVERT(FLOAT,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(pt.limitOfQuantification)),CHAR(13),''),CHAR(9),''),CHAR(10),''),'<',''),'*',''),' ',''),',','.')) * 100) -- multiply by 100 to eliminate the data type conversion confusion.
THEN ...
It looks like what is left of > is the same as what is right to >.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2014 at 5:36 am
The following columns are part of the CTE but I have omitted it because the problem where I face the difficulty in merging the SELECT statements into one is regarding the fields which I have mentioned in the CTE only.
Regarding the CASE expression , please leave that as it is. My requirement is different.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply