Use single and double quotes within double quotes

[akvsachin]

Hi Folks,

I'm trying to execute an SQL Query by connecting Formula Tool -> Dynamic Input Tool 

[Query] = "

WITH CTE AS
(
SELECT
TO_CHAR(Balance_Date, 'MM/DD/YYYY') AS "Balance Date",
Transaction_Description AS "Transaction Description",
Client_Reference_Num AS "Client Reference Num"
FROM Head_Account IN (567890)
WHERE Balance_Date BETWEEN TO_DATE('10/01/2024','MM/DD/YYYY')
AND TO_DATE('10/30/2025','MM/DD/YYYY')
)

"

But this is not working.
Another try was like this

[Query] = "

WITH CTE AS
(
SELECT
TO_CHAR(Balance_Date, 'MM/DD/YYYY') AS 'Balance Date',
Transaction_Description AS 'Transaction Description',
Client_Reference_Num AS 'Client Reference Num'
FROM Head_Account IN (" + [Account] +")
WHERE Balance_Date BETWEEN TO_DATE(' " + [Date From] + "','MM/DD/YYYY')
AND TO_DATE('" + [Date To] +"','MM/DD/YYYY')
)"

Still it's errors.

[aatalai]

@akvsachin to confirm is the error in the formula tool or in the dynamic input tool?

[akvsachin]

The SQL Query works perfectly in a simple Input Data Tool. After Quoting it, it's a Parse Error in Formula Tool when the query is entered.

Second one passes formula tool, but I do get an error in Dynamic Input Tool.

[binu_acs]

@akvsachin is your SQL syntax correct? FROM Head_Account IN (" + [Account] +") is not a valid SQL. can you try the below query

WITH CTE AS
(
    SELECT
        TO_CHAR(Balance_Date, 'MM/DD/YYYY') AS "Balance_Date", -- Removed space to avoid quote issues
        Transaction_Description AS "Transaction_Description",
        Client_Reference_Num AS "Client_Reference_Num"
    FROM Head_Account
    WHERE Account_ID IN (567890) -- Added WHERE and Column Name
    AND Balance_Date BETWEEN TO_DATE('10/01/2024','MM/DD/YYYY')
    AND TO_DATE('10/30/2025','MM/DD/YYYY')
)
SELECT * FROM CTE -- You must select from the CTE to get results

[akvsachin]

Hey @binu_acs 

Below is a sample input data file that I’ll be passing through the Formula Tool and then into the Dynamic Input Tool. Each record will generate its own SQL query based on these fields:

Account	Date From	Date To
256398	12-01-2024	31-10-2025
124565	12-01-2024	31-10-2025
145236	12-01-2024	31-10-2025

In the Formula Tool, I will be creating a field named [Query], which should dynamically construct the SQL statement. The Dynamic Input Tool will then replace its SQL with this generated query so that it runs three different queries—one for each Account ID.

Below is the base SQL query that works correctly when executed directly in the Input Data Tool:

WITH CTE AS
(
SELECT
TO_CHAR(Balance_Date, 'MM/DD/YYYY') AS "Balance_Date",
Transaction_Description AS "Transaction_Description",
Client_Reference_Num AS "Client_Reference_Num"
FROM HCP.ACH_CDHDATE
WHERE Account_ID IN (256398)
AND Balance_Date BETWEEN TO_DATE('10/01/2024','MM/DD/YYYY')
AND TO_DATE('10/30/2025','MM/DD/YYYY')
)

I’m trying to generate this same query dynamically using the values from the input file, so that each row triggers its own query with the correct Account, Date From, and Date To values.

I hope this clarifies the requirement.

[binu_acs]

@akvsachin Try the below formula

