Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    8

    Unhappy Unanswered: Union-Top query 6 rows, bottom query 0 rows, both 4 rows??

    I have a query that is a union with two selects. When the top select is run seperately it returns 6 rows. When the bottom query is run it returns 0 rows. When the whole query is run it returns 4 rows. Why is this odd behavior happening?

    -------------------
    elect
    sat.service_account_number,
    nvl(sat.service_id, -99) ,
    nvl(sat.service_type ,'NULL'),
    nvl(s.service_type, 'NULL') service_service_type,
    nvl(rc.rate_category ,'NULL'),
    nvl(att.transaction_type, 'NULL') at_trans_type,
    sat.transaction_type sat_trans_type,
    nvl(sat.read_class, 'NULL'),
    trunc(sat.trans_date) trans_date,
    sum(sat.trans_amount) trans_amount,
    sum(sat.allocation_unit) sat_allocation_unit,
    max(att.gst_exempt) at_gst_exempt,
    max(att.pst_exempt) at_pst_exempt,
    max(att.payment_method_type) at_payment_method_type,
    max(att.accounting_period) at_accounting_period,
    max(sat.gl_number) sat_gl_number,
    max(sat.offset_gl_number) sat_offset_gl_number,
    nvl(sat.billing_charge_type, 'NULL'),
    max(sat.wahsp) sat_wahsp,
    nvl(sat.rate_period_id ,-99),
    nvl(rc.rate_class_billing_type, 'Z'),
    att.account_number,
    nvl(rp.tlf_indicator, 'Z'),
    nvl(rc.rate_class_id,-99),
    ac.account_type
    from cis.account_transaction@standby.world att,
    cis.service_acct_transaction@standby.world sat,
    cis.service@standby.world s,
    cis.read_class@standby.world r,
    cis.rate_period@standby.world rp,
    cis.rate_class@standby.world rc,
    cis.service_account@standby.world sa,
    cis.account@standby.world ac
    /*where sat.service_acct_trans_id >= n_start_service_acct_tran_id
    and sat.service_acct_trans_id <= n_end_service_acct_tran_id*/
    where sat.service_id = 63923
    and trunc(sat.trans_date) = '26-nov-2001'
    and sat.transaction_type = 'WMRENT'
    and att.account_transaction_id = sat.account_transaction_id (+)
    and sat.service_id = s.service_id (+)
    and sat.read_class = r.read_class (+)
    and sat.rate_period_id = rp.rate_period_id (+)
    and rp.rate_class_id = rc.rate_class_id (+)
    and sat.service_account_number = sa.service_account_number
    and sa.account_number = ac.account_number
    group by att.account_number,ac.account_type, sat.service_account_number,
    sat.service_id,sat.service_type ,
    s.service_type,sat.billing_charge_type,
    rc.rate_category,att.transaction_type,
    sat.transaction_type,
    sat.read_class,sat.rate_period_id,
    rp.tlf_indicator,rc.rate_class_id,
    rc.rate_class_billing_type,
    att.trans_date,sat.trans_date
    union -- Service Account Transaction records with a null Account Transaction Id
    select sat.service_account_number,
    nvl(sat.service_id, -99) ,
    nvl(sat.service_type , 'NULL') sat_service_type,
    nvl(s.service_type, 'NULL') service_service_type,
    nvl(rc.rate_category ,'NULL'),
    'NULL' at_trans_type,
    sat.transaction_type sat_trans_type,
    nvl(sat.read_class, 'NULL'),
    trunc(sat.trans_date) trans_date,
    sum(sat.trans_amount) trans_amount,
    sum(sat.allocation_unit) sat_allocation_unit,
    'N' at_gst_exempt,
    'N' at_pst_exempt,
    'NULL',
    to_char(sysdate,'YYYYMM') at_accounting_period,
    max(sat.gl_number) sat_gl_number,
    max(sat.offset_gl_number) sat_offset_gl_number,
    nvl(sat.billing_charge_type, 'NULL'),
    max(sat.wahsp) sat_wahsp,
    nvl(sat.rate_period_id ,-99),
    nvl(rc.rate_class_billing_type, 'Z'),
    sa.account_number,
    nvl(rp.tlf_indicator, 'Z'),
    nvl(rc.rate_class_id,-99),
    ac.account_type
    from cis.service_acct_transaction@standby.world sat,
    cis.service@standby.world s,
    cis.read_class@standby.world r,
    cis.service_account@standby.world sa,
    cis.account@standby.world ac,
    cis.rate_period@standby.world rp,
    cis.rate_class@standby.world rc
    /* where sat.service_acct_trans_id >= n_start_service_acct_tran_id
    and sat.service_acct_trans_id <= n_end_service_acct_tran_id
    */
    where sat.service_id = 63923
    and trunc(sat.trans_date) = '26-nov-2001'
    and sat.transaction_type = 'WMRENT'
    and sat.account_transaction_id is null
    and sat.service_id = s.service_id (+)
    and sat.read_class = r.read_class (+)
    and sat.service_account_number = sa.service_account_number
    and sa.account_number = ac.account_number
    and sat.rate_period_id = rp.rate_period_id (+)
    and rp.rate_class_id = rc.rate_class_id (+)
    group by sa.account_number,
    ac.account_type,
    sat.service_account_number,
    sat.service_id,
    sat.service_type ,
    sat.billing_charge_type,
    rc.rate_category,
    s.service_type,
    sat.transaction_type,
    sat.read_class,
    sat.rate_period_id,
    rp.tlf_indicator,
    rc.rate_class_id,
    rc.rate_class_billing_type,
    sat.trans_date

  2. #2
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    31
    it's early, very early... so forgive me if i lead you astray... but try a UNION ALL
    My homepage:
    http://www.buro9.com/
    My work:
    http://www.btopenworld.com/
    http://www.officialfootballsites.co.uk/
    http://www.jeepster.co.uk/

Posting Permissions

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