Solved

please fix an oracle query

Posted on 2014-01-31
5
316 Views
Last Modified: 2014-02-06
Hi,

I can't run the following SQL, could you please fix this

thanks
-anshu


sql error--------------------------------------------

SELECT mt1.em_user_abbrev,
       rp2.is_rep_name,
       ur3.em_user_abbrev,
       SUM(EXEC)
FROM   (SELECT c.em_user_abbrev,
               a.em_user_id,
               c.is_rep_id,
               Count(DISTINCT a.is_doc_job_id) EXEC
        FROM   mstr_em_wh_stats.is_doc_fact a
               join mstr_em_wh_stats.is_rep_doc b
                 ON ( a.is_doc_id = b.is_doc_id )
               join (SELECT is_rep_id,
                            em_user_abbrev
                     FROM   mstr_em_wh_stats.is_rep_fact
                            join mstr_em_wh_stats.em_user
                              ON ( is_rep_fact.em_user_id = em_user.em_user_id )
                     WHERE  day_id between'20130828'and'20131028'
                            AND (Lower(em_user_abbrev) LIKE'%mstr%'OR
                                 Lower(em_user_abbrev) = 'administrator')
                     GROUP  BY is_rep_id,
                               em_user_abbrev) c
                 ON ( b.is_rep_id = c.is_rep_id )
        WHERE  a.day_id between'20130828'and'20131028'
        GROUP  BY c.em_user_abbrev,
                  a.em_user_id,
                  c.is_rep_id
        UNIONALL
        SELECT c.em_user_abbrev,
               a.em_user_id,
               c.is_rep_id,
               Count(DISTINCT a.is_rep_job_id) EXEC
        FROM   mstr_em_wh_stats.is_rep_fact a
               join mstr_em_wh_stats.is_rep_rep b
                 ON ( a.is_rep_id = b.is_rep_parent_id )
               join (SELECT is_rep_id,
                            em_user_abbrev
                     FROM   mstr_em_wh_stats.is_rep_fact
                            join mstr_em_wh_stats.em_user
                              ON ( is_rep_fact.em_user_id = em_user.em_user_id )
                     WHERE  day_id between'20130828'and'20131028'
                            AND (Lower(em_user_abbrev) LIKE'%mstr%'OR
                                 Lower(em_user_abbrev) = 'administrator')
                     GROUP  BY is_rep_id,
                               em_user_abbrev) c
                 ON ( c.is_rep_id = b.is_rep_child_id )
        WHERE  a.day_id between'20130828'and'20131028'
        GROUP  BY c.em_user_abbrev,
                  a.em_user_id,
                  c.is_rep_id) mt1
       join mstr_em_wh_stats.is_rep rp2
         ON ( mt1.is_rep_id = rp2.is_rep_id )
       join mstr_em_wh_stats.em_user ur3
         ON ( mt1.em_user_id = ur3.em_user_id )
GROUP  BY mt1.em_user_abbrev,
          rp2.is_rep_name,
          ur3.em_user_abbrev
0
Comment
Question by:anshuma
  • 3
5 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39825713
Sorry for the previous post... I can reproduce the error.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 39825714
Change: UNIONALL
to: UNION ALL
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39826004
Put your query in an editor like TOAD, SQL Developer, Notepad++ - they will show you missing parentheses :)
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 39826509
The unionall (on line 27) without a space certainly jumped out as a problem as slightwv pointed out. However, I tried to replicate the error and got a different error message. Are you still getting an error after changing the unionall to union all?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39827025
When I changed to 'union all' I get the 942 table or view does not exist.  That told me the syntax was correct.

I didn't take the time to create the columns and tables involved.

What error did you get?

>> they will show you missing parentheses :)

It isn't a missing parenthesis. Did you load it up into one of your suggested tools to locate it?
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question