Re: SQL Question -- MIDRANGE-L

Are you sure that the results are different? Or are they simply ordered
differently?

Add an order by clause to the final select for both.

Alternatively, you might consider
with gldata as (
select
gdcom# as com_nbr,
gdyer# as gl_year,
gdact# as gl_acct
from gldetlgd
group by
gdcom#,
gdyer#,
gdact#
UNION DISTINCT
select
glcom# as com_nbr,
glyer# as gl_year,
glact# as gl_acct
from glmastgl
group by
glcom#,
glyer#,
glact#
)
That gives a single results set of company, year, acct that you can then
LEFT JOIN back to master and/or detail.

Charles

On Mon, Mar 3, 2025 at 12:42 PM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

I really appreciate the help, but I'm still lost.

Here's what I came up with to create a list of Company Number, Year and
Account:

with gldyears as (
select
gdcom# as com_nbr,
gdyer# as gl_year,
gdact# as gl_acct
from gldetlgd
group by
gdcom#,
gdyer#,
gdact#
)
,
glmyears as
(select
glcom# as com_nbr,
glyer# as gl_year,
glact# as gl_acct
from glmastgl
group by
glcom#,
glyer#,
glact#
)
SELECT *
FROM gldyears
FULL OUTER JOIN glmyears using (com_nbr,gl_year,gl_acct)
where com_nbr='001' and gl_year=2024;

COM_NBR GL_YEAR GL_ACCT
001 2024 111000000000000
001 2024 111700000000000
001 2024 114000000000000
001 2024 114100000000000
...

IF I change the select statement above to swap GLDYEARS and GLMYEARS, I
get a different result set.
SELECT *
FROM glmyears
FULL OUTER JOIN gldyears using (com_nbr,gl_year,gl_acct)
where com_nbr='001' and gl_year=2024;

COM_NBR GL_YEAR GL_ACCT
001 2024 111000000000000
001 2024 111500000000000
001 2024 111700000000000
001 2024 114000000000000
001 2024 114100000000000
001 2024 114200000000000
...

It's like the FULL OUTER JOIN isn't working. Account #1115 is the one
that does not exist in GLMASTGL for 2024.

This is so much easier in an RPG program LOL.

Greg

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Monday, March 3, 2025 12:22 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Question

Two options to consider and test for performance..

1. use USING
FROM GLMAST
FULL OUTER JOIN GLDETL USING (ACCOUNT, YEAR)
Unlike ON, USING only returns 1 ACCOUNT and YEAR column

2. use CTEs to generate the years first, then use that in the query
with selectedYears as (...)
, allAccounts as (...)
...

HTH,
Charles

On Mon, Mar 3, 2025 at 10:01 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

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
--
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
--
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.