topic Re: SAS VIYA : proc fed sql error in SAS Programming

SAS VIYA : proc fed sql error

Aexor — Fri, 24 Feb 2023 13:42:38 GMT

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.

Re: SAS VIYA : proc fed sql error

sbxkoenk — Fri, 24 Feb 2023 14:10:12 GMT

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

Re: SAS VIYA : proc fed sql error

Aexor — Sat, 25 Feb 2023 15:45:53 GMT

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

Re: SAS VIYA : proc fed sql error

SASKiwi — Sun, 26 Feb 2023 21:54:04 GMT

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)";

Re: SAS VIYA : proc fed sql error

Aexor — Tue, 28 Feb 2023 08:43:47 GMT

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

Re: SAS VIYA : proc fed sql error

SASKiwi — Tue, 28 Feb 2023 19:01:26 GMT

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

Re: SAS VIYA : proc fed sql error

Aexor — Fri, 03 Mar 2023 14:24:15 GMT

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