SUPPORT / SAMPLES & SAS NOTES
Problem Note 66226: Incorrect output occurs when the FEDSQL procedure joins two tables with a join key value of VARCHAR(8192)
 |  |  |  |  |
When you join two tables by using PROC FEDSQL, you might see that the output is not accurate. For example, the joined table has four rows of output, but it should have only two rows.
This issue occurs if the key value is defined as follows:
length key varchar(8192);
Here is an example of code that causes this issue:
cas casauto;
libname casuser cas caslib=casuser;
data casuser.tableA;
infile datalines dsd;
length key varchar(8192) value varchar(8192);
input key $ value $;
datalines;
1, james
2, morris
;
infile datalines dsd;
length key varchar(8192) value varchar(8192);
input key $ value $;
datalines;
1, james
2, morris
;
data casuser.tableB;
infile datalines dsd;
length key varchar(8192) value varchar(8192);
input key $ value $;
datalines;
1, james
2, morris
;
infile datalines dsd;
length key varchar(8192) value varchar(8192);
input key $ value $;
datalines;
1, james
2, morris
;
proc fedsql sessref=&_sessref_.;
create table casuser.tableC {options replace=true} as
select
a.key as akey,
b.key as bkey,
a.value as avalue,
b.value as bvalue
from casuser.tableA as a
inner join casuser.tableB as b
on a.key=b.key
;
quit;
create table casuser.tableC {options replace=true} as
select
a.key as akey,
b.key as bkey,
a.value as avalue,
b.value as bvalue
from casuser.tableA as a
inner join casuser.tableB as b
on a.key=b.key
;
quit;
As a workaround, you can use the VARCHAR(*) variable. The asterisk indicates that a maximum storage size of 536,870,911 characters can be used.
Click the Hot Fix tab in this note for a link to instructions about accessing and applying the software update.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Viya | Linux for x64 | 3.4 | 3.4 | Viya | Viya |