Two table query acting strangely. Need ideas.

[JGresko]

This should be a simple, straight forward query, but it won't come back. Because it's so simple I don't know what else to do to debug it...

Platform: Oracle8i Enterprise Edition Release 8.1.7.3.0

What I'm trying to do:
Query two tables containing cost data to get the average line item cost over the last year. I have one sub-select just to filter out some unwanted countries.

What's happening:
This query ran fine until I added L.STATUS = 'Paid' as criteria... now it just won't come back. It's not a simple problem with that field, because if I comment out another criteria line it works fine with that line in. It appears to be a limit on the number of criteria I can put on invoice_lines.

Debugging I've tried so far:
I tried commenting out individual criteria lines to see the result. (see below) If I just comment out one of the header lines, it doesn't come back. If I comment out just one of the Invoice_lines lines it comes back in seconds.

H.DATE_INVOICE --won't come back
H.Debit_Credit --won't come back
L.OPERATION --5.4 seconds
L.Accrual --4.4 seconds
L.STATUS --6.2 seconds
L.COUNTRY --5.6 seconds

I tried doing a nested select by putting the criteria in the Select clause (that comes back) and then wrapping it in another Select with a Where that had the criteria in it... it didn't come back. I tried various combinations of criteria in the main select and the outer select... the inner always comes back, the outer where I use the where clause won't.

There is nothing out of the ordinary with these two tables, I've written a lot of other queries against them, most a lot more complicated than this!

Any ideas on what else I can try will be greatly appreciated. Thank you for taking the time to look at this!

Judy

SELECT L.Country,
       H.AGENT,
       H.Currency,
       L.Operation,
       L.CostType,
       L.Feecode,
       TO_CHAR(SYSDATE,'mm') as Month, 
       round(sum(L.Total_In_Currency) / 
             count(L.LINE_NUMBER),0) as Average 
  FROM Invoice_Lines L, Invoice_Headers H
 WHERE H.Header_Uid = L.Header_Uid
   AND H.DATE_INVOICE > sysdate - 365
   AND H.Debit_Credit = 'D'
   AND L.OPERATION in ('8513','8514','8515')
   AND L.Accrual = 'false'   
   AND L.STATUS = 'Paid'
   AND L.COUNTRY in (SELECT distinct CODPAY FROM MEMOIP2.TABPAY)    
Group By L.Country, H.AGENT, H.Currency, L.Operation, 
         L.CostType, L.Feecode, TO_CHAR(SYSDATE,'mm')

 

[PHV]

Have you tried something like this ?

SELECT L.Country,
       H.AGENT,
       H.Currency,
       L.Operation,
       L.CostType,
       L.Feecode,
       TO_CHAR(SYSDATE,'mm') as Month,
       round(sum(L.Total_In_Currency) /
             count(L.LINE_NUMBER),0) as Average
  FROM Invoice_Lines L, Invoice_Headers H[b], MEMOIP2.TABPAY P[/b]
WHERE H.Header_Uid = L.Header_Uid
   [b]AND L.COUNTRY = P.CODPAY[/b]
   AND H.DATE_INVOICE > sysdate - 365
   AND H.Debit_Credit = 'D'
   AND L.OPERATION in ('8513','8514','8515')
   AND L.Accrual = 'false'
   AND L.STATUS = 'Paid'
Group By L.Country, H.AGENT, H.Currency, L.Operation,
         L.CostType, L.Feecode, TO_CHAR(SYSDATE,'mm')

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JGresko]

Thanks PH,

I hadn't tried that version, but I just did and it still didn't come back.

Judy