how to apply 2 with clauses

Analytics
Highlighted
Enthusiast

how to apply 2 with clauses

i have below queries which is working fine. but for the ID value, currently, it is assigned by hardcoding (wheresubstr(ID,0,8) in ('363762K')), this is not make sense for us, since we want to let the ID value generating from another queries like below one:

 

select distinct id from table 2
    where RUNCOMPLETEDATETIME>='2018-03-15 00:00:00'
    and RUNCOMPLETEDATETIME<='2018-03-15 00:00:10'

 

then the question is how we can put the outputs of above queries into below queries as values of 'ID'? do I need to add one more 'with' clause? thanks

 

with v1 as
  (select distinct a.ID, a.STEPNAME, a.RUNCOMPLETEDATETIME from
    (SELECT ID, MFGPROCESSSTEP as STEPNAME, RUNCOMPLETEDATETIME FROM table1
      wheresubstr(ID,0,8) in ('363762K')) a)
  
SELECT  id, STEPNAME,
   Sum(flag)
   Over (PARTITION BY substr(ID,0,8)ORDER BY  RUNCOMPLETEDATETIME
         ROWS Unbounded Preceding) +1 as OrderNO
FROM
 (
   SELECT  id, STEPNAME, RUNCOMPLETEDATETIME,
      CASE WHEN Min(CASE WHEN stepname LIKE any ('3010%','3001%','3000%','3120%') THEN stepname end)
                Over (PARTITION BY substr(ID,0,8)ORDER BY  RUNCOMPLETEDATETIME
                      ROWS 1 Preceding ) IS NOT NULL
           THEN 1
           ELSE 0
      END AS flag
   FROM v1
 ) AS dt
 order by hashrow(stepname);

Accepted Solutions
Senior Apprentice

Re: how to apply 2 with clauses

Hi,

 

This shouldn't be a problem, this is a standard case for using a sub-query.

Take your first piece of code...

with v1 as
  (select distinct a.ID, a.STEPNAME, a.RUNCOMPLETEDATETIME from
    (SELECT ID, MFGPROCESSSTEP as STEPNAME, RUNCOMPLETEDATETIME FROM table1
      where substr(ID,0,8) in ('363762K')) a)

...and change it to...

with v1 as
  (select distinct a.ID, a.STEPNAME, a.RUNCOMPLETEDATETIME from
    (SELECT ID, MFGPROCESSSTEP as STEPNAME, RUNCOMPLETEDATETIME FROM table1
      where substr(ID,0,8) in (select distinct id from table 2
                              where RUNCOMPLETEDATETIME>='2018-03-15 00:00:00'
                              and RUNCOMPLETEDATETIME<='2018-03-15 00:00:10')) a)

HTH

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
1 REPLY
Senior Apprentice

Re: how to apply 2 with clauses

Hi,

 

This shouldn't be a problem, this is a standard case for using a sub-query.

Take your first piece of code...

with v1 as
  (select distinct a.ID, a.STEPNAME, a.RUNCOMPLETEDATETIME from
    (SELECT ID, MFGPROCESSSTEP as STEPNAME, RUNCOMPLETEDATETIME FROM table1
      where substr(ID,0,8) in ('363762K')) a)

...and change it to...

with v1 as
  (select distinct a.ID, a.STEPNAME, a.RUNCOMPLETEDATETIME from
    (SELECT ID, MFGPROCESSSTEP as STEPNAME, RUNCOMPLETEDATETIME FROM table1
      where substr(ID,0,8) in (select distinct id from table 2
                              where RUNCOMPLETEDATETIME>='2018-03-15 00:00:00'
                              and RUNCOMPLETEDATETIME<='2018-03-15 00:00:10')) a)

HTH

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com