Query Builder Task Pass-Through Functionality

[sanyaahluwalia]

I am trying to use the Pass-through functionality using the Query Builder Task on SAS EG Demo Version, however, not able to correctly configure the query. I am using DB2 connection and hit the below error while running the below code:

PROC SQL;
CONNECT TO DB2 as con1
(Datasrc=DBQ4 authdomain=DB2_AUTH);

CREATE TABLE WORK.PRSN_DBPMTDTL AS
SELECT *
FROM CONNECTION TO con1 (
SELECT "t1"."PMT_ID",
"t1"."DB_ELEC_ID",
"t1"."OPFM_GP_ID",
"t1"."OPFM_ID"
FROM "PRSN_DBPMTDTL" "t1");

DISCONNECT FROM con1;
QUIT;

error hit: ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0204N "@a1234.PRSN_DBPMTDTL" is an undefined name. SQLSTATE=42704 

@a1234 is my user id, not sure why it is using it automatically as database instead of user id.

Also, the above query is automatically built through the pass-through functionality. Do I need to add any other options to provide the correct database name?

If I edit my code to below, it works fine. I want to explore more options through which the below code can be generated automatically:

PROC SQL;
CONNECT TO DB2 as con1
(Datasrc=DBQ4 authdomain=DB2_AUTH);

CREATE TABLE WORK.PRSN_DBPMTDTL AS
SELECT *
FROM CONNECTION TO con1 (
SELECT "t1"."PMT_ID",
"t1"."DB_ELEC_ID",
"t1"."OPFM_GP_ID",
"t1"."OPFM_ID"
FROM Q401012B.PRSN_DBPMTDTL" "t1");

DISCONNECT FROM con1;
QUIT;

[ChrisNZ]

The code that works uses a 2-level table name Q401012B.PRSN .

Why do you only use the table name PRSN_DBPMTDTL  in your failing query?

[sanyaahluwalia]

Thanks for your response! Just corrected my above question. I am using the in-built Query builder task pass through functionality under the options, which is automatically creating the first code, and it is hitting error as provided above. I want this tool to generate the code which has Q401012B.PRSN_DBPMTDTL instead of @userid..PRSN_DBPMTDTL. Do you have any idea about it?

[s_lassen]

When you edit the code, you refer to the input table as Q401012B.PRSN. But in your generated code, you refer to the input table as PRSN_DBPMTDTL, which is the same name as the output table. I do not know the Query Builder Task very well, but it should be possible to give it the name of an input table, which should the be the same as in your edited code..

[sanyaahluwalia]

Thanks for your response! Just corrected my above question. I am using the in-built Query builder task pass through functionality under the options, which is automatically creating the first code, and it is hitting error as provided above. I want this tool to generate the code which has Q401012B.PRSN_DBPMTDTL instead of @userid..PRSN_DBPMTDTL. Do you have any idea about it?