ORA-00979: not a GROUP BY expression

problem query

select class_schedule.class_id, class_schedule.sum(class_schedule.enrolments) tot_enr, class.name from class_schedule inner join class on class_schedule.class_id = class.id group by class_schedule.class.id

solution

select class_schedule.class_id,
sum(class_schedule.enrolments) tot_enr, class.name
from class_schedule
inner join
class on class_schedule.class_id = class.id
group by class_schedule.class_id, class.name

how to convert my query to stored procedure

I have this procedure below:

set serveroutput on
DECLARE
v_counter NUMBER(7) := 0;
v_tax NUMBER(11,2) := 0;
v_an_sal NUMBER(11,2) := 0;
CURSOR emp_cursor IS
SELECT id, last_name, first_name, salary
FROM scott.s_emp;
--r_emp scott.s_emp%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE(rpad(‘ID’,5) || rpad(‘LAST_NAME’, 15) || rpad(‘FIRST_NAME’,15) || lpad(‘ANNUAL_SALARY’,11) || lpad(‘TAX’,11));
DBMS_OUTPUT.PUT_LINE(rpad(‘____ ’,5) || rpad(‘_______________’, 15) || rpad(‘_______________’,15) || lpad(‘___________’,11) || lpad(‘___________’,11));
FOR r_emp IN emp_cursor LOOP
v_counter := v_counter + 1;
v_tax := 0;
v_an_sal := r_emp.salary * 12;
IF v_an_sal > 150000 THEN
v_tax := 47850 + (v_an_sal -150000)*0.45;
ELSIF v_an_sal > 75000 THEN
v_tax := 17850 + (v_an_sal -75000)*0.40;
ELSIF v_an_sal > 25000 THEN
v_tax := 2850 + (v_an_sal -25000)*0.30;
ELSIF v_an_sal > 6000 THEN
v_tax := (v_an_sal -6000)*0.15;
ELSE
v_tax := 0;
END IF;
DBMS_OUTPUT.PUT_LINE(rpad(r_emp.id,5) || rpad(r_emp.last_name, 15) || rpad(r_emp.first_name,15) || lpad(v_an_sal,11) || lpad(v_tax,11));
IF v_counter mod 5 = 0 THEN
DBMS_OUTPUT.PUT_LINE('******************************************************');
END IF;
END LOOP;
END;
/

I need to convert it to a stored procedure (or create a sored procedure for this) so that i can call it from my psp pages.

solution:
remove 'DECLARE set serveroutput on'
replace with 'CREATE OR REPLACE PROCEDURE proc_emp_sum_analysis AS'

loop through query result and print each row in stored procedure

have this query which returns a set of rows, How can I loop through each row and display them using dbms.output.put_line? Please notice in the where clause there is a variable 'v_trainer_id', the variable value is known (retrieved from a column of a current row of another cursor)

select customer.id as customer_id, membership.join_date, membership.join_fee,round(months_between(sysdate, membership.join_date),0) as months_joined, membership_plan.monthly_price, membership.monthly_discount, (membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0) as total_paidfrom membership,membership_plan,customerwhere membership.membership_plan_id = membership_plan.id andcustomer.id=membership.customer_id andmembership.trainer_id = v_trainer_idorder bycustomer.id;

solution:

v_cur_tid := r_trainer.trainer_id;FOR r_mem IN(select membership.customer_id as cust_id, membership.join_date, membership.join_fee,round(months_between(sysdate, membership.join_date),0) as months_joined, membership_plan.monthly_price, membership.monthly_discount, (membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0) as total_paidfrom membership,membership_planwhere membership.membership_plan_id = membership_plan.id andmembership.trainer_id = v_cur_tidorder bymembership.customer_id)LOOP

PL/SQL: ORA-01744: inappropriate INTO

I have this part of the stored procedure, trying to use one select query to fill in 2 variables, got error 'PL/SQL: ORA-01744: inappropriate INTO'

--print summary of subsection

select t.* from (select count(membership.id) into v_tot_mem_by_trainer,sum((membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0)) into v_tot_rev_by_trainerfrom membership, membership_planwhere membership.trainer_id = r_trainer.trainer_id andmembership.membership_plan_id = membership_plan.id) t;

this is the original select query that has no problem

