Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    10

    Unanswered: Fill values from other table

    Hi,

    I am attempting what I imagine should be a fairly trivial task, but being fairly new to Oracle am having problems.

    I need to run a simple query which will select a number of columns from one table and also fill an additional column with the maximum date found in another. For instance the query should return something like:

    Rep_Name Customer_Code Date
    ---------- -------------- -----
    R01 C01 25-Aug-2004
    R02 C02 25-Aug-2004
    R03 C03 25-Aug-2004
    ~ ~ ~

    I used the following query to attempt this.

    SELECT sb.rep_code,
    sb.customer,
    TO_CHAR(max(si.date),'DDMMYYYY')
    FROM sales_invoiced si,
    sales_booked sb;

    The following error is retrurned: ORA-00937: not a single-group group function.

    I know that this typically infers that the Group By option is missing, however I don't want to group the output.

    I know I could use PL/SQL but this is not an option, and I'm sure there must be a simple way of doing this using SQL which I am not aware of.

    Any help appreciated.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try:
    Code:
    SELECT sb.rep_code,
           sb.customer,
           (SELECT TO_CHAR(max(si.date),'DDMMYYYY') FROM sales_invoiced si)
    FROM   sales_booked sb;

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You have a couple of problems with your query. No GROUP BY clause and no WHERE clause. Try the one below


    SELECT sb.rep_code,
    sb.customer,
    TO_CHAR(max(si.date),'DDMMYYYY')
    FROM sales_invoiced si,
    sales_booked sb
    WHERE sb.customer = si.customer
    GROUP BY sb.rep_code,sb_customer;


    I am assuming that the customer code is in both tables, if it is something else like customer_id, use that instead. If you don't have a where clause and more then one table, you will have a cartesian join. For example if sales_invoiced had 1000 records and sales_booked had 2000 records, a cartesian would return 2000000 records,

    If you truly want the maximum date in the entire table and not the maximum date per customer then see the previous post from Tony for an excelent solution.
    Last edited by beilstwh; 08-25-04 at 10:00.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2004
    Posts
    10
    Thanks for your help guys.

    Just to clarify, I need the maximum date, and not the date using a join.

    I actually have already tried a solution similiar to that suggested by Tony, but got the same error that I just received using Tony's above:

    ORA-01747: invalid user.table.column, table.column, or column specification

    Using a subquery to me makes the most sense, and I'm sure there must be a way of doing it.

    Thanks.

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    what version of oracle? try this:

    SELECT sb.rep_code,
    sb.customer,
    TO_CHAR(si.date,'DDMMYYYY')
    FROM (SELECT max(si.date) date FROM sales_invoiced si) si,
    sales_booked sb;
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What version of the database are you using. Tony's solution will not work in Oracle 7, but will work in Oracle 8I and above. If you do have a database that will support it, do a describe of the sales_invoiced table and make sure that you are using the correct column names. You would do the describe by typing

    desc sales_invoiced
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jul 2004
    Posts
    10
    Sorry, no joy.

    Thanks anyway.

    v9i

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Does that table really have a column called DATE in it. Doesn't that cause problems since it is a reserved word?

  9. #9
    Join Date
    Jul 2004
    Posts
    10
    OK, it's working.
    Thanks everyone for your help.

    Tony's query did work, I was just using the wrong column names.

Posting Permissions

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