view GLPost inner join GLPost 1

[bodlela]

Hi there

I need to create a view like gl transaction listing report for bank accounts (11... or 12...) but instead of showing the account transaction I need to see the transaction which the account is dealing with. Some thing like:

SELECT GLPOST_1.ACCTID, GLPOST_1.FISCALYR, GLPOST_1.FISCALPERD,
GLPOST_1.SRCECURN, GLPOST_1.SRCELEDGER, GLPOST_1.SRCETYPE, GLPOST_1.CNTDETAIL, dbo.GLPOST.JRNLDATE, dbo.GLPOST.BATCHNBR,
dbo.GLPOST.ENTRYNBR, dbo.GLPOST_1.TRANSAMT, dbo.GLAMF.ACCTDESC
FROM dbo.GLPOST INNER JOIN
dbo.GLPOST AS GLPOST_1 ON dbo.GLPOST.BATCHNBR = GLPOST_1.BATCHNBR AND dbo.GLPOST.ENTRYNBR = GLPOST_1.ENTRYNBR AND
dbo.GLPOST.CNTDETAIL <> GLPOST_1.CNTDETAIL INNER JOIN
dbo.GLAMF ON dbo.GLPOST.ACCTID = dbo.GLAMF.ACCTID
WHERE (dbo.GLPOST.ACCTID LIKE '11%' OR
dbo.GLPOST.ACCTID LIKE '12%') and GLPOST.JRNLDATE BETWEEN 20100101 AND 20100131

But this query is failing somewhere because I'm not having the same total as the net change of gl transation listing report for the same period.

Please help

 

[DjangMan]

So you want to locate a transaction for an account that starts with 11 or 12 and see the all of the details within the same entry?

 

[bodlela]

Yes, that is it. Any other idea to get it?

Thanks

 

[Jay Converse]

Why are you joining GLPOST to itself? You just need to join GLPOST to GLAMF.

 

[bodlela]

Hi

because I need to locate a transaction for an account that starts with 11 or 12 and see the all accounts of the details within the same entry

 

[Jay Converse]

Yeah, and that means what? You already have WHERE ACCTID LIKE '11%' OR ACCTID = '12%', there's your selection.

 

[ettienne]

He wants the other side of the entries of anything posted to 11% or 12%.

 

[Jay Converse]

Oh, I get it now.

The numbers are off because you're double counting the original entry. You need a compound select, in other words, you need to join

GLPOST WHERE ACCTID LIKE '11%' OR ACCTID = '12%'

to

GLPOST WHERE ACCTID NOT LIKE '11%' OR ACCTID = '12%',

 

[apexearth]

I didn't have too much time to look into this but I do like query problems. Does this query work better to suit your needs?

SELECT top 100 GLPOST_1.ACCTID, GLPOST_1.FISCALYR, GLPOST_1.FISCALPERD,
GLPOST_1.SRCECURN, GLPOST_1.SRCELEDGER, GLPOST_1.SRCETYPE, GLPOST_1.CNTDETAIL, GLPOST_SQ.JRNLDATE, GLPOST_SQ.BATCHNBR,
GLPOST_SQ.ENTRYNBR, GLPOST_1.TRANSAMT, dbo.GLAMF.ACCTDESC
FROM (SELECT DISTINCT ACCTID, BATCHNBR, ENTRYNBR, JRNLDATE from dbo.GLPOST) AS GLPOST_SQ INNER JOIN
dbo.GLPOST AS GLPOST_1 ON GLPOST_SQ.BATCHNBR = GLPOST_1.BATCHNBR AND GLPOST_SQ.ENTRYNBR = GLPOST_1.ENTRYNBR INNER JOIN
dbo.GLAMF ON GLPOST_SQ.ACCTID = dbo.GLAMF.ACCTID
WHERE (GLPOST_SQ.ACCTID LIKE '11%' OR
GLPOST_SQ.ACCTID LIKE '12%') and GLPOST_SQ.JRNLDATE BETWEEN 20100101 AND 20100131

 

[bodlela]

Thanks for replying guys

The apexearth query is giving 0.00 (zero) as total if I do sum (glpost_1.transamt). I guess it is not excluding the original amount.

Please advice.

 

[apexearth]

Hi,

This query makes sure that it does not include any details that have he same ACCTID as the parent queries ACCTID.

So in this case, GLPOST_SQ would be the parent, GLPOST_1 is the child.

I added in the acctdesc to the other glpost query so you can see both now.

I hope this better suits your needs!

SELECT GLPOST_SQ.ACCTID AS PACCTID, GLAMFA.ACCTDESC as PACCTDESC, GLPOST_1.ACCTID, GLAMFB.ACCTDESC, GLPOST_1.FISCALYR, GLPOST_1.FISCALPERD,
GLPOST_1.SRCECURN, GLPOST_1.SRCELEDGER, GLPOST_1.SRCETYPE, GLPOST_1.CNTDETAIL, GLPOST_SQ.JRNLDATE, GLPOST_SQ.BATCHNBR,
GLPOST_SQ.ENTRYNBR, GLPOST_1.TRANSAMT
FROM (SELECT DISTINCT ACCTID, BATCHNBR, ENTRYNBR, JRNLDATE from dbo.GLPOST ) AS GLPOST_SQ
INNER JOIN dbo.GLPOST AS GLPOST_1 ON GLPOST_SQ.BATCHNBR = GLPOST_1.BATCHNBR
	AND GLPOST_SQ.ENTRYNBR = GLPOST_1.ENTRYNBR AND GLPOST_1.ACCTID <> GLPOST_SQ.ACCTID
INNER JOIN dbo.GLAMF GLAMFA ON GLPOST_SQ.ACCTID = GLAMFA.ACCTID
INNER JOIN dbo.GLAMF GLAMFB ON GLPOST_1.ACCTID = GLAMFB.ACCTID
WHERE (GLPOST_SQ.ACCTID LIKE '11%' OR GLPOST_SQ.ACCTID LIKE '12%')
	AND GLPOST_SQ.JRNLDATE BETWEEN 20100101 AND 20100131
ORDER BY BATCHNBR, ENTRYNBR, CNTDETAIL

 

[bodlela]

Hi apexearth

That is it. thanks a lot

Regards