select t.* from (select count(membership.id) as total_number_of_members,sum((membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0)) as total_membership_paymentfrom membership, membership_planwhere membership.membership_plan_id = membership_plan.id) t;

the solution:

select t.* into v_tot_mem_by_trainer, v_tot_rev_by_trainer from (select count(membership.id),sum((membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0))from membership, membership_planwhere membership.trainer_id = r_trainer.trainer_id andmembership.membership_plan_id = membership_plan.id) t;

ORA-00937: not a single-group group function

I want to select some data from table membership and membership_plan, these 2 have some relationships with each other. In the same query, I also want to 'select count(*) from customers' where customers table has no relationship with the other 2 tables, I just want to find out the total number of customers and display with the rest of the query results.

select (select count(id) from customer), count(membership.id) as total_number_of_members, sum((membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0)) as total_all_members from membership,membership_planwhere membership.membership_plan_id = membership_plan.id;

I got an error message as below:
ERROR at line 1:ORA-00937: not a single-group group function

solution:

select (select count(id) from customer), t.* from (select count(membership.id) as total_number_of_members,
sum((membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0)) as total_all_members
from membership, membership_plan
where membership.membership_plan_id = membership_plan.id) t;

oracle sqlldr datetime value problem

this is my loader ctl file

LOAD DATAINFILE 'trainer.data' BADFILE 'trainer.bad'DISCARDFILE 'trainer.dsc'REPLACEINTO TABLE trainerFIELDS TERMINATED BY ','TRAILING NULLCOLS(ID TERMINATED BY ',' ENCLOSED BY '"',FN TERMINATED BY ',' ENCLOSED BY '"',LN TERMINATED BY ',' ENCLOSED BY '"',GENDER TERMINATED BY ',' ENCLOSED BY '"',PHONE TERMINATED BY ',' ENCLOSED BY '"',MOBILE TERMINATED BY ',' ENCLOSED BY '"',EMAIL TERMINATED BY ',' ENCLOSED BY '"',CERTIFICATE_LEVEL TERMINATED BY ',' ENCLOSED BY '"',IS_PERSONAL_TRAINER TERMINATED BY ',' ENCLOSED BY '"',HIRE_DATE TERMINATED BY ',' ENCLOSED BY '"',IS_ACTIVE ENCLOSED BY '"')

the field 'hire_date' is a datetime field. e.g. '15-JUN-2005 13:30' in the format of 'DD-MON-YYYY HH24:Mi'. please see the first 3 rows of my datafile below:

"1","Cara","Surename","m","06623355","0446002359","lorem.ut.aliquam@dictum.edu","a","y","06-JUN-2006 00:00","y""2","Noble","Surename","m","28435168","0451498500","Mauris.magna.Duis@id.com","a","n","06-JUN-2006 00:00","y""3","Coby","Surename","f","79907443","0460112510","Mauris@accumsan.ca","d","n","06-JUN-2006 00:00","y"


then when i executed the ctl file, i got this error below in the log file

Record 1: Rejected - Error on table TRAINER, column HIRE_DATE.ORA-01830: date format picture ends before converting entire input stringRecord 2: Rejected - Error on table TRAINER, column HIRE_DATE.ORA-01830: date format picture ends before converting entire input stringRecord 3: Rejected - Error on table TRAINER, column HIRE_DATE.ORA-01830: date format picture ends before converting entire input string



solution:
HIRE_DATE DATE "DD-MON-YYYY HH24:Mi" TERMINATED BY ',' ENCLOSED BY '"',

An old joke about project manager

A tourist walked into a pet shop and was looking at the animals on display. While he was there,another customer walked in and said to the shopkeeper, "I'll have a C monkey please." Theshopkeeper nodded, went over to a cage at the side of the shop and took out a monkey. Hefitted a collar and leash, handed it to the customer, saying, "That'll be £5,000."

The customer paid and walked out with his monkey.

Startled, the tourist went over to the shopkeeper and said, "That was a very expensive monkey. Most of them are only a few hundred pounds. Why did it cost so much?" The shopkeeperanswered, "Ah, that monkey can program in C - very fast, tight code, no bugs, well worth themoney."

The tourist looked at a monkey in another cage. "Hey, that one's even more expensive! £10,000! What does it do?"

