Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2011
    Posts
    17

    Unanswered: Question regarding SUBQUERY and LEFT OUTER JOIN.

    Hi again,

    Am a bit unsure as to wether my code is syntactically correct and if the problem is elsewhere or if I simply have the syntax muddled up..

    Here goes.

    Code:
    CREATE OR REPLACE VIEW
      SALES_OVER_30000_WITH_TOTAL
    AS
      SELECT
        E.FIRST_NAME || ' ' || E.LAST_NAME AS EMPLOYEE_NAME,
        SUM(SO.TOTAL) AS TOTAL_SOLD
      FROM
        EMPLOYEE E,  
      (SELECT 
        SUM(SO.TOTAL) AS SUM_SO_TOTAL
      FROM 
        SALES_ORDER SO
      ) MAXRESULTS
      LEFT OUTER JOIN 
        CUSTOMER C 
      ON
        C.SALESPERSON_ID = E.EMPLOYEE_ID
      LEFT OUTER JOIN 
        SALES_ORDER SO 
      ON
        SO.CUSTOMER_ID = C.CUSTOMER_ID
      WHERE MAXRESULTS.SUM_SO_TOTAL > 30000
      GROUP BY EMPLOYEE_NAME
    
       
     
    ;
    SELECT
      EMPLOYEE_NAME,
      TOTAL_SOLD
    FROM
      SALES_OVER_30000_WITH_TOTAL
    ORDER BY MAKE 
    ;

    The current error it is giving is this.

    Code:
    Error at Command Line:17 Column:23
    Error report:
    SQL Error: ORA-00904: "E"."EMPLOYEE_ID": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    I cannot for the life of me seem to work out why this error is occuring or even find an example of what I am trying to do online.

    Any help is greatly appreciated,

    Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post DDL for all tables involved in query
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2011
    Posts
    17
    Ok here is the create tables part.

    Code:
    DROP VIEW SALES;
    DROP TABLE ITEM;
    DROP TABLE SALES_ORDER;
    DROP TABLE CUSTOMER;
    DROP TABLE PRICE;
    DROP TABLE PRODUCT;
    DROP TABLE SALARY_GRADE;
    DROP TABLE EMPLOYEE;
    DROP TABLE JOB;
    DROP TABLE DEPARTMENT;
    DROP TABLE LOCATION;
    
    
    REM Create new datasources
    
    CREATE TABLE LOCATION (
            LOCATION_ID             NUMBER(3),
            REGIONAL_GROUP          VARCHAR2(20));
    
    CREATE TABLE DEPARTMENT (
            DEPARTMENT_ID           NUMBER(2),
            NAME                    VARCHAR2(14),
            LOCATION_ID             NUMBER(3));
    
    CREATE TABLE JOB (
            JOB_ID                  NUMBER(3),
            FUNCTION                VARCHAR2(30));
    
    CREATE TABLE EMPLOYEE (
            EMPLOYEE_ID             NUMBER(4),
            LAST_NAME               VARCHAR2(15),
            FIRST_NAME              VARCHAR2(15),
            MIDDLE_INITIAL          VARCHAR2(1),
            JOB_ID                  NUMBER(3),
            MANAGER_ID              NUMBER(4),
            HIRE_DATE               DATE,
            SALARY                  NUMBER(7,2),
            COMMISSION              NUMBER(7,2),
            DEPARTMENT_ID           NUMBER(2));
    
    CREATE TABLE SALARY_GRADE (
            GRADE_ID                NUMBER(3),
            LOWER_BOUND             NUMBER(7,2),
            UPPER_BOUND             NUMBER(7,2));
    
    CREATE TABLE PRODUCT (
            PRODUCT_ID              NUMBER (6),
            DESCRIPTION             VARCHAR2 (30));
     
    CREATE TABLE PRICE (
            PRODUCT_ID              NUMBER (6),
            LIST_PRICE              NUMBER (8,2),
            MIN_PRICE               NUMBER (8,2),
            START_DATE              DATE,
            END_DATE                DATE);
    
    CREATE TABLE CUSTOMER (
            CUSTOMER_ID             NUMBER (6),
            NAME                    VARCHAR2 (45),
            ADDRESS                 VARCHAR2 (40),
            CITY                    VARCHAR2 (30),
            STATE                   VARCHAR2 (2),
            ZIP_CODE                VARCHAR2 (9),
            AREA_CODE               NUMBER (3),
            PHONE_NUMBER            NUMBER (7),
            SALESPERSON_ID          NUMBER (4),
            CREDIT_LIMIT            NUMBER (9,2));
    
    CREATE TABLE SALES_ORDER  (
            ORDER_ID                NUMBER (4),
            ORDER_DATE              DATE,
            CUSTOMER_ID             NUMBER (6),
            SHIP_DATE               DATE,
            TOTAL                   NUMBER (8,2));
    
    CREATE TABLE ITEM  (
            ORDER_ID                NUMBER (4),
            ITEM_ID                 NUMBER (4),
            PRODUCT_ID              NUMBER (6),
            ACTUAL_PRICE            NUMBER (8,2),
            QUANTITY                NUMBER (8),
            TOTAL                   NUMBER (8,2));


    All the INSERT INTO stuff is below, let me know if you need any more information.

    Thanks in advance

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post results of follow SQL - SELECT * FROM V$VERSION
    I am "old school" & ANSI joins can easily confuse me & it appears Oracle may be confused, too.
    I am too lazy to test so I ask what happens when you change the order of tables in FROM clause?
    In other words to which table does the LEFT OUTER JOIN apply?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2011
    Posts
    17
    Ok results of V$VERSION...

    Code:
    BANNER                                                           
    ---------------------------------------------------------------- 
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 
    PL/SQL Release 10.2.0.1.0 - Production                           
    CORE	10.2.0.1.0	Production                                         
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production          
    NLSRTL Version 10.2.0.1.0 - Production

    Have tried to change around table names and get similar error..

    Code:
    Error at Command Line:10 Column:8
    Error report:
    SQL Error: ORA-00904: "SO"."TOTAL": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    Thanks for your replies

  6. #6
    Join Date
    Apr 2011
    Posts
    9
    You've mixed implicit and explicit join types which can be done but needs careful coding.

    As it stands MAXRESULTS is joined to CUSTOMER but the join predicate references EMPLOYEE?

    So MAXRESULTS is not joined to anything else so at the moment it is only there to cause the view to return no rows if everyone's cumulative sales are <= 30000? But I suspect this isn't what you want and you only want salesperson's who have sold > 30000?

    You've also used the same label (SO) twice which Oracle can handle as long as they are in different scopes but confuses the reader :-)

    Try this (I haven't tested it by the way)

    Code:
    CREATE OR REPLACE VIEW
      SALES_OVER_30000_WITH_TOTAL
    AS
      SELECT
        E.FIRST_NAME || ' ' || E.LAST_NAME AS EMPLOYEE_NAME,
        SUM(SO.TOTAL) AS TOTAL_SOLD
      FROM
        EMPLOYEE E
      LEFT OUTER JOIN 
        CUSTOMER C 
      ON
        C.SALESPERSON_ID = E.EMPLOYEE_ID
      LEFT OUTER JOIN 
        SALES_ORDER SO 
      ON
        SO.CUSTOMER_ID = C.CUSTOMER_ID
      GROUP BY EMPLOYEE_NAME
      HAVING SUM(SO.TOTAL) > 30000
    But those left outer joins are then negated by the presence of the > 30000 condition so they effectively become INNER JOINs.

    Mark.

  7. #7
    Join Date
    Jun 2011
    Posts
    17
    Ok yeah that makes sense.

    Just tried your code and am getting a similar error to before:

    Code:
    Error at Command Line:17 Column:11
    Error report:
    SQL Error: ORA-00904: "EMPLOYEE_NAME": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    Line 17 Column 11:
    Code:
    GROUP BY EMPLOYEE_NAME

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    May I try?
    Code:
    create or replace view sales_over_30000_with_total
    as
       select employee_name, total_sold
         from (select e.first_name || ' ' || e.last_name as employee_name,
                      (select sum (so.total)
                         from sales_order so)
                         total_sold
                 from employee e
                      left outer join customer c
                         on c.salesperson_id = e.employee_id
                      left outer join sales_order so
                         on so.customer_id = c.customer_id)
        where total_sold > 30000

  9. #9
    Join Date
    Jun 2011
    Posts
    17
    Ok yeah that makes sense.

    Just tried your code and am getting a similar error to before:

    Code:
    Error at Command Line:17 Column:11
    Error report:
    SQL Error: ORA-00904: "EMPLOYEE_NAME": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    Line 17 Column 11:
    Code:
    GROUP BY EMPLOYEE_NAME

  10. #10
    Join Date
    Jun 2011
    Posts
    17
    Quote Originally Posted by Littlefoot View Post
    May I try?
    Code:
    create or replace view sales_over_30000_with_total
    as
       select employee_name, total_sold
         from (select e.first_name || ' ' || e.last_name as employee_name,
                      (select sum (so.total)
                         from sales_order so)
                         total_sold
                 from employee e
                      left outer join customer c
                         on c.salesperson_id = e.employee_id
                      left outer join sales_order so
                         on so.customer_id = c.customer_id)
        where total_sold > 30000
    Hi Littlefoot, that actually seems to have worked to some extent. It is pulling through some data but here is the data pulled.

    Code:
    EMPLOYEE_NAME                   TOTAL_SOLD             
    ------------------------------- ---------------------- 
    TERRY JONES                     237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    SARAH DUNCAN                    237236.75              
    RICHARD LEWIS                   237236.75              
    RAYMOND PORTER                  237236.75              
    RAYMOND PORTER                  237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    PAUL ROSS                       237236.75              
    MICHAEL DOUGLAS                 237236.75              
    MATTHEW FISHER                  237236.75              
    MARY TURNER                     237236.75              
    MARY TURNER                     237236.75              
    MARY TURNER                     237236.75              
    MARY TURNER                     237236.75              
    MARY TURNER                     237236.75              
    MARY TURNER                     237236.75              
    MARION BLAKE                    237236.75              
    LYNN DENNIS                     237236.75              
    LIVIA WEST                      237236.75              
    LIVIA WEST                      237236.75              
    LIVIA WEST                      237236.75              
    LIVIA WEST                      237236.75              
    LIVIA WEST                      237236.75              
    LIVIA WEST                      237236.75              
    LIVIA WEST                      237236.75              
    LIVIA WEST                      237236.75              
    LESLIE BAKER                    237236.75              
    KEVIN ALLEN                     237236.75              
    KEVIN ALLEN                     237236.75              
    KEVIN ALLEN                     237236.75              
    KEVIN ALLEN                     237236.75              
    KEVIN ALLEN                     237236.75              
    KENNETH MARTIN                  237236.75              
    KENNETH MARTIN                  237236.75              
    KENNETH MARTIN                  237236.75              
    KENNETH MARTIN                  237236.75              
    KENNETH MARTIN                  237236.75              
    KENNETH MARTIN                  237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    KAREN SHAW                      237236.75              
    JOHN SMITH                      237236.75              
    JENNIFER FORD                   237236.75              
    JEAN DOYLE                      237236.75              
    JAMES MURRAY                    237236.75              
    GREGORY LANGE                   237236.75              
    GREGORY LANGE                   237236.75              
    GRACE ROBERTS                   237236.75              
    FRED JAMES                      237236.75              
    FRANCIS KING                    237236.75              
    DONALD SCOTT                    237236.75              
    DIANE ADAMS                     237236.75              
    DENISE SOMMERS                  237236.75              
    DANIEL PETERS                   237236.75              
    DANIEL PETERS                   237236.75              
    DANIEL PETERS                   237236.75              
    DANIEL PETERS                   237236.75              
    DANIEL PETERS                   237236.75              
    DANIEL PETERS                   237236.75              
    CYNTHIA WARD                    237236.75              
    CYNTHIA WARD                    237236.75              
    CYNTHIA WARD                    237236.75              
    CYNTHIA WARD                    237236.75              
    CYNTHIA WARD                    237236.75              
    CYNTHIA WARD                    237236.75              
    CHRIS ALBERTS                   237236.75              
    CAROL CLARK                     237236.75              
    BARBARA MILLER                  237236.75              
    ALICE JENSEN                    237236.75              
    129 rows selected

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see; you got it "work" (sort of). I believe that the main problem now is that SALES_ORDER is not joined to any other table, so - TOTAL_SOLD is equal for all of them. Rubbish, I agree.

    What about this:
    Code:
    create or replace view sales_over_30000_with_total
    as
       select employee_name, total_sold
         from (  select e.first_name || ' ' || e.last_name as employee_name,
                        sum (so.total) total_sold
                   from employee e
                        left outer join customer c
                           on c.salesperson_id = e.employee_id
                        left outer join sales_order so
                           on so.customer_id = c.customer_id
               group by e.first_name || ' ' || e.last_name)
        where total_sold > 30000
    Last edited by Littlefoot; 06-10-11 at 07:27.

  12. #12
    Join Date
    Jun 2011
    Posts
    17
    AHA!!! You sir are a legend
    That seems to have worked perfectly..

    Thankyou so much for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •