Avatar of bose3
bose3 asked on

SQL Query Sorting Order

Hello,

I have been trying to do sorting in DB2 with the following but have no success so far. I will post more details if it's confusing

Here is the before scenario

Branch A             Branch B

PIR                      PIR
PIR                      WB
WB                      PIR
WB                      WB

Is it possible to make it look like this so that Branch A and Branch B values are parallel to each other ?

Branch A             Branch B

PIR                      PIR
PIR                      PIR
WB                      WB
WB                      WB

Thanks
DB2SQL

Avatar of undefined
Last Comment
dsacker

8/22/2022 - Mon
Sean Stuber

Are branch A and branch B separate tables or are they columns within a single table?

Will they always be 1-to-1? If not, what do you want the results to be if there are more values in A than in B or vice versa?
dsacker

The second set of data does not have the same rows as the first set. Perhaps you wanted something like this, so that you have all the parallels first, and then the non-parallels later?

Branch A             Branch B

PIR                      PIR
WB                      WB
PIR                      WB
WB                      PIR

This would SORT them as such, if I assumed correctly:

SELECT BranchA, BranchB
FROM YourTable
ORDER BY CASE WHEN BranchA = BranchB THEN '0' ELSE '1' END,
    BranchA, BranchB

Open in new window

ASKER
bose3

Sdstuber,

Branch A and Branch B are seperate tables. The relationship could be 1 to Many. Assumption is that Branch B table value will always be in Branch A table for a given identifier. If there is a case where Branch B value doesn't exist in Branch A then that is considered a break, which requires end user action.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
dsacker

Not knowing what your field name is, I plugged the name Field1, so change as needed. This may work:

SELECT BranchA.Field1, BranchB.Field1
FROM BranchA
INNER JOIN BranchB ON BranchB.Field1 = BranchA.Field1
ORDER BY CASE WHEN BranchA.Field1 = BranchB.Field1 THEN '0' ELSE '1' END,
    BranchA.Field1, BranchB.Field1

Open in new window

Do you need to see those rows in BranchB that aren't in BranchA? The above query did not take that into account. Or would that simply be a second query, something like this?

SELECT *
FROM BranchB
WHERE Field1 NOT IN (SELECT Field1 FROM BranchA)

Open in new window

ASKER
bose3

dsacker,

I tried to use your solution but go the following error.

> Script lines: 1-98 -------------------------
 [IBM][CLI Driver][DB2] SQL0104N  An unexpected token "FACILITY_BRANCH" was found following "FACILITY_BRANCH".  Expected tokens may include:  "AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MI".  SQLSTATE=42601 "

Here is the logic for the branch match. Facility Branch (aka Branch A), Outstanding Branch (aka Branch B). Ideally I want to see the breaks only but sometimes it helps to see all the data.

Select

FAC.FAC_PID_FACILITY,
FPP_CDE_BRANCH as Facility_Branch,
FPP_CDE_PORTFOLIO as "Facility Portfolio",
FPP_CDE_EXPENSE as "Facility Expense",
OST_RID_OUTSTANDNG,
OUT.OST_NME_ALIAS as "Outstanding Alias",
OUT.OFS_CDE_BRANCH as Outstanding_Branch,
OUT.OFS_CDE_PORTFOLIO as "Outstanding Portfolio",
OUT.OFS_CDE_EXPENSE as "Outstanding Expense",

From L7001.VLS_FACILITY FAC

Left Join FPP on FPP_PID_FACILITY = FAC_PID_FACILITY

iNNER join OUT OUT on OUT.OST_PID_FACILITY = FAC_PID_FACILITY

ORDER BY CASE WHEN Facility_Branch = Outstanding_Branch THEN '0' ELSE '1'END Facility_Branch, Outstanding_Branch

Open in new window

dsacker

Looks like simply some typos. There was no space before "END", and no comma after for the subsequent fields. Try this:

SELECT  FAC.FAC_PID_FACILITY,
        FPP_CDE_BRANCH as Facility_Branch,
        FPP_CDE_PORTFOLIO as "Facility Portfolio",
        FPP_CDE_EXPENSE as "Facility Expense",
        OST_RID_OUTSTANDNG,
        OUT.OST_NME_ALIAS as "Outstanding Alias",
        OUT.OFS_CDE_BRANCH as Outstanding_Branch,
        OUT.OFS_CDE_PORTFOLIO as "Outstanding Portfolio",
        OUT.OFS_CDE_EXPENSE as "Outstanding Expense",

FROM    L7001.VLS_FACILITY FAC

LEFT JOIN FPP on FPP_PID_FACILITY = FAC_PID_FACILITY

INNER JOIN OUT OUT on OUT.OST_PID_FACILITY = FAC_PID_FACILITY

ORDER BY CASE
            WHEN Facility_Branch = Outstanding_Branch THEN '0'
            ELSE '1'
         END,
         Facility_Branch,
         Outstanding_Branch

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
bose3

dsacker,

I fixed the typos but still getting the old results. Here is the output

FACILITY_BRANCH      OUTSTANDING_BRANCH
PIR                                     PIR
PIR                                     WB
WB                                    PIR
WB                                    WB
dsacker

Hmmmm, that ORDER BY should fix that, because it accomplishes the following:

If they are the same, sets a value of '0'
Else sets a value of '1'
Sorts on that value first, THEN on the remaining columns.

Makes me wonder if they are truly passing the CASE IF = test.

Can you add an additional field, just to see?

Somewhere within the SELECT list, add the following:

        CASE
            WHEN Facility_Branch = Outstanding_Branch THEN '0'
            ELSE '1'
        END AS Match_Indicator,

Open in new window

Please let me see the two branches AND this indicator.
ASKER
bose3

See the output below

FACILITY_BRANCH      OUTSTANDING_BRANCH      MATCH_INDICATOR
PIR                                  PIR                                                0
PIR                                  WB                                               1
WB                                  PIR                                               1
WB                                  WB                                              0
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Sean Stuber

You have 3 tables in your query,  Which tables/columns correspond to the two tables you're interested in.

If there is a case where Branch B value doesn't exist in Branch A then that is considered a break,

What should that look like?

Also, given two PIR values in A and two PIR values in B, who do I know which ones should be lined up?
dsacker

Keep the Match_Indicator in your SELECT LIST. Don't take it out.

Change the ORDER BY to:

ORDER BY Match_Indicator,
         Facility_Branch,
         Outstanding_Branch

Open in new window

Does that change the output? (I think we simply have to play with this a bit, and it should get you there.)
ASKER
bose3

dsacker,

I think the issue is that there is no uniqe identifier between the multiple branches which leads to dupes and then sorting those dupes is quite tricky. I usually pull the data from 2 tables which consist branch information then sorting them independently and just apply Match formula to get the breaks.

The following join also generates the same result as "Match_Indicator"

iNNER join OUT OUT on OUT.OST_PID_FACILITY = FAC_PID_FACILITY
                 AND OUT.OFS_CDE_BRANCH <> FPP_CDE_BRANCH
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dsacker

Well, that would certainly make it a challenge. Perhaps you can UNION two queries, (1) SELECT joining on Facility_Branch = Outstanding_Branch, and then (2) SELECT joining on OUT.OST_PID_FACILITY = FAC_PID_FACILITY AND Facility_Branch <> Outstanding_Branch.

That may get you a little closer, but seems like you know the data on both sides well. Data mining is usually what gets you through it.
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
bose3

sdstuber,

I have tried your solution and it seems to work majority of the time. I am currently applying minor tweaks to get the desired result. There are cases where Branch A has 1 row count but Branch B has 2 counts. In those cases I intentionally added dupes so that I could get the same row count as Branch B.

will keep you posted

Thanks
dsacker

Seems like dsacker should get assist point awards, preferably excellent, considering the time invested and productive dialogue with author.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck