[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements

[GitBox <gi...@apache.org>]

villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-377607727
 
 
   @sangramdhal102 I'm surprised the query runs, as you seem to have mixed ' and ’. Example:
   ```soapgatewayservice_received >= 'startdate’```. I believe that's what is confusing sqlparse. I'm not an expert in Postgres, but I would perhaps refactor the query to make it slightly easier to read. Something along these lines:
   
   ```sql
   with total_tasks_per_project as (
   select 
     extract('hour' from soapgatewayservice_received) as hour,
     modality,
     case lower(ingestion_status) when 'completed' then 1 else null end as completed,
     case lower(ingestion_status) when 'failed' then 1 else null end as failed,
     case lower(ingestion_status) when 'inprocess' then 1 else null end as inprocess,
     ingestioncheckpointservice_completed - soapgatewayservice_received as ingestion_time
   from
     provenance.provenancedata 
   where
     soapgatewayservice_received >= 'startdate'
     and soapgatewayservice_received <= 'enddate'
     and modality in ('CV','DC','MR','ICAP')
   group by
     modality, 
     hour 
   union
   select
     extract('hour' from restingestiongatewayservice_received) as hour,
     modality,
     case lower(ingestion_status) when 'completed' then 1 else null end as completed,
     case lower(ingestion_status) when 'failed' then 1 else null end as failed,
     case lower(ingestion_status) when 'inprocess' then 1 else null end as inprocess,
     ingestioncheckpointservice_completed - restingestiongatewayservice_received as ingestion_time
   from
     provenance.provenancedata
   where 
     restingestiongatewayservice_received >= 'startdate'
     and restingestiongatewayservice_received <= 'enddate'
     and modality = 'CT'
   group by
     modality, 
     hour
   )
   select 
     hour,
     modality,
     count(*) as total,
     count(completed) as completed,
     count(failed) as failed,
     count(inprocess) as inprocess,
     avg(ingestion_time) as avg_ingestion_time
   from 
     total_tasks_per_project
   group by
     hour,
     modality
   order by
     hour
   ```
   

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services