CODEWITHSUNDEEP
sqlt-sql
sam
sam

Reputation: 81

why are the results of variable and condition are different?

I was wondering to figure out this problem.

In the below query am trying to select @transcript as the combination of 2 column values. but when i am using this variable in select statement am getting only 3 results (actual output should be 8 results). where as when i use the condition directly in select statement am getting actual output.

can anybody please help me in figuring out this issue. 

declare @transcript varchar(10)
select @transcript = [CAREER_CD]+[CAREER_SUFX_CD] from dbo.SR0DAT
select DISTINCT @transcript transcriptCareerCode, 
case @transcript
     when 'U1' then 'BACCALAUREATE'
     when 'U2' then 'SECOND BACCALAUREATE'
     when 'G1' then 'GRADUATE'
     when 'L1' then 'LAW'
     when 'D1' then 'DENTISTRY'
     when 'M1' then 'MEDICINE'
     when 'IU' then 'transcriptCareerName'
     when 'IG' then 'IEO Graduate'
     end as transcriptCareerName
from dbo.SR0DAT
WHERE @transcript <>'G2'
union
select 'IU','IEO Undergraduate'
union
select 'IG','IEO Graduate'

output:

transcriptCareerCode    transcriptCareerName
G1  GRADUATE
IG  IEO Graduate
IU  IEO Undergraduate

2nd code:

select DISTINCT [CAREER_CD]+[CAREER_SUFX_CD] transcriptCareerCode, 
case [CAREER_CD]+[CAREER_SUFX_CD]
     when 'U1' then 'BACCALAUREATE'
     when 'U2' then 'SECOND BACCALAUREATE'
     when 'G1' then 'GRADUATE'
     when 'L1' then 'LAW'
     when 'D1' then 'DENTISTRY'
     when 'M1' then 'MEDICINE'
     when 'IU' then 'transcriptCareerName'
     when 'IG' then 'IEO Graduate'
     end as transcriptCareerName
from dbo.SR0DAT
WHERE [CAREER_CD]+[CAREER_SUFX_CD] !='G2'
union
select 'IU','IEO Undergraduate'
union
select 'IG','IEO Graduate'

output:

transcriptCareerCode    transcriptCareerName
D1  DENTISTRY
G1  GRADUATE
IG  IEO Graduate
IU  IEO Undergraduate
L1  LAW
M1  MEDICINE
U1  BACCALAUREATE
U2  SECOND BACCALAUREATE

Upvotes: 0

Views: 42

Answers (2)

shawnt00
shawnt00

Reputation: 17915

While both queries are indeed run against your table none of its columns are being used at all in the first one: for each row you're simply returning a fixed/constant expression. The distinct option is helping to mask what's going on and if you remove the that you'll see all of the multiple copies that are being collapsed into one row. If you also say select @transcript, * ... you'll see where the rest of the data is as well.

Are you just trying to accomplish an alias for the transcript expression so you don't have to repeat it?

with T as (
    select CAREER_CD + CAREER_SUFX_CD as transcript
    from dbo.SR0DAT
)
select DISTINCT
    transcript transcriptCareerCode, 
    case transcript
        when 'U1' then 'BACCALAUREATE'
        when 'U2' then 'SECOND BACCALAUREATE'
        when 'G1' then 'GRADUATE'
        when 'L1' then 'LAW'
        when 'D1' then 'DENTISTRY'
        when 'M1' then 'MEDICINE'
        when 'IU' then 'transcriptCareerName'
        when 'IG' then 'IEO Graduate'
    end as transcriptCareerName
from T
where transcript <> 'G2'
union
select 'IU', 'IEO Undergraduate'
union
select 'IG', 'IEO Graduate'

Upvotes: 1

Paurian
Paurian

Reputation: 1402

In your first query you are creating a singular value through the variable @transcript. As a result, your call to:

select @transcript = [CAREER_CD]+[CAREER_SUFX_CD] from dbo.SR0DAT

Only guarantees that the last record in the SELECT statement gets assigned to the @transcript variable.

As a result, only one row from that query will return (you've union'ed the other two rows in your first example).

The second example is using actual database set logic to pull in values across the set - not just the last value in the set.

Upvotes: 1

Related Questions