Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Adding patron name to patron-owes SQL

    • 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
    //Feedback