Query with subquery containing group clause doesn't return any rows - WHY ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Query with subquery containing group clause doesn't return any rows - WHY ?

  1. #1
    Join Date
    Feb 2008
    Posts
    31

    Query with subquery containing group clause doesn't return any rows - WHY ?

    Hi,

    My query doesn't return any values :

    select g1.NTRX from gtrx g1
    where exists
    (SELECT b.cfunctrx, b.cpro1trx, b.nmsgitrx, b.nmrc, b.ncrd, b.namtstrx,
    b.dltimtrx, b.nrtrftrx,count(*)
    FROM gtrxacq a, gtrx b
    WHERE a.ntrx = b.ntrx AND a.acq_bus_date = (SELECT curr_bus_date -1
    FROM gmbr
    WHERE nmbr = 0)
    and g1.NTRX=b.NTRX
    GROUP BY b.cfunctrx,
    b.cpro1trx,
    b.nmsgitrx,
    b.nmrc,
    b.ncrd,
    b.namtstrx,
    b.dltimtrx,
    b.nrtrftrx
    HAVING COUNT (*) > 1);

    --------------------

    but such query returns some number of rows :

    SELECT b.cfunctrx, b.cpro1trx, b.nmsgitrx, b.nmrc, b.ncrd, b.namtstrx,
    b.dltimtrx, b.nrtrftrx,count(*)
    FROM gtrxacq a, gtrx b
    WHERE a.ntrx = b.ntrx AND a.acq_bus_date = (SELECT curr_bus_date -1
    FROM gmbr
    WHERE nmbr = 0)
    /*and g1.NTRX=b.NTRX*/
    GROUP BY b.cfunctrx,
    b.cpro1trx,
    b.nmsgitrx,
    b.nmrc,
    b.ncrd,
    b.namtstrx,
    b.dltimtrx,
    b.nrtrftrx
    HAVING COUNT (*) > 1


    AND when i put results from query above into query :

    select g1.NTRX from gtrx g1
    where
    g1.CFUNCTRX= 200 and g1.CPRO1TRX= 000 and g1.NMSGITRX= 1240 and
    g1.NMRC= '000000000000675' and g1.NCRD= 405671**********
    and g1.NAMTSTRX=14.26 and g1.DLTIMTRX=to_date('07/08/2008 15:07:02','MM/DD/YYYY HH24:MI:SS')
    and g1.NRTRFTRX= '000414598393';

    it returns values.

    what is wrong ?

    Best Regards Arkadiusz Masny

  2. #2
    Join Date
    Jul 2008
    Posts
    7
    Please post the structure of the table

Posting Permissions

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


Click Here to Expand Forum to Full Width