"WITH CTE AS (
    SELECT 
        TO_CHAR(Balance_Date, 'MM/DD/YYYY') AS Balance_Date,
        Transaction_Description AS Transaction_Description,
        Client_Reference_Num AS Client_Reference_Num
    FROM HCP.ACH_CDHDATE
    WHERE Account_ID IN (" + ToString([Account]) + ")
      AND Balance_Date BETWEEN TO_DATE('" + [Date From] + "', 'MM/DD/YYYY')
                           AND TO_DATE('" + [Date To]   + "', 'MM/DD/YYYY')
)
"

[akvsachin]

Hey @binu_acs @aatalai @alexnajm  , 

Let me tell you the complete situation that I'm trying to achieve.

The 3 columns in input data file are going to be dynamic, i.e., Account Number, Date From and Date To.

Below is the complete SQL Code that I'll have to use to retrieve the data from server.

Through Dynamic Input, Modify SQL Query case I can update WHERE Clause : HEAD_ACCOUNT IN (4567890) while Text to Replace 4567890 as Integer.
I'm having trouble on how to replace Dates here.

WITH CTE AS
(
    SELECT
        TO_CHAR(Balance_Date, 'MM/DD/YYYY')     AS "Balance Date",
        TO_CHAR(Value_Date, 'MM/DD/YYYY')       AS "Value Date",
        Head_Account_Name                       AS "Main Account",
        Account_Number                          AS "Within Account",
        Currency_Code                           AS "Currency Code",
        Currency_Account_Name                   AS "Currency Account Name",
        TO_CHAR(Transaction_Amount, '999G999G999G999G999G999G999.99')
                                               AS "Transaction Amount",
        Debit_Credit_Indicator                  AS "Debit/Credit Indicator",
        Transaction_Category                    AS "Transaction Category",
        Transaction_Description                 AS "Transaction Description",
        Client_Reference_Num                    AS "Client Reference Num"
    FROM MAPS_REFERENCE_MAIN
    WHERE HEAD_ACCOUNT IN (4567890) 
	  AND Balance_Date BETWEEN TO_DATE('10/01/2024','MM/DD/YYYY')
                           AND TO_DATE('10/30/2025','MM/DD/YYYY')
      AND (Perf_Security_Type <> 0 OR  Perf_Security_Type = 2)
    ORDER BY balance_date, head_account_number, account_number
)

SELECT *
FROM (
        SELECT DISTINCT *
        FROM CTE
        ORDER BY TO_DATE("Balance Date", 'MM/DD/YYYY'),
                 "Main Account",
                 "Currency Code"
     )

UNION ALL

(SELECT
    NULL AS "Balance Date",
    NULL AS "Value Date",
    NULL AS "Main Account",
    NULL AS "Within Account",
    NULL AS "Currency Code",
    NULL AS "Currency Account Name",
    NULL AS "Transaction Amount",
    NULL AS "Debit/Credit Indicator",
    NULL AS "Transaction Category",
    NULL AS "Transaction Description",
    NULL AS "Client Reference Num"
FROM DUAL
WHERE NOT EXISTS ( SELECT 1 FROM CTE ))

Please let me know if you have any another solution for this case. 

[binu_acs]

@akvsachin try the below sql in the formula tool

"WITH CTE AS
(
    SELECT
        TO_CHAR(Balance_Date, 'MM/DD/YYYY')     AS ""Balance Date"",
        TO_CHAR(Value_Date, 'MM/DD/YYYY')       AS ""Value Date"",
        Head_Account_Name                       AS ""Main Account"",
        Account_Number                          AS ""Within Account"",
        Currency_Code                           AS ""Currency Code"",
        Currency_Account_Name                   AS ""Currency Account Name"",
        TO_CHAR(Transaction_Amount, '999G999G999G999G999G999G999.99')
                                               AS ""Transaction Amount"",
        Debit_Credit_Indicator                  AS ""Debit/Credit Indicator"",
        Transaction_Category                    AS ""Transaction Category"",
        Transaction_Description                 AS ""Transaction Description"",
        Client_Reference_Num                    AS ""Client Reference Num""
    FROM MAPS_REFERENCE_MAIN
    WHERE HEAD_ACCOUNT IN (" + ToString([Account]) + ") 
      AND Balance_Date BETWEEN TO_DATE('" + [Date From] + "','MM/DD/YYYY')
                           AND TO_DATE('" + [Date To] + "','MM/DD/YYYY')
      AND (Perf_Security_Type <> 0 OR  Perf_Security_Type = 2)
    ORDER BY balance_date, head_account_number, account_number
)

