Results 1 to 5 of 5

Thread: query help

  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Unanswered: query help

    hi


    select NVL(SUM(amount),0) AS AMT
    from payments_trans
    where major='2401' and submajor='00' and
    minor='119' and group_sub='00' and
    sub_head='01' and detail_head='110' and
    sub_detail='111' and pnp='N' and
    vc='V' and treasury_code=2500 and
    contingency='N' and ddo_code=25000103001
    AND TRANS_ISS_DATE BETWEEN '01-APR-04' AND '31-MAR-05'
    AND STATUS NOT IN('T','O')
    group by major,submajor,minor,group_sub,sub_head,
    detail_head,sub_detail,pnp,vc,contingency,treasury _code, ddo_code

    I tried this query. Result is no rows selected. But i need to display zero(0) as AMT if there is no rows.

    Please suggest any changes in the above query.


    bye
    sreedhar

  2. #2
    Join Date
    Mar 2004
    Location
    india
    Posts
    24

    query

    remove the group by field as you are passing and checking all the fields in WHERE condition. It'll give you ZERO (0) as sum, if there is no record corresponding the values given in WHERE conditon.
    regards,
    Chary

  3. #3
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    select nvl((select SUM(amount)
    from payments_trans
    where major='2401' and submajor='00' and
    minor='119' and group_sub='00' and
    sub_head='01' and detail_head='110' and
    sub_detail='111' and pnp='N' and
    vc='V' and treasury_code=2500 and
    contingency='N' and ddo_code=25000103001
    AND TRANS_ISS_DATE BETWEEN '01-APR-04' AND '31-MAR-05'
    AND STATUS NOT IN('T','O')
    group by major,submajor,minor,group_sub,sub_head,
    detail_head,sub_detail,pnp,vc,contingency,treasury _code, ddo_code), 0) AS AMT from dual;

  4. #4
    Join Date
    Feb 2004
    Posts
    108

    Re: query help

    Originally posted by m_sreedhar

    I tried this query. Result is no rows selected. But i need to display zero(0) as AMT if there is no rows.

    I suggest handle this at application level. If the query raises exception - NDF, or if the return value is NULL then you can always assign zeroes.

    Sum = 0 and Sum is NULL - both may have completely different meanings for the application.

    Sum = 0 means that payments_trans DOES HAVE txns - but with 0 amt. So sum is zero.

    But sum returning NULL means that there are no txns.

  5. #5
    Join Date
    Nov 2003
    Posts
    10
    all the above solutions are right.

    thanks to all.

Posting Permissions

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