Is DECLARE what I should be using in order to reference a created field within the same program

Question

I'm trying to create a new field and then later on in my program reference that new field within a case statement, but I can't seem to get the syntax right - my error message says there's an error near the '='.

Here's my code:

declare @source_sys_obligation_id varchar(40);
if facility_utilization in ('F')  
    set source_sys_obligation_id = source_sys_facility_id
 else set source_sys_obligation_id = source_sys_utilization_num;
select
source_sys_utilization_num
,source_sys_id
,facility_utilization
,case when source_sys_id in ('AFSEAST','AFSLSAL','DFBDOM','ACBS')
    then right('000000000000000' + substring(source_sys_obligation_id,6,10),16)

      when source_sys_id in ('MLSTLEND') 
    then  right('000000000000000' + left(source_sys_obligation_id,15),16)

    else '' end as No
from    BridgeUnderwrite.dbo.t_sag_pimsc1
where   source_sys_id in ('AFSEAST','AFSLSAL','DFBDOM','ACBS','MLSTLEND')
order by source_sys_id
;

Answer 1

The error is in reference to the set statements. They should look like:

if facility_utilization in ('F')
    set @source_sys_obligation_id = source_sys_facility_id 
else 
    set @source_sys_obligation_id = source_sys_utilization_num;

That ought to do it :) . . . however, source_sys_facility_id and source_sys_utilization_num are most likely going to be your next issues . . . are they variables (or perhaps parameters passed in) as well?

Answer 2

The '@' is part of the name. All T-SQL variable names or procedure parameters have to begin with this character (I assume the reason is so they are easy to discern from table and column names). So you probably need to say set @source_sys_obligation_id ... instead of set source_sys_obligation_id ....

Answer 3

What you want and what you can have are not the same! You cannot create a field in a select stament and then reference it in the same select statment in a case. Your code makes no sense at all and indcates a severe lack of understanding of how variables work.

declare @source_sys_obligation_id varchar(40); 
if facility_utilization in ('F')   
    set source_sys_obligation_id = source_sys_facility_id 
 else set source_sys_obligation_id = source_sys_utilization_num; 

This does not add columns to a select nor would it even populate anything even adding the @sign as some others have suggested because you do not have a select here. Further a variable can only have one value, not a different value per record. So scrap this whole approach. What you really need is a derived table or a CTE. You could also simply embed the first case in the second case. Something like this might get waht you are asking for:

SELECT a.source_sys_utilization_num   
,a.source_sys_id   
,a.facility_utilization 
,CASE WHEN a.source_sys_id in ('AFSEAST','AFSLSAL','DFBDOM','ACBS')   
    THEN RIGHT('000000000000000' + SUBSTRING(a.source_sys_obligation_id,6,10),16)   
      WHEN a.source_sys_id in ('MLSTLEND')    
    THEN  RIGHT('000000000000000' + LEFT(a.source_sys_obligation_id,15),16)   
    ELSE '' END AS [No]  
FROM 
(SELECT   
source_sys_utilization_num   
,source_sys_id   
,facility_utilization   
 ,CASE WHEN facility_utilization = 'F' THEN source_sys_facility_id
 ELSE source_sys_utilization_num END AS source_sys_obligation_id
FROM    BridgeUnderwrite.dbo.t_sag_pimsc1   
WHERE   source_sys_id in ('AFSEAST','AFSLSAL','DFBDOM','ACBS','MLSTLEND'))  a 
ORDER BY source_sys_id   

Too busy to write the other versions. Maybe someone else will supply.