SAS VIYA : proc fed sql error

[Aexor]

I have a proc fed sql query where I am passing a query as text .

I wanted to run a another query . but I am getting error 

ERROR: Unsupported operation in FedSQL query: IN/ANY/ALL subquery.

proc cas;
fedsql.execdirect status=s/
query="create table CAS_LIB.newt {options replace=true} as
select a.buis_id as b_id ,
b.plan_set as p_s ,
b.version_no as ver_no ,
a.date as date ,
a.mkd_price_amt as mkd_price_amt ,
from old_cas.tab1 as a,
 old_cas.tab2 as b
where a.buis_id in
(select id from new_cas.tab3) and
upcase(a.a.buis_id) = upcase(b.buis_id)";

How can I use this subquery here in fed sql.

[sbxkoenk]

Hello,

FedSQL for CAS does not support use of non-correlated subqueries with the IN, ANY, and ALL predicates. For example, the following non-correlated subquery is not supported:

select * from table1 where x in (select x from table2);

At least this is true for SAS VIYA 3.5.
It's documented here :
FedSQL Programming for CAS
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/casfedsql/p1ucns3lrhzhamn1k6fw6vhzhzam.htm

Good luck,

Koen

[Aexor]

Yes It is not working. Any other option I can use in place of subquery ?

[SASKiwi · Accepted solution]

Sub-queries NOT using IN operator are OK. Try something like this:

proc cas;
fedsql.execdirect status=s/
query="create table CAS_LIB.newt {options replace=true} as
select a.buis_id as b_id ,
b.plan_set as p_s ,
b.version_no as ver_no ,
a.date as date ,
a.mkd_price_amt as mkd_price_amt ,
from old_cas.tab1 as a,
 old_cas.tab2 as b
left join
(select distinct id from new_cas.tab3) as C
on A.buis_id = C.ID
where upcase(a.buis_id) = upcase(b.buis_id)
and not missing(C.ID)";

[Aexor]

Thank you. I am getting error like ERROR: Table "A" not found in FROM clause

[SASKiwi]

Please post your complete SAS log then including code and errors. We can't help without evidence.

[Aexor]

issue resolved, I was using wrong table reference . thank you