I have the following query in Mule 3 and I want to re-write the same in Mule 4. But I am unable to pass the dynamic value properly. I have referred to various online materials and videos on following the syntax but none couldn't help.
Attached below are the queries which I am using in Mule 3 vs Mule 4.
Please let me know if anyone has seen a similar case
Mule 3 Query:
SELECT
DISTINCT CONCAT('${db.host}', '>' ,N.TABLE_CATALOG, '>' ,N.TABLE_SCHEMA, '>' ,N.TABLE_NAME) as FULL_TABLE_NAME,
N.TABLE_CATALOG AS DATABASE_NAME,
N.TABLE_SCHEMA AS SCHEMA_NAME,
N.TABLE_NAME AS TABLE_NAME,
'' AS OBJ_DESCRIPTION,
N.CREATE_DATE AS CREATION_DTTM,
N.MODIFY_DATE AS UPDATE_DTTM
FROM
(SELECT B.TABLE_CATALOG,B.TABLE_SCHEMA,
B.TABLE_NAME,
CASE WHEN B.TABLE_TYPE='BASE TABLE' THEN 'T' ELSE 'V' END TABLE_TYPE,
CASE WHEN C.create_date IS NULL THEN D.create_date ELSE C.create_date END CREATE_DATE,
CASE WHEN C.modify_date IS NULL THEN D.modify_date ELSE C.modify_date END MODIFY_DATE
FROM #flowVars.databaseName.INFORMATION_SCHEMA.TABLES B
LEFT JOIN #flowVars.databaseName.sys.tables C ON C.name=B.TABLE_NAME
LEFT JOIN #flowVars.databaseName.sys.views D ON D.name=B.TABLE_NAME ) N
WHERE N.TABLE_TYPE= 'T' AND N.TABLE_SCHEMA IN (${db.schemas})
Mule 4 Query which I tried:
SELECT
DISTINCT CONCAT('${secure::db.host}', '>' , N.TABLE_CATALOG, '>' , N.TABLE_SCHEMA, '>' , N.TABLE_NAME) as FULL_VIEW_NAME,
N.TABLE_CATALOG AS DATABASE_NAME,
N.TABLE_SCHEMA AS SCHEMA_NAME,
N.TABLE_NAME AS TABLE_NAME,
'' AS OBJ_DESCRIPTION,
N.CREATE_DATE AS CREATION_DTTM,
N.MODIFY_DATE AS UPDATE_DTTM
FROM
(SELECT
B.TABLE_CATALOG,
B.TABLE_SCHEMA,
B.TABLE_NAME,
CASE WHEN B.TABLE_TYPE = 'BASE TABLE' THEN 'T'
ELSE 'V' END TABLE_TYPE,
CASE WHEN C.create_date IS NULL THEN D.create_date
ELSE C.create_date END CREATE_DATE,
CASE WHEN C.modify_date IS NULL THEN D.modify_date
ELSE C.modify_date END MODIFY_DATE
FROM
#vars.databaseName.INFORMATION_SCHEMA.TABLES B
LEFT JOIN #vars.databaseName.sys.tables C ON
C.name = B.TABLE_NAME
LEFT JOIN #vars.databaseName.sys.views D ON
D.name = B.TABLE_NAME) N
WHERE
N.TABLE_TYPE = 'T'
AND N.TABLE_SCHEMA IN (${secure::db.schemas})
Pls use #[] and then add "select * from $(vars.orgTable)" inside dataweave expression.