SELECT *
FROM (
        SELECT DISTINCT *
        FROM CTE
        ORDER BY TO_DATE(""Balance Date"", 'MM/DD/YYYY'),
                 ""Main Account"",
                 ""Currency Code""
     )

UNION ALL

(SELECT
    NULL AS ""Balance Date"",
    NULL AS ""Value Date"",
    NULL AS ""Main Account"",
    NULL AS ""Within Account"",
    NULL AS ""Currency Code"",
    NULL AS ""Currency Account Name"",
    NULL AS ""Transaction Amount"",
    NULL AS ""Debit/Credit Indicator"",
    NULL AS ""Transaction Category"",
    NULL AS ""Transaction Description"",
    NULL AS ""Client Reference Num""
FROM DUAL
WHERE NOT EXISTS ( SELECT 1 FROM CTE ))"

[akvsachin]

Thanks for the reply @binu_acs 
I've already tried this... Did not work ... Parse Error.

"WITH CTE AS
(
    SELECT
        TO_CHAR(Balance_Date, 'MM/DD/YYYY')     AS "

Only this part gets parsed.

[binu_acs]

@akvsachin 

"WITH CTE AS
(
    SELECT
        TO_CHAR(Balance_Date,"+" 'MM/DD/YYYY') AS "+ '"Balance Date",'+
        "TO_CHAR(Value_Date, 'MM/DD/YYYY') AS "+'"Value Date",'+
        "Head_Account_Name AS "+'"Main Account",'+
        "Account_Number AS  "+'"Within Account",'+
        "Currency_Code AS  "+'"Currency Code",'+
        "Currency_Account_Name AS  "+'"Currency Account Name",'+
        "TO_CHAR(Transaction_Amount, '999G999G999G999G999G999G999.99') AS  "+'"Transaction Amount",'+
        "Debit_Credit_Indicator AS  "+'"Debit/Credit Indicator",'+
        "Transaction_Category AS  "+'"Transaction Category",'+
        "Transaction_Description AS  "+'"Transaction Description",'+
        "Client_Reference_Num  AS  "+'"Client Reference Num"'+
    " FROM MAPS_REFERENCE_MAIN
  WHERE HEAD_ACCOUNT IN (" + ToString([Account]) + ") 
      AND Balance_Date BETWEEN TO_DATE('" + [Date From] + "','MM/DD/YYYY')"+
                           " AND TO_DATE('" + [Date To] + "','MM/DD/YYYY')"+
      " AND (Perf_Security_Type <> 0 OR  Perf_Security_Type = 2)
    ORDER BY balance_date, head_account_number, account_number
)

SELECT *
FROM (
        SELECT DISTINCT *
        FROM CTE
        ORDER BY TO_DATE(" +'"Balance Date",'+ "'MM/DD/YYYY'),"+
                 '"Main Account",'+
                 '"Currency Code"'+
    " )

UNION ALL

(SELECT"+
    "NULL AS   "+'"Balance Date",'+
    "NULL AS   "+'"Value Date",'+
    "NULL AS   "+'"Main Account",'+
    "NULL AS   "+'"Within Account",'+
    "NULL AS   "+'"Currency Code",'+
    "NULL AS   "+'"Currency Account Name",'+
    "NULL AS   "+'"Transaction Amount",'+
    "NULL AS   "+'"Debit/Credit Indicator",'+
    "NULL AS   "+'"Transaction Category",'+
    "NULL AS   "+'"Transaction Description",'+
    "NULL AS   "+'"Client Reference Num"'+
"FROM DUAL
WHERE NOT EXISTS ( SELECT 1 FROM CTE ))"