Fatal error when trying to save dataset

[MikeDel001]

Apologies if this is already posted, i did search and found similar issues but they relate to queries with Jinja.

I have an SQL script but when i try to save the dataset i get a Fatal Error. no description to the error and i was hoping someone could shed a little light on the issue?

This is my script:
`SELECT

CAST(CAST((SUM(y.workhours)) AS int) / 60 AS varchar) + ' hours ' + right('0' + CAST(CAST((SUM(y.workhours)) AS int) % 60 AS varchar(2)), 2) + ' mins' AS availabletime,
CAST(CAST((SUM(y.totalfreetime)) AS int) / 60 AS varchar) + ' hours ' + right('0' + CAST(CAST((SUM(y.totalfreetime)) AS int) % 60 AS varchar(2)), 2) + ' mins' AS freetime,
CAST(CAST((SUM(y.totalworktime)) AS int) / 60 AS varchar) + ' hours ' + right('0' + CAST(CAST((SUM(y.totalworktime)) AS int) % 60 AS varchar(2)), 2) + ' mins' AS worktime,
y.description

FROM (
SELECT
eds.engineerid,
eds.day as workday,
CASE
WHEN pa.allday = 1 THEN 0
WHEN (CAST(eds.day AS DATE) BETWEEN CAST(pa.startdate AS DATE) and CAST(pa.enddate AS DATE)) THEN 0
ELSE DATEDIFF(MINUTE, pwh.starttime, pwh.finishtime)
END AS workhours,
--eds.travelhometime,
CASE
WHEN pa.allday = 1 THEN 0
WHEN (CAST(eds.day AS DATE) BETWEEN CAST(pa.startdate AS DATE) and CAST(pa.enddate AS DATE)) THEN 0
ELSE ISNULL(eds.totaljobtime, 0)
END AS totaljobtime,
CASE
WHEN pa.allday = 1 THEN 0
WHEN (CAST(eds.day AS DATE) BETWEEN CAST(pa.startdate AS DATE) and CAST(pa.enddate AS DATE)) THEN 0
ELSE ISNULL(eds.totaltraveltime, 0)
END AS totaltraveltime,
CASE
WHEN pa.allday = 1 THEN 0
WHEN (CAST(eds.day AS DATE) BETWEEN CAST(pa.startdate AS DATE) and CAST(pa.enddate AS DATE)) THEN 0
ELSE ISNULL(eds.travelhometime, 0)
END AS totaltravelhometime,
CASE
WHEN pa.allday = 1 THEN 0
WHEN (CAST(eds.day AS DATE) BETWEEN CAST(pa.startdate AS DATE) and CAST(pa.enddate AS DATE)) THEN 0
ELSE ISNULL(eds.totaljobtime, 0) + ISNULL(eds.totaltraveltime, 0) + ISNULL(eds.travelhometime, 0)
END AS totalworktime,
CASE
WHEN pa.allday = 1 THEN 0
WHEN (CAST(eds.day AS DATE) BETWEEN CAST(pa.startdate AS DATE) and CAST(pa.enddate AS DATE)) THEN 0
ELSE DATEDIFF(MINUTE, pwh.starttime, pwh.finishtime) - ISNULL(DATEDIFF(MINUTE, pa.startdate, pa.enddate), 0) - ISNULL(eds.totaljobtime, 0) - ISNULL(eds.totaltraveltime, 0) - ISNULL(eds.travelhometime, 0)
END AS totalfreetime,
DATENAME(weekday, eds.day) as dayname,
CASE
WHEN pa.allday = 1 THEN '0'
ELSE ISNULL(DATEDIFF(MINUTE, pa.startdate, pa.enddate), 0)
END AS absence,
ci.description,
ci.calendarid,
ci.itemnumber
FROM engineerdaysummaries eds
INNER JOIN personworkinghours pwh
ON eds.engineerid = pwh.personid
AND pwh.dayname = 'MONDAY'
AND pwh.personid = eds.engineerid
INNER JOIN engineers e
ON e.engineerid = pwh.personid
AND e.vehicleid IS NOT NULL
AND e.vehicleid NOT IN ('21', '22', '24', '23')
INNER JOIN calendaritems ci
ON eds.day BETWEEN ci.startdate AND ci.enddate
LEFT JOIN efacsmaster..personabsences pa
ON pa.personid = pwh.personid
AND (CAST(eds.day AS DATE) BETWEEN CAST(pa.startdate AS DATE) and CAST(pa.enddate AS DATE))) AS y
WHERE
y.dayname NOT IN ('Saturday', 'Sunday')
GROUP BY
y.description
`

Thanks in advance for your assistance.

[dosubot[bot]]

The fatal error you're encountering when trying to save the dataset in Apache Superset could be related to several factors, including SQL syntax issues, unsupported functions, or configuration settings in Superset. Here are a few things to consider:

1. Complexity and Execution Time: If the query is too complex or takes too long to execute, it might exceed the allowed execution time, leading to a SQLLAB_TIMEOUT_ERROR ^[1][2]. Consider simplifying the query or optimizing it to reduce execution time.

2. Result Size: If the result size of your query exceeds the allowed limit, it could cause an error. Check the SQL_MAX_ROW configuration in Superset, which limits the number of rows a query can return ^[3][4].

3. Data Type Conversions: Ensure that the data type conversions in your SQL script are compatible with the database you are using. Incompatible conversions might lead to errors during execution.

4. Error Handling and Logging: Superset uses a function called handle_query_error to manage errors during SQL execution. Check the logs for any error messages that might provide more details about the issue ^[4][5].

5. Unsupported SQL Syntax: Ensure that the SQL syntax and functions used in your script are supported by the SQL dialect configured in Superset. Unsupported syntax could lead to errors.

Review these aspects of your query and configuration to identify potential issues. If the problem persists, consider breaking down the query into smaller parts to isolate the cause of the error.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[MikeDel001]

Sorry, i forgot to mention that i can run the query fine in the SQL Lab but just cannot save it.

[sabikahmukhi]

try removing any -- comments . worked for me