<?xml version="1.0" encoding="UTF-8"?>
<rss xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:taxo="http://purl.org/rss/1.0/modules/taxonomy/" version="2.0">
  <channel>
    <title>topic Re: Function returns UNSUPPORTED_CORRELATED_SCALAR_SUBQUERY in Data Engineering</title>
    <link>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5652#M2013</link>
    <description>&lt;P&gt;Dear experts,&lt;/P&gt;&lt;P&gt;&lt;/P&gt;&lt;P&gt;we have not received any answer on this please respond.&lt;/P&gt;&lt;P&gt;&lt;/P&gt;</description>
    <pubDate>Mon, 08 May 2023 10:13:08 GMT</pubDate>
    <dc:creator>TheofilosSt</dc:creator>
    <dc:date>2023-05-08T10:13:08Z</dc:date>
    <item>
      <title>Function returns UNSUPPORTED_CORRELATED_SCALAR_SUBQUERY</title>
      <link>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5649#M2010</link>
      <description>&lt;P&gt;Hello experts. The below function in Databricks gives UNSUPPORTED_CORRELATED_SCALAR_SUBQUERY error. We didn't have this issue though in Oracle. Is this a limitation of Databricks? Just to note the final result returns only one row. Thank you in advance&lt;/P&gt;&lt;P&gt;&lt;/P&gt;&lt;P&gt;create or replace function GET_PREV_DAY(vdate DATE, vdays_bef decimal(38) DEFAULT 1) returns DATE&lt;/P&gt;&lt;P&gt;return&lt;/P&gt;&lt;P&gt;&amp;nbsp;&amp;nbsp;with cte as&lt;/P&gt;&lt;P&gt;&amp;nbsp;&amp;nbsp;(&amp;nbsp;select (t.DATE_FIELD), row_number() over (order by t.DATE_FIELD desc) rn&lt;/P&gt;&lt;P&gt;&amp;nbsp;&amp;nbsp;from time t&lt;/P&gt;&lt;P&gt;&amp;nbsp;&amp;nbsp;where t.DATE_FIELD = (p_date)&lt;/P&gt;&lt;P&gt;&amp;nbsp;&amp;nbsp;and t.WORK_DAYS = 1&lt;/P&gt;&lt;P&gt;&amp;nbsp;&amp;nbsp;) select (cte.DATE_FIELD) as l_result from cte where rn= p_days_before;&lt;/P&gt;</description>
      <pubDate>Tue, 18 Apr 2023 08:04:12 GMT</pubDate>
      <guid>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5649#M2010</guid>
      <dc:creator>elgeo</dc:creator>
      <dc:date>2023-04-18T08:04:12Z</dc:date>
    </item>
    <item>
      <title>Re: Function returns UNSUPPORTED_CORRELATED_SCALAR_SUBQUERY</title>
      <link>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5650#M2011</link>
      <description>&lt;P&gt;@ELENI GEORGOUSI​&amp;nbsp;:&lt;/P&gt;&lt;P&gt;&lt;/P&gt;&lt;P&gt;The UNSUPPORTED_CORRELATED_SCALAR_SUBQUERY error occurs because the subquery in the WHERE clause contains a reference to the main query's column "p_date". This type of subquery is called a correlated scalar subquery and it is not supported in Databricks.&lt;/P&gt;&lt;P&gt;One way to rewrite this query to avoid the error is to use a JOIN or a subquery to filter the rows based on the DATE_FIELD column only, and then use the ROW_NUMBER function to get the row number of the desired row:&lt;/P&gt;&lt;PRE&gt;&lt;CODE&gt;CREATE OR REPLACE FUNCTION GET_PREV_DAY(vdate DATE, vdays_bef decimal(38) DEFAULT 1)
RETURNS DATE
AS
$$
  SELECT DATE_FIELD
  FROM (
    SELECT DATE_FIELD, ROW_NUMBER() OVER (ORDER BY DATE_FIELD DESC) AS rn
    FROM time
    WHERE WORK_DAYS = 1 AND DATE_FIELD &amp;lt; vdate
  ) subquery
  WHERE rn = vdays_bef;
