• Article Type: General
• Product: Aleph
• Product Version: 20

Description:
We would like to add z303 name to the following sql. I think this takes a multiple join including z31, z308 and z303, but I can't figure out how to do it.

set echo off
set pause off
set term off
set show off
set feed off
set verify off
set pagesize 58

column PATRON_ID format a14 heading "Patron-ID"
column BALANCE format $999,999.99 heading "Balance"

spool patron-balance-all.lst
select substr(z31_rec_key,1,12) PATRON_ID, sum(to_number(concat(translate(z31_credit_debit,'CD','-0'),translate(z31_sum/100,'
','0')))) BALANCE
from z31
where z31_status = 'O'
group by substr(Z31_REC_KEY,1,12) HAVING sum(to_number(concat(translate(z31_credit_debit,'CD','-0'),translate(z31_sum/100,'
','0')))) > 0
order by BALANCE desc;

spool off
exit;

Resolution:
[From site:] We got this working. There were two issues:

First, the formatting of the query was in part causing Oracle to think it reached the end of the statement before it had fully been parsed.

Second, even with the statement formatting corrected the statement was running into ORA-00979 “not a GROUP BY expression error. That error is caused because GROUP BY clauses MUST contain each and every element of the SELECT statement used.

Corrected SQL:

set echo off
set pause off
set term off
set show off
set feed off
set verify off
set pagesize 58

column PATRON_ID format a14 heading "Patron-ID"
column BALANCE format $999,999.99 heading "Balance"
column NAME format a30 heading "NAME"

spool patron-balance-all-CES.lst
select z303_name NAME, substr(z31_rec_key,1,12) PATRON_ID,
sum(to_number(concat(translate(z31_credit_debit,'CD','-0'),translate(z31_sum/100,'
','0')))) BALANCE
from z31, z303
where z31_status = 'O'
and
substr(z31_rec_key,1,12) = z303_rec_key
group by
z303_name,
substr(z31_rec_key,1,12) HAVING
sum(to_number(concat(translate(z31_credit_debit,'CD','-0'),translate(z31_sum/100,'
','0')))) > 0
order by BALANCE desc;

spool off
exit;

• Article last edited: 10/8/2013