Code in EG using query builder

[anirudhs]

Hi I want to code the fllowing code in EG using the query builder, can anyone help for doing the same in EG.

If Substr(Agency_Name,1,7)= 'INHOUSE' then

do;

Agency_Name ='INHOUSE';

Agency_Code ='INHOUSE';

end;

Else If Substr(Agency_Name,1,7)= '' then

do;

Agency_Name = 'UNALLOCATED';

Agency_Code = 'UNALLOCATED';

End;

Thanks

[anirudhs]

Hi please help want to code the following condition in EG  using the query builder.

If Substr(Agency_Name,1,7)= 'INHOUSE' then

do;

Agency_Name ='INHOUSE';

Agency_Code ='INHOUSE';

end;

Else If Substr(Agency_Name,1,7)= '' then

do;

Agency_Name = 'UNALLOCATED';

Agency_Code = 'UNALLOCATED';

End;

Thank you

[anirudhs]

Agency _code & Agency_Name are two different variables... so when Agency_name is Unallocated then other variable be should also allocate the same value.

[RW9]

Yes.  Please refer to SQL programming help, each variable is "selected" from a datasource so you need at the end:

select case when substr(agency_name,1,7)="INHOUSE" then "INHOUSE"
            else "UNALLOCATED" end as agency_name,
       case when substr(agency_name,1,7)="INHOUSE" then "INHOUSE"
            else "UNALLOCATED" end as agency_code
...

You cannot set the value of two output variables in one select <variable> line, you need to select data as variable, data as variable,...

[gamotte]

Hello,

It is not said in the question if substr(agency_name,1,7) can be somethng else than 'INHOUSE' or ' '.

Also, substrn should be used instead of substr if the length of agency_name can be shorter than 7.

select case when substrn(agency_name,1,7)="INHOUSE" then "INHOUSE"
            when substrn(agency_name,1,7)=' ' then "UNALLOCATED" 
       else agency_name end as agency_name,

[RW9]

I assume the "query builder" creates SQL, so:

case when substr(agency_name,1,7)="INHOUSE" then "INHOUSE"
         else "UNALLOCATED" end

For the first variable and then for the second (as each variable is separate in a select clause):

case when substr(agency_name,1,7)="INHOUSE" then "INHOUSE"
         else "UNALLOCATED" end