Help with Complex Query 1

[eerich]

Hi Everyone,

I'm trying to extract data from a general ledger system where I need to select a full journal entry which is comprised of several lines based on criteria of at least one line item in an account in an Income Statement range that meets a credit amount value as well as criteria for the other account that may have hit the Income Statement as a debit amount value. I've tried using the EXISTS clause but can't seem to get it quite right to return the full journal entry where the 2 conditions are met. Here's my query:

SELECT c.GLDOC, c.LineSeqNum, c.GLAmount, c.GLPeriod, c.GLAcctNum, c.AcctDescr
FROM Corp as C
Where
Exists (Select * from Corp WHERE (c.Gldoc = corp.Gldoc)
AND
((cint(GLAcctNum) Between 4000 AND 9999) AND GLAmount <=-350000)
AND
Exists (Select * from Corp WHERE (c.Gldoc = corp.Gldoc)
AND
((cint(GLAcctNum) NOT Between 0 AND 2999) AND GLAmount >0)
))
Order by GLDOC, CINT(LineSeqNum);

I can't get the entire journal entry line items to return. For instance I have one entry that is comprised of 73 lines but when I run the above query it won't return the entire entry, it only gives me a subset of the lines.

Any help is much appreciated

 

[Remou]

Does this not suit?

SELECT c.GLDOC, c.LineSeqNum, c.GLAmount, c.GLPeriod, c.GLAcctNum, c.AcctDescr
FROM Corp AS c
WHERE (cint(c.GLAcctNum) Between 4000 AND 9999
AND c.GLAmount <=-350000)
OR (cint(c.GLAcctNum) NOT Between 0 AND 2999
AND c.GLAmount >0)

If GLDoc is the memo field, do not use order by, it will truncate the field. You could set up an extra field, say:

Left(GLDoc, 100)

And sort on that.

http://lessthandot.com

 

[eerich]

Hi Remou,

No - that doesn't return the entire journal entry. It would only return a subset of the lines that make up a journal entry. The GLDoc field + LineSeqNum is makes up the primary key. So the dataset would look something like:

GLDOC LINESEQNUM
01055 001
01055 002
01055 003
01055 004
02300 001
02300 002
02300 003

If for instance the GLaccount number matches the criteria for GLDOC 01055 lines 003 and 004, my result set needs to return all of the lines for GLDOC 01055, i.e., 01055 lines 1 through 4

 

[Remou]

So the problem is returning a set, not a truncated memo, as I had thought. How about:

SELECT c.GLDOC, c.LineSeqNum, c.GLAmount, c.GLPeriod, c.GLAcctNum, c.AcctDescr
FROM Corp AS c
INNER JOIN (
   SELECT c.GLAcctNum 
   FROM Corp 
   WHERE (cint(c.GLAcctNum) Between 4000 AND 9999
   AND c.GLAmount <=-350000)
   OR (cint(c.GLAcctNum) NOT Between 0 AND 2999
   AND c.GLAmount >0)) As g
ON c.GLAcctNum=g.GLAcctNum

http://lessthandot.com

 

[PHV]

what about this ?

SELECT GLDOC,LineSeqNum,GLAmount,GLPeriod,GLAcctNum,AcctDescr
FROM Corp
WHERE GLDOC IN (SELECT GLDOC FROM Corp
WHERE (Val(GLAcctNum) Between 4000 And 9999 AND GLAmount<=-350000)
OR (Val(GLAcctNum) NOT Between 0 And 2999 AND GLAmount>0))
ORDER BY 1,2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eerich]

Hi Remou,

I receive a syntax error using the example you posted. It doesn't recognize the aliases within the subquery. I'm prompted to enter a value for c.GLAcctNum and c.GLAmount.

I've tried to modify it slightly but I still receive the error

 

[eerich]

Hi PHV,

Your examples works great but it doesn't evaluate both of the criteria because of the OR condition. I need to return all lines of the GLDOC journal where at least one line item contains a credit amount to an expense GL account number AND the debit side posts to any other GL account that is not on the balance sheet side (i.e., not between GLAccounts 0 and 2999).

That is why in my original query I was aliasing the table against itself in order to select all of the expense accounts with a credit amount and then determine if the other line items in the journal entry were debited to either revenue or expense.

Any other suggestions is much appreciated.

 

[PHV]

SELECT GLDOC,LineSeqNum,GLAmount,GLPeriod,GLAcctNum,AcctDescr
FROM Corp
WHERE GLDOC IN (SELECT GLDOC FROM Corp
WHERE Val(GLAcctNum) Between 4000 And 9999 AND GLAmount<=-350000)
AND GLDOC IN (SELECT GLDOC FROM Corp
WHERE Val(GLAcctNum) NOT Between 0 And 2999 AND GLAmount>0)
ORDER BY 1,2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

Oops. that should be:

SELECT c.GLDOC, c.LineSeqNum, c.GLAmount, c.GLPeriod, c.GLAcctNum, c.AcctDescr
FROM Corp AS c
INNER JOIN (
   SELECT c.GLAcctNum
   FROM Corp c
   WHERE (cint(c.GLAcctNum) Between 4000 AND 9999
   AND c.GLAmount <=-350000)
   OR (cint(c.GLAcctNum) NOT Between 0 AND 2999
   AND c.GLAmount >0)) As g
ON c.GLAcctNum=g.GLAcctNum

http://lessthandot.com

 

[PHV]

Another way with no subqueries at all:

SELECT DISTINCT C.GLDOC,C.LineSeqNum,C.GLAmount,C.GLPeriod,C.GLAcctNum,C.AcctDescr
FROM (Corp C
INNER JOIN Corp A ON C.GLDOC=A.GLDOC)
INNER JOIN Corp B ON C.GLDOC=B.GLDOC
WHERE Val(A.GLAcctNum) Between 4000 And 9999 AND A.GLAmount<=-350000
AND Val(B.GLAcctNum) NOT Between 0 And 2999 AND B.GLAmount>0
ORDER BY 1,2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eerich]

Hi,
I've tried both of the additional solutions but they don't return the correct results. I think a self-join is needed so that the comparison can select all of the GLDOC line items with a credit amount entry to an expense account and then check to see whether any of the other line items within the matching GLDOC has a debit to the non- balance sheet accounts. For my dataset:

GLDOC LINESEQNUM GLACCTNO ACCTCLASS GLAMOUNT
01055 001 4650 EXPENSE -375,000
01055 002 2070 LIABILITY -106,400
01055 003 3490 REVENUE 370,000
01055 004 1035 ASSET 111,400
02300 001 1120 ASSET 250,000
02300 002 4650 EXPENSE -500,000
02300 003 1330 ASSET 250,000

My result set should return all 4 lines of GLDOC '01055' because the expense threshhold of <= -350,000 is met AND a 'revenue' account was debited.

The results should not include GLDOC '02300' because although the Expense criteria is met, the other lines were balance sheet accounts.

Any help on how I might construct this query would be helpful.

Thanks

 

[eerich]

PHV,

Your last solution WORKED!!! Incredible!
This is such a tremendous help I can't thank you all enough.

I apologize I hadn't seen your post before I posted the previous response.

Thanks so much and have a Star!