"Oh, that one's a C++ monkey; it can manage object-oriented programming, Visual C++, even some Java. All the really useful stuff," said the shopkeeper.

The tourist looked around for a little longer and saw a third monkey in a cage of its own. The price tag around its neck read £50,000. The tourist gasped to the shopkeeper, "That one costs more than all the others put together! What on earth does it do?"

The shopkeeper replied, "Well, I haven't actually seen it do anything, but it says it's a project manager".

oracle how to random select a value out of 2 letters

select chr(121 - 11 * (round(dbms_random.value))) is_successful from dual
or

select case when dbms_random.value(0,1) < 0.5 then 'y' else 'n' end is_successful from dual

or

select translate(dbms_random.string('U',1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ynynynynynynynynynynynynyn') from dual

or

select distinct First_Value(is_successful) over(order by dbms_random.value) as is_successfulfrom (select 'n' as is_successful from dual union all select 'y' from dual);

or

substr('yn', dbms_random.value(1,3), 1)

oracle add a day

select sysdate+1 from dual

oracle add a day

select sysdate+1 from dual

oracle get a rendom set of string value with length in between 0 and 100

select dbms_random.string('l',dbms_random.value(0,100)) from dual

oracle return a range of integer

select level num from dual connect by level <= 5;

NUM
1
2
3
4
5

oracle get a random value out of a query result

I have a query:

select id from class_schedule where id not in (select distinct(event_id) from trainer_schedule)

I want to pick up a random value from this query result and assign it to a varible e.g. randomID.

solution:

randomID :=
select id from
(
select id from class_schedule
where id not in (select distinct(event_id) from trainer_schedule)
order by dbms_random.value
)
where rownum = 1

oracle update one table with values from another table

e.g.

update membership
set membership.join_fee = (select membership_plan.join_fee
from membership_plan
where membership.membership_plan_id = membership_plan.id)
where exists (select 1
from membership_plan
where membership.membership_plan_id = membership_plan.id)

ORA-06550 problem in script

Tried to execute the script below got error The query:

DECLARE
CURSOR sol_cursor IS select * from membership;
months NUMBER(5,0) :=0;
BEGIN
FOR r_sol IN sol_cursor LOOP
months := (select min_months from membership_plan where id = r_sol.membership_plan_id);
UPDATE membership
SET end_date = add_months(r_sol.end_date,months)
WHERE id = r_sol.id
END LOOP;
END;

ERROR:

ORA-06550: line 6, column 12: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null others avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

SOLUTION:
changing:
months := (select min_months from membership_plan where id = r_sol.membership_plan_id);

to:
select min_months INTO months from membership_plan where id = r_sol.membership_plan_id;

oracle add an hour

select to_char(to_date('15-08-2006 21:30','DD-MM-YYYY HH24:Mi')+1/24,'DD-MM-YYYY HH24:Mi') from dual
or
select sysdate, sysdate + 5/24 from dual;

I created this, do you know what it does?

update class_schedule set start_time= add_months(start_time, -12*

(select to_number(to_char(start_time,'yyyy')-2006) from dual)) where

to_char(start_time,'yyyy')> to_char(2006)


GOOD LUCK!

oracle add a year

update class_schedule set start_time = (select add_months(start_time,12*2003)from dual)

or

add_months(trunc(start_time),12*35)

oracle update and display datetime in Date datatype fields

update class_schedule set start_time = to_date('2003-06-13 15:18','YYYY-MM-DD HH24:MI') where id=17

select to_char(start_time,'YYYY-MM-DD HH24:MI') from class_schedule where id=17;

oracle generate a random date using julian dates

SELECT TO_CHAR(TO_DATE('2006-06-06', 'YYYY-MM-DD'), 'J') FROM DUAL;
--> 2453893

SELECT TO_CHAR(TO_DATE('2007-10-01', 'YYYY-MM-DD'), 'J') FROM DUAL;
--> 2454375


SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2453893, 2454375)), 'J') FROM DUAL;

--> 19/SEP/06

using pl/sql dbms_random function for generating random values

UPDATE purchase_order_line
SET discount_total = (select round(dbms_random.value(0, 9999.22),2) from dual)
WHERE purchase_order_id = r_po.purchase_order_id;