Hello @sdut, thank you for the reply!
I tried this query, creating my own test tables, and it did pass down. I ran this with Denodo, backed by Hive. If your user is still having issues with query pass down, might want to enter a tech support track for investigation.
options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i linesize=132 pagesize=max validvarname=any validmemname=extend noquotelenmax; LIBNAME x SASIOJDB jdbc_escapes=none user=XXXXXX password=XXXXXX DRIVERCLASS="com.denodo.vdb.jdbcdriver.VDBJDBCDriver" URL="jdbc:vdb://<server>:49999/sas_accesstesting" dbmax_text=1024 dbmax_text_types=all preserve_names=no /* materialized=yes */ schema="sas_accesstesting" preserve_names=yes ; proc sql;
connect using x;
execute by x (create materialized table cust (personal_id varchar(10), fielda varchar(10), fieldf varchar(10) ));
execute by x (create materialized table exp (balance_amt varchar(10), fielda varchar(10), fieldc varchar(10), fieldb varchar(10), fielde varchar(10) ));
execute by x (create materialized table int (interest_rate_pctv varchar(10), fieldb varchar(10), fieldc varchar(10), fieldd varchar(10) ));
quit;
proc sql;
select
cust.personal_id,
exp.balance_amt,
int.interest_rate_pctv
from
x.cust
inner join x.exp
on cust.fielda = exp.fielda
left join x.int
on exp.fieldb = int.fieldb
and exp.fieldc = int.fieldc
and int.fieldd = 'test'
where
exp.fielde = 'test'
and cust.fieldf = 'test';
quit;
JDBC_10: Prepared: on connection 0
SELECT * FROM "sas_accesstesting"."cust" WHERE 0=1
JDBC_11: Prepared: on connection 0
SELECT * FROM "sas_accesstesting"."exp" WHERE 0=1
JDBC_12: Prepared: on connection 0
SELECT * FROM "sas_accesstesting"."int" WHERE 0=1
JDBC_13: Prepared: on connection 0
select TXT_1."personal_id", TXT_2."balance_amt", TXT_3."interest_rate_pctv" from "cust" TXT_1 inner join "exp" TXT_2 on
TXT_1."fielda" = TXT_2."fielda" left join "int" TXT_3 on ((TXT_2."fieldb" = TXT_3."fieldb") and (TXT_2."fieldc" = TXT_3."fieldc"))
and ((TXT_3."fieldd" = 'test')) where (TXT_1."fieldf" = 'test') and (TXT_2."fielde" = 'test')
ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.
NOTE: No rows were selected.
Also, while researching this track I noticed that version 8.0u20210209 of the Denodo JDBC driver does support escape syntax. From their web page..
The driver now supports the JDBC escape syntax for date and time literals. For date literals, this is {d 'yyyy-mm-dd'} , for timestamp is {ts 'yyyy-mm-dd hh:mm:ss.f . . .'} and for time literals is {t 'hh:mm:ss'} .
HTH
... View more