Home » SQL & PL/SQL » SQL & PL/SQL » multiple selects after with clauses
multiple selects after with clauses [message #669294] Thu, 12 April 2018 15:00 Go to next message
dinavahi.saradhi@gmail.co
Messages: 8
Registered: December 2008
Junior Member
I have a question.. can we run multiple select statements along with multiple with clauses like below:

WITH v1   
     (   
          toll_id,   
          entry_plaza,   
          exit_plaza,   
          plate,   
          toll_date   
     )   
     AS   
     (   
            SELECT 1,   
                   101,   
                   102,   
                   'MARS01234',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
            UNION ALL   
            SELECT 2,   
                   101,   
                   103,   
                   'MARS9876',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
            UNION ALL   
            SELECT 3,   
                   101,   
                   101,   
                   'MARS7321',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
            UNION ALL   
            SELECT 4,   
                   101,   
                   104,   
                   '2',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
            UNION ALL   
            SELECT 5,   
                   101,   
                   101,   
                   'MARS2234',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
            UNION ALL   
            SELECT 6,   
                   101,   
                   105,   
                   'MARS7667',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
     )   
     ,   
     v2   
     (   
          violation_id,   
          entry_plaza,   
          exit_plaza,   
          plate,   
          toll_date   
     )   
     AS   
     (   
            SELECT 2,   
                   101,   
                   102,   
                   'MARS01234',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
            UNION ALL   
            SELECT 7,   
                   101,   
                   103,   
                   'MARS8747',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
            UNION ALL   
            SELECT 9,   
                   101,   
                   101,   
                   'MARS9842',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
            UNION ALL   
            SELECT 10,   
                   101,   
                   104,   
                   'MARS01244',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
            UNION ALL   
            SELECT 11,   
                   101,   
                   101,   
                   '3',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
            UNION ALL   
            SELECT 12,   
                   101,   
                   105,   
                   'MARS09234',   
                   To_timestamp('21-Mar-17', 'dd-Mon-rr')   
            FROM   dual   
     )   
SELECT   To_char(Trunc(toll_date, 'HH'), 'DD-MON-RR HH24 "Hr"') date_hour,   
         Count(1)   
FROM     (   
                SELECT toll_id,   
                       entry_plaza,   
                       exit_plaza,   
                       plate,   
                       toll_date   
                FROM   v1   
                WHERE  plate NOT IN ( '2',   
                                     '3' )   
                UNION   
                SELECT violation_id toll_id,   
                       entry_plaza,   
                       exit_plaza,   
                       plate,   
                       toll_date   
                FROM   v2   
                WHERE  plate NOT IN ( '2',   
                                     '3' ) )   
GROUP BY Trunc(toll_date, 'HH')   
ORDER BY 1,   
select   to_char(trunc(toll_date, 'HH'), 'DD-MON-RR HH24 "Hr"') date_hour,   
         count(1)   
FROM     (   
                SELECT toll_id,   
                       entry_plaza,   
                       exit_plaza,   
                       plate,   
                       toll_date   
                FROM   v_toll_full   
                WHERE  (   
                              entry_plaza = 101   
                       AND    exit_plaza = 101 )   
                UNION   
                SELECT violation_id toll_id,   
                       entry_plaza,   
                       exit_plaza,   
                       plate,   
                       toll_date   
                FROM   v_violation_full   
                WHERE  (   
                              entry_plaza = 101   
                       AND    exit_plaza = 101 ) )   
GROUP BY trunc(toll_date, 'HH')   
ORDER BY 1  

From the above query, first select query is working fine after "with clauses".
Quote:

But, can we make "multiple select queries to work" after "with clauses". If not, please provide best alternate approach

[Updated on: Thu, 12 April 2018 15:13]

Report message to a moderator

Re: multiple selects after with clauses [message #669295 is a reply to message #669294] Thu, 12 April 2018 15:29 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
We don't have your tables.
We don't have your data.
We don't have your requirements.

What problem are you trying to solve?
How will I know when correct results is posted here?
Which metric measures best?
Re: multiple selects after with clauses [message #669296 is a reply to message #669294] Fri, 13 April 2018 00:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

UNION ALL?

Re: multiple selects after with clauses [message #669297 is a reply to message #669294] Fri, 13 April 2018 00:10 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
You cannot have multiple selects separated by a comma as you have shown, but you can have multiple selects using union all.

-- invalid syntax:
SCOTT@orcl_12.1.0.2.0> with a as (select * from dual)
  2  select * from a,
  3  select * from a
  4  /
select * from a
*
ERROR at line 3:
ORA-00903: invalid table name

-- valid syntax:
SCOTT@orcl_12.1.0.2.0> with a as (select * from dual)
  2  select * from a
  3  union all
  4  select * from a
  5  /

D
-
X
X

2 rows selected.

Previous Topic: group timestamp column hourly
Next Topic: Problem in adding Html code in PL/SQL procedure
Goto Forum:
  


Current Time: Wed Jan 29 03:05:13 CST 2020