Column StatementType not found in QryDetails.

[sandeyshc]

Describe the bug
Got this error while getting last 10 most cpu consumed sessions. Column StatementType not found in QryDetails. But when i see code inner query doesn't have StatementType in QryDetails but in allowed_dimensions its mentioned as allowed why? Please fix it

To Reproduce
Steps to reproduce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Smartphone (please complete the following information):

• Device: [e.g. iPhone6]
• OS: [e.g. iOS8.1]
• Browser [e.g. stock browser, safari]
• Version [e.g. 22]

Additional context
"""
allowed_dimensions = ["LogDate", "hourOfDay", "dayOfWeek", "workloadType", "workloadComplexity","UserName","AppId","StatementType"]
unsupported_dimensions = []
if dimensions is not None:
unsupported_dimensions = [dim for dim in dimensions if dim not in allowed_dimensions]
dimensions = [dim for dim in dimensions if dim in allowed_dimensions]
else:
dimensions=[]

# Update comment string based on dimensions used and supported.
if dimensions:
    comment+="Metrics aggregated by " + ", ".join(dimensions) + "."
if unsupported_dimensions:
    comment+="The following dimensions are not supported and will be ignored: " + ", ".join(unsupported_dimensions) + "."

# Dynamically construct the SELECT and GROUP BY clauses based on dimensions
dim_string = ", ".join(dimensions)
group_by_clause = ("GROUP BY " if dimensions else "")+dim_string
dim_string += ("," if dimensions else "")

filter_clause = ""
filter_clause += f"AND UserName = '{user_name}' " if user_name else ""
filter_clause += f"AND LogDate = '{date}' " if date else ""
filter_clause += f"AND dayOfWeek = '{dayOfWeek}' " if dayOfWeek else ""
filter_clause += f"AND hourOfDay = '{hourOfDay}' " if hourOfDay else ""

query = f"""
SELECT
    {dim_string}
    COUNT(*) AS "Request Count",
    SUM(AMPCPUTime) AS "Total AMPCPUTime",
    SUM(TotalIOCount) AS "Total IOCount",
    SUM(ReqIOKB) AS "Total ReqIOKB",
    SUM(ReqPhysIO) AS "Total ReqPhysIO",
    SUM(ReqPhysIOKB) AS "Total ReqPhysIOKB",
    SUM(SumLogIO_GB) AS "Total ReqIO GB",
    SUM(SumPhysIO_GB) AS "Total ReqPhysIOGB",
    SUM(TotalServerByteCount) AS "Total Server Byte Count"
FROM
    (
        SELECT
            CAST(QryLog.Starttime as DATE) AS LogDate,
            EXTRACT(HOUR FROM StartTime) AS hourOfDay,
            CASE QryCal.day_of_week
                WHEN 1 THEN 'Sunday'
                WHEN 2 THEN 'Monday'
                WHEN 3 THEN 'Tuesday'
                WHEN 4 THEN 'Wednesday'
                WHEN 5 THEN 'Thursday'
                WHEN 6 THEN 'Friday'
                WHEN 7 THEN 'Saturday'
            END AS dayOfWeek,
            QryLog.UserName,
            QryLog.AcctString,
            QryLog.AppID ,
            CASE
                WHEN QryLog.AppID LIKE ANY('TPTLOAD%', 'TPTUPD%', 'FASTLOAD%', 'MULTLOAD%', 'EXECUTOR%', 'JDBCL%') THEN 'LOAD'
                WHEN QryLog.StatementType IN ('Insert', 'Update', 'Delete', 'Create Table', 'Merge Into')
                    AND QryLog.AppID NOT LIKE ANY('TPTLOAD%', 'TPTUPD%', 'FASTLOAD%', 'MULTLOAD%', 'EXECUTOR%', 'JDBCL%') THEN 'ETL/ELT'
                WHEN QryLog.StatementType = 'Select' AND (AppID IN ('TPTEXP', 'FASTEXP') OR AppID LIKE 'JDBCE%') THEN 'EXPORT'
                WHEN QryLog.StatementType = 'Select'
                    AND QryLog.AppID NOT LIKE ANY('TPTLOAD%', 'TPTUPD%', 'FASTLOAD%', 'MULTLOAD%', 'EXECUTOR%', 'JDBCL%') THEN 'QUERY'
                WHEN QryLog.StatementType IN ('Dump Database', 'Unrecognized type', 'Release Lock', 'Collect Statistics') THEN 'ADMIN'
                ELSE 'OTHER'
            END AS workloadType,
            CASE
                WHEN StatementType = 'Merge Into' THEN 'Ingest & Prep'
                WHEN StatementType = 'Select' THEN 'Answers'
                ELSE 'System/Procedural'
            END AS workloadComplexity,
            QryLog.AMPCPUTime,
            QryLog.TotalIOCount,
            QryLog.ReqIOKB,
            QryLog.ReqPhysIO,
            QryLog.ReqPhysIOKB,
            QryLog.TotalServerByteCount,
            (QryLog.ReqIOKB / 1024 / 1024) AS SumLogIO_GB,
            (QryLog.ReqPhysIOKB / 1024 / 1024) AS SumPhysIO_GB
        FROM
            DBC.DBQLogTbl QryLog
            INNER JOIN Sys_Calendar.CALENDAR QryCal
                ON QryCal.calendar_date = CAST(QryLog.Starttime as DATE)
        WHERE
            CAST(QryLog.Starttime as DATE) BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE
            AND StartTime IS NOT NULL
            {filter_clause}
    ) AS QryDetails
    {group_by_clause}
"""

"""

[dtehan-td]

@sandeyshc in dba_tools.py file, line 231 gives a list of allowed dimensions, however in lines 219 and 251 - 255 that build up the filter_clause we have not covered "workloadType", "workloadComplexity", "AppId", "StatementType"

The simple fix is for us to remove the filter_clauses not currently supported.

A more complicated fix would be to add those fields to the function arguments and filter condition.

@remi-td you might want to weigh in on this as well.