$$&lt;/CODE&gt;&lt;/PRE&gt;&lt;P&gt;In this rewritten query, the subquery filters the rows based on the WORK_DAYS and DATE_FIELD columns only, and then the main query selects the row with the desired row number. This approach should work in Databricks and also in Oracle.&lt;/P&gt;&lt;P&gt;&lt;/P&gt;</description>
      <pubDate>Fri, 21 Apr 2023 02:22:35 GMT</pubDate>
      <guid>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5650#M2011</guid>
      <dc:creator>Anonymous</dc:creator>
      <dc:date>2023-04-21T02:22:35Z</dc:date>
    </item>
    <item>
      <title>Re: Function returns UNSUPPORTED_CORRELATED_SCALAR_SUBQUERY</title>
      <link>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5651#M2012</link>
      <description>&lt;P&gt;Hello, we have changed the function as you mentioned but still the same issue is present.&lt;/P&gt;&lt;P&gt;&lt;/P&gt;&lt;P&gt;&lt;/P&gt;&lt;P&gt;%sql&lt;/P&gt;&lt;P&gt;create or replace function test_get(v_date DATE, days_before decimal(38) DEFAULT 1) returns DATE&lt;/P&gt;&lt;P&gt;return&lt;/P&gt;&lt;P&gt;select (xx) as l_result&amp;nbsp;FROM&lt;/P&gt;&lt;P&gt;(select (t.xx), row_number() over (order by t.xx desc) as rn&lt;/P&gt;&lt;P&gt;from time_table t&lt;/P&gt;&lt;P&gt;where t.xx = (v_date)&lt;/P&gt;&lt;P&gt;and t.DA_WORK_DAYS = 1&lt;/P&gt;&lt;P&gt;) sub&lt;/P&gt;&lt;P&gt;where sub.rn= days_before;&lt;/P&gt;&lt;P&gt;&lt;/P&gt;&lt;P&gt;&lt;/P&gt;&lt;P&gt;&lt;B&gt;&lt;I&gt;analysis exception: Correlated scalar subqueries must be aggregated: Filter (cast(cast(rn#8811 as decimal(10,0)) as decimal(38,0)) = outer(p_days_before#8814))&lt;/I&gt;&lt;/B&gt;&lt;/P&gt;&lt;P&gt;&lt;B&gt;&lt;I&gt;+- SubqueryAlias sub&lt;/I&gt;&lt;/B&gt;&lt;/P&gt;&lt;P&gt;&lt;B&gt;&lt;I&gt; +- Window [xx#8846, row_number() windowspecdefinition(xx#8846 DESC NULLS LAST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS rn#8811], [xx#8846 DESC NULLS LAST]&lt;/I&gt;&lt;/B&gt;&lt;/P&gt;&lt;P&gt;&lt;B&gt;&lt;I&gt; +- Project [xx#8846]&lt;/I&gt;&lt;/B&gt;&lt;/P&gt;&lt;P&gt;&lt;B&gt;&lt;I&gt; +- Filter ((xx#8846 = outer(p_date#8813)) AND (cast(DA_WORK_DAYS#8860 as int) = 1))&lt;/I&gt;&lt;/B&gt;&lt;/P&gt;&lt;P&gt;&lt;B&gt;&lt;I&gt; +- SubqueryAlias t&lt;/I&gt;&lt;/B&gt;&lt;/P&gt;&lt;P&gt;&lt;B&gt;&lt;I&gt; +- SubqueryAlias spark_catalog.&lt;/I&gt;&lt;/B&gt;time_table &lt;/P&gt;&lt;P&gt;&lt;B&gt;&lt;I&gt; +- Relation &lt;/I&gt;&lt;/B&gt;&lt;/P&gt;</description>
      <pubDate>Fri, 21 Apr 2023 10:03:31 GMT</pubDate>
      <guid>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5651#M2012</guid>
      <dc:creator>TheofilosSt</dc:creator>
      <dc:date>2023-04-21T10:03:31Z</dc:date>
    </item>
    <item>
      <title>Re: Function returns UNSUPPORTED_CORRELATED_SCALAR_SUBQUERY</title>
      <link>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5652#M2013</link>
      <description>&lt;P&gt;Dear experts,&lt;/P&gt;&lt;P&gt;&lt;/P&gt;&lt;P&gt;we have not received any answer on this please respond.&lt;/P&gt;&lt;P&gt;&lt;/P&gt;</description>
      <pubDate>Mon, 08 May 2023 10:13:08 GMT</pubDate>
      <guid>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5652#M2013</guid>
      <dc:creator>TheofilosSt</dc:creator>
      <dc:date>2023-05-08T10:13:08Z</dc:date>
    </item>
    <item>
      <title>Re: Function returns UNSUPPORTED_CORRELATED_SCALAR_SUBQUERY</title>
      <link>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5653#M2014</link>
      <description>&lt;P&gt;Hello @Suteja Kanuri​&amp;nbsp; can we have any respond on the above?&lt;/P&gt;&lt;P&gt;Thank you.&lt;/P&gt;</description>
      <pubDate>Tue, 09 May 2023 08:29:49 GMT</pubDate>
      <guid>https://community.databricks.com/t5/data-engineering/function-returns-unsupported-correlated-scalar-subquery/m-p/5653#M2014</guid>
      <dc:creator>TheofilosSt</dc:creator>
      <dc:date>2023-05-09T08:29:49Z</dc:date>
    </item>
  </channel>
</rss>

