topic Re: SQL error on DBInput - how to debug? in Talend Studio

SQL error on DBInput - how to debug?

Anonymous — Sat, 16 Nov 2024 04:06:08 GMT

Hi!

I get a SQL error message when running a query with context variables. I have tried to figure out what the syntax error is but I can't reproduce it manually, when replacing the context variables by hand and runs the query it works in SQL server. So how can I get my query with context variables replaced with values? I tried globalMap in a tJava but only get Null back. See below. What can I do to get the query that Talend sends to Sql server?

Re: SQL error on DBInput - how to debug?

TRF — Sun, 17 Nov 2019 21:09:04 GMT

Can you share the query?

Re: SQL error on DBInput - how to debug?

Anonymous — Sun, 17 Nov 2019 22:35:33 GMT

"WITH CREATE_STG AS(
SELECT DISTINCT
CONCAT("+context.FOCAL_IDFR01+","+context.FOCAL_IDFR02+","+context.FOCAL_IDFR03+","+context.FOCAL_IDFR04+","+context.FOCAL_IDFR05+") AS FOCAL_IDFR,
CONCAT("+context.FOCAL_R_IDFR01+","+context.FOCAL_R_IDFR02+","+context.FOCAL_R_IDFR03+","+context.FOCAL_R_IDFR04+","+context.FOCAL_R_IDFR05+") AS FOCAL_R_IDFR,
"+context.R_TYPE_IDFR+" AS R_TYPE_IDFR,
CONCAT("+context.R_FOCAL_IDFR01+","+context.R_FOCAL_IDFR02+","+context.R_FOCAL_IDFR03+","+context.R_FOCAL_IDFR04+","+context.R_FOCAL_IDFR05+","+context.R_FOCAL_IDFR06+","+context.R_FOCAL_IDFR07+","+context.R_FOCAL_IDFR08+","+context.R_FOCAL_IDFR09+","+context.R_FOCAL_IDFR10+") AS R_FOCAL_IDFR"
+"
FROM dbo.\""+context.SRC_REL_TABLE+"\"
)

,GET_KEY AS(
SELECT
KDW01.FOCAL_KEY AS FOCAL_KEY,
KDW02.FOCAL_KEY AS FOCAL_R_KEY,
KDW03.FOCAL_KEY AS R_TYPE_KEY,
KDW04.FOCAL_KEY AS R_FOCAL_KEY
FROM CREATE_STG STG
INNER JOIN dbo.\""+context.FOCAL_IDFR_TABLE+"\" KDW01
ON STG.FOCAL_IDFR=KDW01.IDFR
INNER JOIN dbo.\""+context.FOCAL_R_IDFR_TABLE+"\" KDW02
ON STG.FOCAL_R_IDFR=KDW02.IDFR
INNER JOIN dbo.\""+context.R_FOCAL_IDFR_TABLE+"\" KDW03
ON STG.R_FOCAL_IDFR=KDW03.IDFR
INNER JOIN DMN_VAL_IDFR KDW04
ON STG.R_TYPE_IDFR=KDW04.IDFR
)
SELECT
GET_KEY.FOCAL_KEY,
GET_KEY.FOCAL_R_KEY,
GET_KEY.R_TYPE_KEY,
GET_KEY.R_FOCAL_KEY,
1 AS DATA_KEY,
1 AS INST_KEY,
1 AS INST_ROW_KEY,
SYSDATETIME() AS POPLN_TMSTP
FROM GET_KEY
LEFT JOIN dbo.\""+context.TGT_REL_TABLE+"\" R
ON GET_KEY.FOCAL_KEY = R.FOCAL_KEY
AND GET_KEY.FOCAL_R_KEY = R.FOCAL_R_KEY
AND GET_KEY.R_TYPE_KEY = R.R_TYPE_KEY
AND GET_KEY.R_FOCAL_KEY = R.R_FOCAL_KEY
WHERE (
(CASE WHEN (( GET_KEY.R_FOCAL_KEY IS NULL AND R.R_FOCAL_KEY IS NULL OR GET_KEY.R_FOCAL_KEY = R.R_FOCAL_KEY)) THEN 1 ELSE 0 END ) = 0
)"

Re: SQL error on DBInput - how to debug?

Anonymous — Tue, 19 Nov 2019 03:18:46 GMT

Hi,

I believe you are not populating value to one of your context variables. One trick will be to print the whole query using tjava and see the output query. It would be also ideal to check the big query part by part so that you can corner the problematic area quickly.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved