Query Report

•

What is DaniWeb IT Discussion Community?

You're currently browsing the Oracle section within the Web Development category of DaniWeb, a massive community of 423,764 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,391 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our Oracle advertiser: Programming Forums

Thread Tools Display Modes

Views: 548 | Replies: 2

•

Join Date: Oct 2006

Posts: 15

Reputation: Bobbiegirl is an unknown quantity at this point

Rep Power: 2

Solved Threads: 0

Bobbiegirl Bobbiegirl is offline

Offline

Newbie Poster

Query Report

Apr 13th, 2008

I have written the following code to try and run a report that will list customers names and ytd charter costs whose ytd charter cost are greater than the average charter cost for all customers. There are 4 table involved:
customer: PK cus_code, cus_fname, cus_lname||
charter: pk char_trip, char_date, cus_code,char_distance,ac_number||
aircraft: pk ac_number, mod_code||
model: pk mod_number, mod_chg_mile
The total cost is calculated by the destination times the charge per mile plus 35% for profit. When I run it I get an error of Oracle>

Help with Code Tags

(Toggle Plain Text)

@ U7LQ3
(SELECT avg(sum(charter.char_distance*model.mod_chg_mile)*1.35,'$99,999.99')
*
ERROR at line 13:
ORA-00933: SQL command not properly ended
[SELECT cust.cus_fname, cust.cus_lname "Customer",
     sum(charter.char_distance*model.mod_chg_mile)* 1.35, '$99,999.99' "C\
harter"
FROM hartmar.customer JOIN hartmar.charter
ON customer.cus_code = charter.char_code
 JOIN hartmar.aircraft
ON charter.ac_number = aircraft.ac_number
JOIN hartmar.model
ON aircraft.mod_code = model.mod_code
where exists
    (select distinct charter.char_date
      from hartmar.charter
     where charter.char_date = '12 months')
(SELECT avg(sum(charter.char_distance*model.mod_chg_mile)*1.35,'$99,999.9\
9')
   FROM hartmar.charter
GROUP BY customer.cus_lname)
;]

Can someone please advise, I am beginning to think that I am trying to over analyze it.

Thank you
Barb

Last edited by peter_budo : Apr 17th, 2008 at 1:50 pm. Reason: Keep It Organized - please use [code] tags

•

Join Date: Nov 2004

Location: Netherlands

Posts: 5,752

Reputation: jwenting is a jewel in the rough

Rep Power: 18

Solved Threads: 197

jwenting

Offline

duckman

Re: Query Report

Apr 14th, 2008

yes, you have a syntax error in your SQL which is why Oracle doesn't understand it.
Most likely it's a missing or superfluous brace somewhere.

As people are clearly allowed to attack me but I'm not allowed to defend myself, I no longer post to this site.

•

Join Date: Oct 2006

Posts: 15

Reputation: Bobbiegirl is an unknown quantity at this point

Rep Power: 2

Solved Threads: 0

Bobbiegirl Bobbiegirl is offline

Offline

Newbie Poster

Re: Query Report

Apr 14th, 2008

I have posted the whole srcipt again as I have made a ouple of adjustments. Also the error I am getting is on the end of the script:

Help with Code Tags

(Toggle Plain Text)

{ERROR:(SELECT avg(sum(charter.char_distance * model.mod_chg_mile)*1.35,'$99,999.99'))
 *
ERROR at line 13:
ORA-00936: missing expression}

new updated script:

[SELECT cust.cus_fname, cust.cus_lname "Customer",
     sum(charter.char_distance*model.mod_chg_mile)* 1.35, '$99,999.99' "Charter"
FROM hartmar.customer JOIN hartmar.charter
ON customer.cus_code = charter.char_code
 JOIN hartmar.aircraft
ON charter.ac_number = aircraft.ac_number
JOIN hartmar.model
ON aircraft.mod_code = model.mod_code
where exists
    (select distinct charter.char_date
      from hartmar.charter
where charter.char_date = ADD_MONTHS(TO_DATE(CONCAT('10-01', 'FISCAL_YEAR'), 'MM-DD-YYYY'), -12)
(SELECT avg(sum(charter.char_distance * model.mod_chg_mile)*1.35,'$99,999.99'))
   FROM hartmar.charter
 FROM hartmar.charter
GROUP BY customer.cus_lname))
;]

I have tried to firgur out what express is missing but cannot seem to locate it.

Barb

Last edited by peter_budo : Apr 17th, 2008 at 1:51 pm. Reason: Keep It Organized - please use [code] tags