SAS VIYA :- FEDSQL SUBQUERY ERROR

[Rub_Saifi]

Hi experts,

I am facing an error in SAS Viya.

Error -  SAS FEDSQL doesn't supports Subquery in its Syntax. While using subquery in the code its throwing error.As i want to use in Fedsql itself, I can't use this code in Proc Sql as I'm fetching the data from database.

How we can resolve it. Can you please help me out?

Code -:

Proc Fedsql Mysession = Sessref;

create table casuser.table {options replace = true compress - true} as

select
htd.TRAN_DATE,htd.tran_amt,htd.tran_id,decode(htd.part_tran_type,'C','CREDIT','DEBIT') part_tran_type,gam.acid as gamacid,htd.acid as htdacid,
(select foracid from tbaadm.gam@finlink where acid=htd.acid)account
from tbaadm.htd@finlink,tbaadm.gam@finlink
where gam.foracid IN ('0582955100398')
and acct_cls_flg='N'
and entity_cre_flg='Y'
and htd.pstd_flg='Y'
and htd.del_flg='N'
and htd.tran_date BETWEEN '23-Mar-2023' AND '23-Mar-2023'
and (tran_id,tran_date) in (select tran_id,tran_date from tbaadm.htd@finlink where htd.acid=gam.acid);

quit;

[LinusH]

Hi,

I'm a bit confused since FedSQL should support sub-queries.

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.3/casfedsql/p1ucns3lrhzhamn1k6fw6vhzhzam.htm

Can you post the complete log?

And when you mention database, you refer to CAS, right?

Since if your data is in an external DB Proc SQL works.

[Rub_Saifi]

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:

My log Error:-

NOTE: CAS action completed [OKAY]
FEDSQL: load of action set returned rc=00000000
ERROR: Unsupported operation in FedSQL query: Correlated subquery.
ERROR: Unsupported operation in FedSQL query: IN/ANY/ALL subquery.
ERROR: The action stopped due to errors.
ERROR: The FedSQL action was not successful.
FEDSQL: The fedsql.execDirect action returned rc=0x000003f4 

[LinusH]

Without knowing more details about your data and specific business logic to be achieved, it's hard to tell how to rewrite the query.

Alternatives could be using EXISTS or some kind of inner join logic?