RE: SQL Question

Thanks for the reply, Daniel...

I guess my issue would be the "WHERE" statement.

Both files contain the "year"... So if the user wants "where the year is 2024 and the period is 12", how do I qualify my statement?

If I say "where GLMAST.YEAR = 2024" then I'll still be missing the accounts that don't have a record in GLMAST.

Or, do I need to use the FULL OUTER JOIN to create a CTE containing a comprehensive list of Accounts and Years, then use that for my selection?

I may have oversimplified this a bit... I already have SQL Views of each table. Maybe I need a third view to hide all of this logic and give my report writer fields to qualify on?

Thanks again,
Greg

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Monday, March 3, 2025 11:00 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: SQL Question

Hi Greg,

the szenario that you describe screams "FULL OUTER JOIN".

Let's say, you have the fields GLMAST.ACCOUNT and GLDETL.ACCOUNT that both hold the account number - you can do it with:

SELECT GLMAST.*, GLDETL.*
FROM GLMAST
FULL OUTER JOIN GLDETL ON GLDETL.ACCOUNT = GLMAST.ACCOUNT
WHERE ...

This means, that you will get:

1. all rows from GLMAST where NO matching rows exist in GLDETL
2. all rows where GLMAST and GLDETL ACCOUNT are matching
3. all rows from GLDETL where NO matching rows exist in GLMAST

I hope I understood correctly what you meant.

Kind regards,
Daniel

Am 03.03.2025 um 16:41 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

I have two GL tables... GLMAST and GLDETL.

GLMAST stores period totals for a GL account for a given year. Account, Year, Beginning Balance, Period 1, Period 2, etc.
GLDETL contains the individual GL credit/debit transactions.

I'm trying to do a report that shows beginning balance, credits, debits, and ending balance for a given account and year/period.
The problem is this:

1. The GLMAST doesn't contain a record for an account & year where every period total is $0.00.
2. The GLDETL may not have any transactions for a given account/year/period, but may still have a beginning balance in GLMAST

My output needs to have a row if data exists in EITHER table for a give account, year and period.
I'm not sure how to do that using an SQL statement or view. Looking for ideas.

Thanks,
Greg
[Logo]<https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Greg Wilburn
Director of IT
301.895.3792 ext. 1231