Help with Group By.... 1

[Darrel77]

Here's what I need...

My simple query looks like this....

SELECT acctnum, checknum, postdate, amt
FROM trans
WHERE checknum is not null
AND postdate between '30-JAN-06' AND '02-FEB-06'
GROUP BY acctnum, checknum, postdate, amt
ORDER BY acctnum, checknum

...I need to create a report that identifies checks that clear an account during any given week that are off by
more than 100 check numbers. For example, check # 821 clears October 26 and then check # 989 clears on October 28. This would be identified in the report as a warning sign for review.

I posted this in a different forum and the most recent reply was ...

SELECT a.acctnum, checknum, postdate, amt
FROM trans t,
(select acctnum from trans WHERE checknum is not null
AND postdate >= '30-JAN-06' AND postdate <= '02-FEB-06' and max(checknum)- Min(checknum)>100
group by acctnum) a
WHERE t.acctnum = a.acctnum
ORDER BY a.acctnum, checknum

...but this query comes back with a...

"ORA-00934: group function is not allowed here"

Please advise.

Thanks,
Darrel

 

[SantaMufasa]

Darrel,

I'm certain we can get this to work for you, but before I/we suggest syntax that will get you past your current issue, let's ensure that the syntax is WYGIWYW (What You Get Is What You Wanted).

Could you please restate, in plain-English (...don't use SQL syntax) what it is that you are trying to display (including your subquery)? You current syntax is slightly puzzling to me.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Beilstwh]

SELECT t.acctnum, t.checknum, t.postdate, t.amt
FROM trans t,
(select acctnum
from trans,max(checknum) maxc, Min(checknum) minc
WHERE checknum is not null
AND postdate >= '30-JAN-06'
AND postdate <= '02-FEB-06'
group by acctnum) a
WHERE t.acctnum = a.acctnum
and a.maxc - a.minc > 100
ORDER BY t.acctnum, t.checknum;




Bill
Oracle DBA/Developer
New York State, USA

 

[Darrel77]

Bill...
I tried your sugesstion and I received a missing right parenthesis error on the "max(checknum) maxc" field.

When I try to run the virtual table query by itself, I get a SQL command not properly ended on the same field.

------
Mufasa...

In essence, if I have a TRANS table that reads...

ACCTNUM CHECKNUM POSTDATE AMT
123456 497 2/1/06 50.00
123456 499 2/1/06 140.00
123456 500 2/1/06 500.00
123456 501 2/3/06 153.13
123456 502 2/5/06 196.02
123456 650 2/14/06 178.25
123456 651 2/17/06 423.25

I need to write a query that'll show...
ACCTNUM CHECKNUM POSTDATE AMT
123456 502 2/5/06 196.02
123456 650 2/14/06 178.25

....simply because these checks were processed out of a sequence greater than 100 numbers.

Hope this clarifies my issue.

- Darrel

 

[Beilstwh]

Sorry, bad cut and paste. Try

SELECT t.acctnum, t.checknum, t.postdate, t.amt
FROM trans t,
(select acctnum, max(checknum) maxc, Min(checknum) minc
from trans
WHERE checknum is not null
AND postdate >= '30-JAN-06'
AND postdate <= '02-FEB-06'
group by acctnum) a
WHERE t.acctnum = a.acctnum
and a.maxc - a.minc > 100
ORDER BY t.acctnum, t.checknum;

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Darrel,

If you are simply looking for checks where the gap between adjacent checks is > 100, then you might like this code:

SELECT a.acctnum, a.checknum, a.postdate, a.amt,
       b.acctnum, b.checknum, b.postdate, b.amt
FROM trans a
    ,trans b
WHERE b.acctnum = a.acctnum
  and b.checknum > a.checknum+100
  and b.checknum =
      (select min(checknum)
         from trans
        where acctnum = a.acctnum
          and checknum > a.checknum)
ORDER BY a.acctnum, a.checknum
/

   ACCTNUM   CHECKNUM POSTDATE         AMT    ACCTNUM   CHECKNUM POSTDATE         AMT
---------- ---------- --------- ---------- ---------- ---------- --------- ----------
    123456        502 05-FEB-06     196.02     123456        650 14-FEB-06     178.25
*************************************************************************************

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Darrel77]

Mufasa...

That seems to be the base of what I'm looking for! Worked like a charm. I'm gonna tweak the query a bit to look for a select number of accounts within a particular period, but in essence, I think I'm in good shape now.

Thanks!
Darrel