topic SAS VIYA :- FEDSQL SUBQUERY ERROR in SAS Programming

SAS VIYA :- FEDSQL SUBQUERY ERROR

Rub_Saifi — Mon, 27 Mar 2023 08:17:51 GMT

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;

Re: SAS VIYA :- FEDSQL SUBQUERY ERROR

LinusH — Mon, 27 Mar 2023 08:33:58 GMT

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.

Re: SAS VIYA :- FEDSQL SUBQUERY ERROR

Rub_Saifi — Wed, 29 Mar 2023 06:45:54 GMT

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

Re: SAS VIYA :- FEDSQL SUBQUERY ERROR

LinusH — Wed, 29 Mar 2023 13:42:32 GMT

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?