why am I getting an extra var?

Question

why am I getting an extra var?

diva_di_dati

SSC Journeyman

Points: 77
More actions
May 23, 2012 at 12:35 pm

#256348

If I run
select RECIP as recipno,
DIAGNOSIS_CODE as diag_1,
/*icd 9 codes must be 5 in length without decimal code*/
CASE
WHEN dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE IS NOT NULL
THEN
CASE
WHEN SUBSTRING(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)),
CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))), 1) =
RIGHT(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), 1)
THEN CAST(LEFT(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)),
CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))) - 1)
AS varchar(5))
WHEN LEN(SUBSTRING(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)),
CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))),
CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))) + 1)) > 1
THEN CAST(PARSENAME(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), 2) +
PARSENAME(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), 1)
AS varchar(5))
END
ELSE NULL
END AS diag_1
from dbo.p_DMAS_MEDICAL_TRANSITION
inner join dbo.m_Enrollment_months
on dbo.m_Enrollment_months.MED_ID= dbo.p_DMAS_MEDICAL_TRANSITION.recip
where dbo.m_Enrollment_months.DOS_DATE between '10/1/2011' and '10/31/2011'
I get recipno and diag_1 as I expect but also a second diag_1 var which I did not expect and is all null ...if I take outthe last end as diag_1 it will just keep that second var without a name. What code do I omit to just get the recipno and fisrt diag_1?
Thx

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

diva_di_dati (5/23/2012)
If I run
select RECIP as recipno,
DIAGNOSIS_CODE as diag_1,
/*icd 9 codes must be 5 in length without decimal code*/
CASE
WHEN dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE IS NOT NULL
THEN
CASE
WHEN SUBSTRING(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)),
CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))), 1) =
RIGHT(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), 1)
THEN CAST(LEFT(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)),
CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))) - 1)
AS varchar(5))
WHEN LEN(SUBSTRING(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)),
CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))),
CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))) + 1)) > 1
THEN CAST(PARSENAME(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), 2) +
PARSENAME(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), 1)
AS varchar(5))
END
ELSE NULL
END AS diag_1
from dbo.p_DMAS_MEDICAL_TRANSITION
inner join dbo.m_Enrollment_months
on dbo.m_Enrollment_months.MED_ID= dbo.p_DMAS_MEDICAL_TRANSITION.recip
where dbo.m_Enrollment_months.DOS_DATE between '10/1/2011' and '10/31/2011'
I get recipno and diag_1 as I expect but also a second diag_1 var which I did not expect and is all null ...if I take outthe last end as diag_1 it will just keep that second var without a name. What code do I omit to just get the recipno and fisrt diag_1?
Thx

You have two columns in your select with the same alias.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

diva_di_dati SSC Journeyman Points: 77 More actions · Answer 2

yes, I figured that when I omited the end as diag_1 from the end and still got the column of data.....where am I getting that last column from in the code.....I can'tseem to isolate it...

thx

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Because you are using the diag_1 alias for the column DIAGNOSIS_CODE and for the computed column with the CASE functions.

SELECT

RECIP AS recipno

,DIAGNOSIS_CODE AS diag_1 -- < Here

,

/*icd 9 codes must be 5 in length without decimal code*/

CASE

WHEN dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE IS NOT NULL

THEN CASE

WHEN SUBSTRING(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))), 1) = RIGHT(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), 1)

THEN CAST(LEFT(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))) - 1) AS VARCHAR(5))

WHEN LEN(SUBSTRING(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))), CHARINDEX('.', LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE))) + 1)) > 1

THEN CAST(PARSENAME(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), 2) + PARSENAME(LTRIM(RTRIM(dbo.p_DMAS_MEDICAL_TRANSITION.DIAGNOSIS_CODE)), 1) AS VARCHAR(5))

END

ELSE NULL

END AS diag_1 -- < And Here

FROM

dbo.p_DMAS_MEDICAL_TRANSITION

INNER JOIN dbo.m_Enrollment_months

ON dbo.m_Enrollment_months.MED_ID = dbo.p_DMAS_MEDICAL_TRANSITION.recip

WHERE

dbo.m_Enrollment_months.DOS_DATE BETWEEN '10/1/2011' AND '10/31/2011'

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

diva_di_dati (5/23/2012)
yes, I figured that when I omited the end as diag_1 from the end and still got the column of data.....where am I getting that last column from in the code.....I can'tseem to isolate it...
thx

Perhaps formatting it will shed some light??? I used the formatter at http://poorsql.com

SELECT RECIP AS recipno

,DIAGNOSIS_CODE AS diag_1

,